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