Query Of Join With Two And-Criterias Returns Always Zero

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

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');



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

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.

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



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

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.

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…