Yii Framework Forum: Strict Comparison Cdbcriteria. How ? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Strict Comparison Cdbcriteria. How ? Many to many Rate Topic: -----

#1 User is offline   Termit132 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-September 12

Posted 11 December 2012 - 02:28 PM

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

#2 User is offline   mrk 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-March 11

Posted 11 December 2012 - 04:05 PM

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

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 11 December 2012 - 07:26 PM

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

0

#4 User is offline   mrk 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-March 11

Posted 12 December 2012 - 07:36 AM

View Postsoftark, 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.
0

#5 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 12 December 2012 - 10:10 AM

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

#6 User is offline   mrk 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-March 11

Posted 12 December 2012 - 02:50 PM

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 :).
0

#7 User is offline   Termit132 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 14-September 12

Posted 03 June 2013 - 08:18 AM

Hi All !
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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users