1.table product id title status-> 1

2.table order (intermediate) prodict_id user_id status 1

3.table user id name email

you need to get product.title, user.name, user.email where the status proudct.status = 1, order.status = 1 sql I wrote,

SELECT `user`.`username`, `user`.`email`, `product`.`title` FROM `user` LEFT JOIN `order` ON `order`.`user_id` = `user`.`id` LEFT JOIN `product` ON `order`.`product_id` = `product`.`id` AND `product`.`status` = 1 WHERE `order`.`status` = 1

But how can I do this in equating records, I can not understand. it may be more convenient to specify conditions in the entity type

 public function getOrders() { return $this->hasMany(Product::className(), ['id' => 'product_id']) ->viaTable('order', ['user_id' => 'id']) ->where('status' => 1); //пробовал разные варианты onCondition и т.д. //вычитал что может лучше укаызвать via, но что-то тоже //ничего не поулчается. }

controller code

 $users = User::find() ->joinWith([ 'orders' ])->all();

tables

  • This code returns all records ignoring conditions (statuses), where and how to register them I can not understand. - ProMix
  • It seems like $users = User::find()->joinWith(['orders'])->andWhere(["orders.status" => 1])->all(); - Peresada
  • no, it does not work, it chooses but ignores the status of the product, that is, where proudct.status = 0 also rakes out, but it shouldn’t, how do I add (product.status = 1) this condition? - ProMix
  • if I add (andWhere (['product.status' => 1])) I get the following error SQLSTATE [42S22]: Column not found: 1054 Unknown column 'product.status' in 'where clause' user . * FROM user LEFT JOIN order ON user . id = order . user_id LEFT JOIN product ON order . product_id = product . id WHERE ( order . status = 1) AND ( product . status = 1) - ProMix
  • show how your user, product, order tables look - Peresada

1 answer 1

Judging by the comments and the error, the problem is in JOIN, you need to put the product and order statuses inside these statements

In other words, the query will look something like this.

 $users = User::find()-> joinWith(['orders' => function (\yii\db\ActiveQuery $query) { $query->where(['order.status' => 1]); }, 'products' => function (\yii\db\ActiveQuery $query) { $query->where(['product.status' => 1]); }, ]])->all();
  • Thanks, it turned out! True, he did a little differently, in entiti, where connections were added by a method with a "closure" or an anonymous function - how to correctly say. - ProMix