[Termit132]

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

For exapmle:
table user
id.....name
1......John
2......Mary

table user_to_country
user_id.....country_id
1................1
1................3
2................2
2................1

table country
id................country_name
1................United Kingdom
2................Ukraine
3................Afganistan

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.

[mrk]

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.

[softark]

What about this?

$country = array(1, 3);
foreach($countries as $country)
    $criteria->addInCondition('country_id', array($country));

[mrk]

softark, on 11 December 2012 - 07:26 PM, said:

What about this?

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

[softark]

Ah, yes. You are right. Thank you for the correction.

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

[EDIT]
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.

[mrk]

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 .