Strict Comparison Cdbcriteria. How ? Many to many
Posted 11 December 2012 - 02:28 PM
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.
If i set :
$criteria->addInCondition('country_id', array(1, 3));
In result i'm retreive John and Mary, but i wish to get only John, 'cause only it match to all values, which seted in addInCondition (1, 3)
Thank you in advance.
Posted 11 December 2012 - 04:05 PM
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.
Posted 12 December 2012 - 07:36 AM
$country = array(1, 3); foreach($countries as $country) $criteria->addInCondition('country_id', array($country));
This won't work. Because having row like for example this:
U.Id | U.Name | C.ID | C.Name
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.
Posted 12 December 2012 - 10:10 AM
Well, what about this kind of query?
select a.* from A a join B b1 on ... join B b2 on ... where b1.id = x and b2.id = y
OK, I think I got your point in the first reply, mrk.
So you are suggesting something like this:
select a.*, count(b.id) as b_count from A a join B b on ... where b.id in ... group by a.id having b_count = ...
Am I right?
It seems a decent solution to me.
Posted 12 December 2012 - 02:50 PM
Posted 03 June 2013 - 08:18 AM
Thanks for solution, it helps me.
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.