CODEWITHSUNDEEP
joinactiverecordyii2
Chloe
Chloe

Reputation: 26294

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?

Upvotes: 2

Views: 3085

Answers (3)

YanAlex
YanAlex

Reputation: 119

If you want index by relation recods via joinWith() or with() results you can use following:

->with(['relationName' => function($q) {
            $q->indexBy('field_name');
        }])

Upvotes: 0

Beowulfenator
Beowulfenator

Reputation: 2300

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.

Upvotes: 4

Chloe
Chloe

Reputation: 26294

I ended up doing it manually for a subset of the ids and it only uses 2 queries. I'd still be interested in the indexBy though.

$products = Product::find()->joinWith('ebay', true, 'inner join')->where(['ebay.epid' => $productIds])->all();
$ebayProducts = array();
foreach ($products as $p) {
  $ebayProducts[$p->ebay->epid] = $p;
}

Upvotes: 0

Related Questions