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