Related Tables and Limit Issue / Bug?

I have a few related tables I am trying to get pagination on. So for example:

$product->joinWith(“companies”)->joinWith(‘categories’)->where(blah b;ahc);

    $products = $test->offset($pages->offset)



If I were to run $product->offset(0)->all() I would get all my products back.

If I were to run $product->offset(0)->limit(20)->all() I will only get 4-6 products back depending on the page I am on, The count is random.

I checked the database and copied the queries and they generate data fine for all situations. I iterated over the object and it is on 4-5 items long. So it is not pagination widget, it is something goofy with activerecord and LIMIT.

If I remove "categories" out of the equation, then pagination works properly and all my products are returned.

Any thoughts?

The only issue bug was me… I guess, I thought active record would of handled it. Since product is a 1 to many join with categories, the limit is being applied to a non grouped Product data return. Each product has multiple categories, so the SQL result set is a multiple of the same product. After adding a groupby on product ID the issue was resolved.

Problem is, it isn’t something you can directly see. What limit was doing was putting a LIMIT on my SQL result set which had multiples of product and then building the object, finally consolodating those items in the product object so I would only end up with 3 or 4 unique items but missing the other 16-17.

If the product object / active record loaded those repeating items without consolodating the repeats, it would have been much easier to see the problem.

I wrote a wiki article on this problem.

CActiveRecord of Yii 1.1 has a very difficult problem regarding HAS_MANY relation and OFFSET/LIMIT. It is greatly improved in Yii 2.0. But you are right. There still exists a stumbling block.

I think I actually came across this article when I was searching for a solution. But it did not settle in that you were actually presenting the solution to the problem at the time.

Thanks and cheers.