I’m using “many to many” relations, and i’m trying to write search based on strict comparison for some properties of model, which is “many to many” relationship.
If i writing IN condition (by method addInCondition) for this relationship - it’s not strict searching 'cause
query match to each model, which satisfy to at least one of seted in addInCondition values. I wish to get models, which satisfy all seted velues in addInCondition.
This query seems to be a really nice riddle . I don’t think it can be done by some simple query with combination of wheres, joins etc but one ugly workaround comes to my mind
all you have to do is extend your query for additional column count(*), add grouping by eg user.id (I guess you’ve already did it because otherwise you would receive user multiple time in one result set), and adding HAVING statement that would compare count of user-country mapping with prepared parameter equals to size of array you are giving as a parameter. So summing up you would like to receive query like this:
select some_fields, count(*) c from user u join .. here comes joins .. etc .. where uc.country_id in (1,3) group by u.id having c = 2;
so having your $criteria variable you have to do following:
$criteria->select = 'unfortunately you have to specificy all fields you would like to have (it can be *), count(*) as c';
$criteria->group = 'u.id';
$criteria->having = 'c = ' . count(array(1,2)) <- here comes variable storing id of countries
of course your existing addInCondition statement stays.
I would be really interested if someone found regular solution for this problem.
This won’t work. Because having row like for example this:
U.Id | U.Name | C.ID | C.Name
1…John…1…UK
your criteria will produce query like this SELECT something … where C.Id IN (1) AND C.Id IN (2) which will return 0 rows as no row have 2 different countries.
Entry for one User is treated as multiple rows (for every country) after joining with Country, and WHERE statement will be applied to every row separately.
Yes that’s exactly what I suggested. Well for me it’s not beautifull solution I find it some kind of mysql statement hack. But it’s just my opinion, if it’s eficient why not to use it .
As i using a few tables to join and CDbCriteria i found some problem when COUNT return strange numbers. This is because if i didn’t determine some WHERE clause for OTHER TABLES, it counts all records from these tables, so i’m write rules: if no any conditions for other tables, i don’t join its, and if condition exist - join.