Yii Framework Forum: Query Of Join With Two And-Criterias Returns Always Zero - Yii Framework Forum

Jump to content

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

Query Of Join With Two And-Criterias Returns Always Zero Rate Topic: -----

#1 User is offline   gb5256 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 22-April 11

Posted 14 September 2013 - 05:31 AM

Hi there, perhaps somebody can help me with this one.
I have two tables, lets say images and keywords.
There is a HAS_MANY relation between them.
The keyword table consists of this:
- ID
- Image_ID
- Keyword_ID

If I call in yii something like :
Show all Images where keyword = "1234"
it works as expected.

But I need to call something like
Show all Images where keyword = "1234" AND keyword = "asdf"
Then no result is returned (but there are images with both keywords).

My query will be in the end very dynamic, so I do not know how many keywords will be called in the query.

Any ideas how to achieve this?
I have checked the output of the log files.
The SQL which yii generates looks fine, but it also gives no result directly run in MYSQL.
This is the query:

SELECT `t`.`id` AS `t0_c0`, `t`.`id_image` AS `t0_c1`,
`t`.`image_number` AS `t0_c2`, `t`.`keyword` AS `t0_c40`,
`imageKeywords`.`id` AS `t1_c0`, `imageKeywords`.`image_id` AS `t1_c1`,
`imageKeywords`.`keyword_id` AS `t1_c2` FROM `image` `t`  
LEFT OUTER JOIN
`image_keyword` `imageKeywords` ON (`imageKeywords`.`image_id`=`t`.`id`) 
WHERE  (`imageKeywords`.`keyword_id`=189) AND (`imageKeywords`.`keyword_id`=152)


I am lost.

And ideas?

gb5256
0

#2 User is offline   nineinchnick 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 622
  • Joined: 12-September 11
  • Location:Bialystok, Poland

Posted 14 September 2013 - 05:47 AM

No keyword holds both values at the same time. You want to use the 'OR' operator. Or if passing a list of values, use the 'IN' operators as:
SELECT * FROM table WHERE column IN ('value1','value2','value3');

Don't be a dick.
0

#3 User is offline   gb5256 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 22-April 11

Posted 14 September 2013 - 06:56 AM

Hello nineinchnick,
thanks for your reply.
I have tested that already, but of course there is a difference between AND and OR.
If I put OR into the query, then I get images which have at least Keyword1 OR keyword2.
But this is not what I am searching for.

hmmm.
Any ideas?

gb5256
0

#4 User is offline   softark 

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

Posted 14 September 2013 - 08:40 AM

Hmm, something like this?

select * from image i1
join keyword k1 on i1.id = k1.image_id
where k1.keyword_id = xxx
and i1.id in
(
  select id from image i2
  join keyword k2 on i2.id = k2.image_id
  where k2.keyword_id = yyy
);


Sorry, I have no confidence at all for this. I'd like to hear from the sql gurus.
0

#5 User is offline   softark 

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

Posted 14 September 2013 - 08:58 AM

Ah, this is much better:
select * from image
join keyword k1 on image.id = k1.image_id
join keyword k2 on image.id = k2.image_id
where k1.keyword_id = xxx
and k2.keyword_id = yyy

0

#6 User is offline   nineinchnick 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 622
  • Joined: 12-September 11
  • Location:Bialystok, Poland

Posted 14 September 2013 - 09:25 AM

I get it now, this should also work and should be a little faster:
select image.* from image
join keyword k1 on image.id = k1.image_id
where k1.keyword_id = xxx 
or k1.keyword_id = yyy
group by image.id
having count(k1.id) = 2


This gets all images having at least one of the two keywords and then leaves only those having both.
Don't be a dick.
1

#7 User is offline   softark 

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

Posted 14 September 2013 - 09:38 AM

View Postnineinchnick, on 14 September 2013 - 09:25 AM, said:

I get it now, this should also work and should be a little faster:
select image.* from image
join keyword k1 on image.id = k1.image_id
where k1.keyword_id = xxx 
or k1.keyword_id = yyy
group by image.id
having count(k1.id) = 2


This gets all images having at least one of the two keywords and then leaves only those having both.

Wow, nice.
So when you use it, you can easily change the number of keywords dynamically. :)
0

#8 User is offline   nineinchnick 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 622
  • Joined: 12-September 11
  • Location:Bialystok, Poland

Posted 14 September 2013 - 09:46 AM

Yes, now you can use the 'IN' operator. But you can't fetch keyword details this way, only image data, because keywords needs to be aggregated to be counted. You'd have to use lazy loading to fetch keywords for loaded images.

BTW If you want to get better at SQL I suggest switching to PostgreSQL and using pgAdmin3 on a daily basis. It got an amazing graphical query "explain" tool that shows how it is executed step by step.
Don't be a dick.
0

#9 User is offline   gb5256 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 22-April 11

Posted 14 September 2013 - 12:34 PM

Dear softark and nineinchnick,
wow,
you two came up with some true SQL-magic...

In the end I think the
select image.* from image
join keyword k1 on image.id = k1.image_id
where k1.keyword_id = xxx 
or k1.keyword_id = yyy
group by image.id
having count(k1.id) = 2

will be the solution I go for, as I do need to do it with Active Record.
The other solutions use multipe joins and I see some problems with joining the same relation n-times and then assigning the "same" relation in several where clauses, all within the Active Record. The "having" solution is quite simple and easy to build with Active Record,

THANKS GUYS !

PS: if you are ever in the area of Amsterdam, stop by, I owe you two a big beer...
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