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