Doctrine 2.1 DQL - Many-to-many query multiple values - Item in multiple categories?

Question

This may seem like a rudimentary request, but I can't seem to get it to work, so I'm either missing something stupid, or am not understanding how it should be done. Thanks in advance.

I have two doctrine entities with a many-to-many relationship: Items and Categories. Which are joined by items_has_categories.

/**
* Item
*
* @Table(name="items")
* @Entity(repositoryClass="Entity\Repository\Item")
*/
class Item
{

....

 /**
 * @var Categories
 *
 * @ManyToMany(targetEntity="Categorie", inversedBy="items", cascade={"persist"})
 * @JoinTable(name="items_has_categories",
 *   joinColumns={
 *     @JoinColumn(name="items_id", referencedColumnName="id")
 *   },
 *   inverseJoinColumns={
 *     @JoinColumn(name="categories_id", referencedColumnName="id")
 *   }
 * )
 */
private $categories;

....
}

/**
* Categorie
*
* @Table(name="categories")
* @Entity(repositoryClass="Entity\Repository\Categorie")
*/
class Categorie
{
 .....

/**
 * @var Items
 *
 * @ManyToMany(targetEntity="Item", mappedBy="categories")
 */
private $items;

....
}

And what I'm trying to do is run a query to return all items that are in all of "x" number of categories - which I think/thought should be a SELECT with and AND clause:

class Item extends EntityRepository
{
    public function findItemsByCategories($categories)
    {

    $qString = 'SELECT j, t, c FROM Technique\Entity\Item j LEFT JOIN j.itemImages t JOIN j.categories c WHERE';

    $i = 0;
    foreach ($categories as $c)
    {
        $qString .= ' c.name = ?' . $i;

        if ($i < (count($categories)-1))
        {
            $qString .= ' AND';
        }
        $i++;
    }

    $query = $this->_em->createQuery($qString);
    $query->setParameters($categories);

    return $query->getResult();
}

That little bit of code has no errors and spits out the following DQL SELECT query (when 2 categories are sent in the array: $categories):

SELECT j, t, c FROM Technique\Entity\Item j LEFT JOIN j.itemImages t JOIN j.categories c WHERE c.name = ?0 AND c.name = ?1

This is always returning an empty array, i.e., no results. Even though in my DB, there are more than 20 items fitting the criteria: are in both categories.

Anyone see what I'm doing wrong here? Is this supposed to be an AND selection...? Basically I'd just like to know how to query a many-to-many relationship in Doctrine 2+, where there's more than one value that must be met...

Naima

Answer 1

For anyone interested, I figured it out (painfully). Doctrine should really explain this better instead of the the one line they have on the DQL page....

Basically it's not an AND query, it's a MEMBER OF AND query. For each category a MEMBER OF must be created and then added to the whole query with an AND:

SELECT j, t FROM Entity\Item j
LEFT JOIN j.itemImages t
WHERE ?0 MEMBER OF j.categories AND ?1 MEMBER OF j.categories
AND ?2 MEMBER OF j.categories, etc.

That will return all items that are in all categories requested.

Naima

Thank you for sharing your find. I was about to give up on a very similar issue, but MEMBER OF did the trick. Best to you. – Cowell

Finally a nice way to do this. MEMBER OF really should be more documented. Didn't even know it existed. Thanks a lot. – Scrubland

From my experiments, this method will cause multiple sub-queries to be run. If DQL accepts it, you'd be far better off with a sub-query and a WHERE clauses that uses IN(). I'll experiment and post an answer if this still works in DQL. – Viole

@Viole Did you ever figure this out? I'm going happily with the member of answer for now as I'm in a hurry rather than optimizing right now, but would like to circle back and know for the future. (I'll give it a go myself if I get the time!) – Woodsy