Search code examples
joinactiverecordyii2

How do I use join and indexBy in Yii2?

I have this, but it loads each and every ebay row individually, generating thousands of SQL statements:

$products = \app\models\Product::find()
    ->joinWith('ebay', false, 'inner join')
    ->indexBy(function($row){return $row->ebay->epid;})
    ->all();

I tried this, but it gave an error: 'Getting unknown property: app\models\Product::ebay.epid'

$products = \app\models\Product::find()
    ->joinWith('ebay', false, 'inner join')
    ->indexBy('ebay.epid')
    ->all();

Setting eager loading = true doesn't help either. It still loads each row individually then loads them again at the end.

How can I efficiently join a table in Yii and index by a value in the joined table?

Solution

  • You won't be able to do it with indexBy. However, ArrayHelper::index can index an array on a related model field. So here's how it can be done:

    $products = \app\models\Product::find()
    ->with('ebay')
    ->all();

ArrayHelper::index($products, 'ebay.epid');

    The code will run two queries, one to get all products, one to get all related ebay products. Then the array will be indexed with no DB queries at all.