Yii2 Join loop, how?

Question

MySQL

Hello, the question is in the title, in Yii I did a loop from this line:

foreach($arr as $key => $value)
{
$criteria->join .= 'LEFT JOIN `table` '. $value .' ON (`'. $value .'`.`value_id`=`t`.`id`)';
}

everything worked.
in Yii2 there is no criterion, how to be? I did not find a solution in the dock
. I tried this, it did not work:

$query = Product::find();
   foreach(array('1','2','3') as $item)
   {
      $query->JoinWith('filters')->where(['table.value_id'=>$item]);
   }
   
    // В модели Product
    public function getFilters()
    {
        return $this->hasOne(Table::className(), ['products_id' => 'id']);
    }

Only one join passes, the last one

Answer 1

www.yiiframework.com/doc-2.0/guide-query-builder.h...
the only thing is that you have to invent aliases for the tables (not sure if this is broken automatically).

Answer 2

How about a regular SQL query?

Answer 3

If I understand you correctly, then your request will look something like this:

$data = Product::find()
  ->joinWith('table')
  ->where(['table.value_id' => [1, 2, 3]])
  ->asArray()
  ->all();

I don’t understand why such sql generated :(

SELECT `product`.* FROM `product` LEFT JOIN `table` ON `product`.`id` = `table`.`product_id` WHERE `table`.`value_id` IN (1, 2, 3);
SELECT * FROM `table` WHERE `product_id` IN ('1', '2');

But I would do like this:

$data = Product::find()
  ->with([
    'table' => function(Query $q) {
      $q->where(['value_id' => [1, 2, 3]]);
    }
  ])
  ->asArray()
  ->all();

SELECT * FROM `product`;
SELECT * FROM `table` WHERE (`value_id` IN (1, 2, 3)) AND (`product_id` IN ('1', '2'));

// print_r($data);
Array
(
    [0] => Array
        (
            [id] => 1
            [manufacturer_id] => 2
            [category_id] => 13
            [status] => 10
            [name] => фотокамера Canon SX 150 IS black
            [created_at] => 1395167522
            [updated_at] => 1395167679
            [table] => Array
                (
                    [product_id] => 1
                    [value_id] => 1
                )
        )

    [1] => Array
        (
            [id] => 2
            [manufacturer_id] => 2
            [category_id] => 29
            [status] => 10
            [name] => сумка
            [created_at] => 1395170865
            [updated_at] => 1395170865
            [medium_delivery] => 0
            [table] => Array
                (
                    [product_id] => 2
                    [value_id] => 1
                )
        )
)