Sorry if the topic title isn’t a good description of the problem, but here’s my issue.
Basically I was following this guide by softark:
www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation
because what I needed to achieve is extremely similar to example # 6. The project I’m working on has a dealers table with MANY products. The real difference is instead of a wild card search on a single string, I have a dropdown that allows users to select multiple products (which gives me an array of product id keys). My query ends up using an IN statement to find dealers that have all relevant product matches. Softark’s updated answer helps explain a lot of things but what I don’t understand is if there’s more than 1 product selected being passed to the IN statement, weird things happen and pagination breaks. I tried running the queries that Yii does in my sql editor against a single dealer and here’s what I get:
-
If I was searching for 1 product (and I know that dealer has this product), I get the dealer returned just fine in the first filtered step together with the product being searched so no problems here.
-
If I tried searching for 2 products (1 that I know exists, while another that doesn’t for the dealer), I again get the dealer together with the product that exists fine in the filtered list, and the final searched list returns nothing because 1 of the products does not exist in the filtered list so this scenario works out fine too.
-
If I tried searching for 2 products (both of which I know exists), I get 4 rows returned for my filtered set instead of just 2. Each row is basically a duplicate or the previous row (so I get 2 * 2 products).
#3 messes up my pagination because after grouping, I have to put in a HAVING statement to only grab a filtered list containing both products I searched for (GROUP BY t.id HAVING COUNT() = 2, or the number of products being searched for) because of my IN statement, else I would have a filtered list containing Dealers that only have 1 of the products. "HAVING COUNT() = 2" doesn’t work in this case because the result set ends up containing 4 records instead of 2.
I get the right records returned but pagination is broken because YII does an initial COUNT first on your query containing both WITH statements (following softark’s example) and this ends up being 0 because of the duplicate row issue. Succeeding queries are then processed (first to get the filtered list on the first WITH relation, then to search on that filtered list in the 2nd relation) and gives me the correct records.
Any idea why this happens (its most likely an issue with my generated SQL code) and how to fix this in YII?
By the way, the cdbcriteria is being used as a criteria, together with paging and sort details passed to a cactivedataprovider class. I don’t think that should make a difference though.
