Ian Fosbery Ian Fosbery - 1 year ago 118
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download