yii2 limit not working correctly

Yii 2.0 General Discussions
1

Hi guys! In my app limit not worked correctly. My query:




$products = Product::find()

            ->joinWith('categories')

            ->joinWith('review')

            ->joinWith('vendor')

            ->joinWith('productProperties')

            ->filterWhere(['in', 'product_category_property_id', $filter])

            ->andFilterWhere(['product_category_link.category_id' => $category])

            ->andFilterWhere(['between', 'price', $cost_from, $cost_to])

            ->andFilterWhere(['hit' => $on_top])

            ->andFilterWhere(['in', 'brand_id', $get_brands]);


        $count = clone $products;


        $pagination = new Pagination([

            'defaultPageSize' => 12,

            'totalCount' => $count->count(),

        ]);


        $models = $products->orderBy(['created_at' => SORT_DESC])

            ->offset($pagination->offset)

            ->limit($pagination->limit)

            ->all();

If I try without pagination with limit problem is similar too. But without limit everthing ok. If I remove all joins limit works correctly

2

Hi farkhodjon, welcome to the forum.

When you use joinWith for hasMany relations, limit may not work as expected, because the result set of the query may have duplicated entries from the main table.




product   category

A         X

B         Y

C         X

C         Y

C         Z

In the above, limit(5) will retrieve 3 products, not 5.

An easy solution for it may be adding "distinct" to the query.




products = Product::find()

            ->distinct()

            ->joinWith('categories')

            ...

Take a look at the following wiki.

Yii 2.0: Drills : Search by a HAS_MANY relation in Yii 2.0

http://www.yiiframework.com/wiki/780/drills-search-by-a-has_many-relation-in-yii-2-0

3

It helps, thank you