CODEWITHSUNDEEP
phpyii2
Vitaxxxa
Vitaxxxa

Reputation: 29

ActiveQuery count() and all() return different results

I have a query:

$query = Products::find();
$query->joinWith('vendor', true, 'LEFT JOIN');
$query->joinWith('values', true,'LEFT JOIN');
$query->where(['<>', 'stock', 7]);
$query->andWhere(['category_id' => $model->id]);

if (!empty($activeVendors))
    $query->andWhere(['lan_vendors.id' => array_flip($activeVendors)]);

if (!empty($activeValues)){
    $query->andWhere(['lan_products_values.value_id' => $activeValues]);
}

$totalProducts = $query->count();
$products = $query->all();

In result:

$totalProducts = 12;
count($products) = 3;

I can not solve this problem. Reading the documentation did not help. Is there something wrong with the database itself?

Upvotes: 2

Views: 622

Answers (2)

csminb
csminb

Reputation: 2382

your left join statements generate duplicate rows.
after a the query runs yii removes duplicate data and creates a usable array of uniqe Product models

the duplicate rows are not avoidable in your case since you enforce eager loading with left join

$query->joinWith('vendor', true, 'LEFT JOIN');
$query->joinWith('values', true,'LEFT JOIN');

you can try to run something like this to adjust the relations to your conditions, and follow the generated queries in the debug log, 

$query->with([
    'vendor' => function (\yii\db\ActiveQuery $query) use ($activeVendors) {
        $query->andFilterWhere(['lan_vendors.id' => array_flip($activeVendors)]);
    },
    'values' => function (\yii\db\ActiveQuery $query) use ($activeValues) {
        $query->andWhere(['lan_products_values.value_id' => $activeValues]);
    },
])

also follow the generated queries in the debug log, it's a usefull way of figuring out what happens in the two cases

Upvotes: 1

Bizley
Bizley

Reputation: 18021

Because you are joining additional tables here most probably you have got dupicated results - you can check it by running this query manually outside of Yii.

Query count() is showing you all the rows fetched from database (with duplicates).

all() on the other hand takes the fetched rows and while populating the Yii 2 models it removes duplicates so you have got unique ones.

Upvotes: 0

Related Questions