Yii2 Query with multiple tables

Question

users

ID | customerID | <others>

userTemplates

ID | userID | productCode

templates

ID | customerID | productCode | <others>

Im trying to find a nice solution to get all templates that are assigned to a given user based on the prodCode in the userTemplates table.

The product codes need to contain a wild card % so would be like this P.0100.% or %.0100.%

I have done this so far:

$usersTemplates = ArrayHelper::map(userTemplates::find()->where("userID = ".Yii::$app->user->identity->ID."")->all(),'ID','productCode');

$templates = new Query;
$templates->from('templates')
          ->where("customerID = ".Yii::$app->user->identity->customer->ID."")
          ->andWhere("disabled = 0")
          ->andWhere("approved = 1");

foreach($usersTemplates as $prodCode){
    $templates->andWhere("productCode LIKE '%".$prodCode."%'");
}

but i end up with this

SELECT * FROM `templates` WHERE ((((customerID = 1) AND (disabled = 0)) AND (approved = 1)) AND (productCode LIKE '%TESTWP%')) AND (productCode LIKE '%P.0100.0001%')

which is almost there but this bit:

AND (productCode LIKE '%TESTWP%')) AND (productCode LIKE '%P.0100.0001%')

needs to be:

AND ((productCode LIKE '%TESTWP%')) OR (productCode LIKE '%P.0100.0001%'))

Im sure there might be a better way of getting the list of templates.

Thanks in advance

ScaisEdge · Accepted Answer · 2016-07-20 20:34:55Z

1

when you have a complex query a simple solution is based on findBySql

$sql = "SELECT * 
  FROM `templates` 
  WHERE ((((customerID = 1) 
  AND (disabled = 0)) 
  AND (approved = 1)) 
  AND ((productCode LIKE '%TESTWP%')) 
  OR (productCode LIKE '%P.0100.0001%'))";

$model = Templates::findBySql($sql)->all();

answered Jul 20, 2016 at 20:34

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Yii2 Query with multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related