How to find a given value in a many-to-many relationship using Doctrine?

I have an entity "Discussion" that has (among other things):

A category property (every discussion has 1 category associated with it)
A ManyToMany association of "recommended categories" (every discussion can be recommended in multiple categories)

I'm trying to select all the discussions that are either in a specific category (their category property is that category) or that are recommended in this specific category.

I can easily get all the discussions that are in this category like this:

 $qb = self::$em->getRepository('Discussion')->createQueryBuilder('d');
 $discussions = $qb->where('d.category='.$current_category_id)
 ->setFirstResult( $offset )
 ->setMaxResults( $limit )          
 ->getQuery()->getResult();

I need to add to that all the discussions that have current_category_id in their list of recommended_categories.

So adding something like this:

$qb = self::$em->getRepository('Discussion')->createQueryBuilder('d');
$discussions = $qb->where('d.category='.$current_category_id)

  ->orWhere($qb->expr()->in($current_category_id, 'd.recommended_categories'))

 ->setFirstResult( $offset )
 ->setMaxResults( $limit )          
 ->getQuery()->getResult();

Which gives an SQL error because it doesn't like the "4 IN(d.recommended_categories))" in the SQL.

This is what the recommended_categories looks like in the Discussions entity:

/**
 * @ManyToMany(targetEntity="Category")
 * @JoinTable(name="discussion_recommended_categories",
 *   joinColumns={@JoinColumn(name="discussion_id", referencedColumnName="id")},
 *   inverseJoinColumns={@JoinColumn(name="category_id", referencedColumnName="id")}
 * )
 */
private $recommended_categories;

And here is the Category entity:

/** @Id @Column(type="integer") @GeneratedValue **/
protected $id;

/** @Column(type="string", unique=true, nullable=false,name="Name") **/
protected $name;

I also tried playing around with exists (does $current_category_id exist in d.recommended_categories) but nothing worked.

Any ideas how I can check if a given value (not a column) exists in a list of values associated with that entity?

Thanks!

Solution

First of all you should use prepared statements for the parameter in your where clause so you are protected against sql injection. Also you need to pass in your current category entity and Doctrine will magically extract the id from it by looking at your mapping. Assuming you have $currentCategory available.

$discussions = $qb
    ->where('d.category = :category')
    ->setParameter('category', $currentCategory);

You are trying to use a IN to check for a given value in a collection, but this is not what IN is meant for. You should use this for a one of condition (as a replacement for several or's). i.e. myfield = 1 OR myfield = 2 OR myfield = 3 you can write as myfield IN (1, 2, 3). I think you got a syntax error because you have the argument order wrong.

Anyway get rid of the IN and just add a join to the recommended_categories and a orWhere on the category. All together you should end up something like this.. untested.

$discussions = $qb
    ->where('d.category = :category')
    ->leftJoin('d.recommended_categories', 'rc')
    ->orWhere('rc = :category')
    ->setParameter('category', $currentCategory);