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