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.
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.
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.
