0

I have the following table (Articles, Categories, Relations) structure:

articles
 >id
 >title
 >body

cats
 >id
 >title

cat_article_relations
 >id
 >cat_id
 >article_id

Now i created a page where the user is able to select one or more categories (using checkboxes). After submitting the search form i have an array with all the category id's selected. Now, my problem occurs when i try to load all articles which have all of the selected categories.

When using:

...    
->leftJoin('a.Cat_article_relations rl')
->andWhere('rl.cat_id = ?', $sg[0])
...

It works because i only look for one included category. As soon as i add multiple "->andWhere"'s with a loop through all my category id's (in the array i got) it won't work anymore. I checked the Doctrine 1.2 documentation for any solution but couldn't find anything. "->whereIn" and similar are not giving the results i want.

I only want to get all articles which got a relation with all selected categories. Maybe someone can give me a hint :)

Info: If you have a solution for a pure MySQL query it would be a big help for me too. It doesn't have to be written with Doctrine.

Thanks

Peter
  • 1
  • 1
  • Could you explain why `whereIn` is wrong in this case? – j0k Jul 04 '12 at 12:53
  • ->whereIn('rl.cat_id',$sg) returns all articles which have at least one of the selected categories but i just want to get the articles which are in a relation with all the categories in my array. – Peter Jul 04 '12 at 12:59

1 Answers1

0

I am not sure if I understand the relation but this should be changed from:

->leftJoin('a.Cat_article_relations rl')
->andWhere('rl.cat_id = ?', $sg[0])

to

->leftJoin('a.Cat_article_relations rl WITH rl.cat_id=?', $sg[0])
Zeljko
  • 5,048
  • 5
  • 36
  • 46