Ian Fosbery Ian Fosbery - 6 months ago 19
SQL Question

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

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

Answer

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.