How to select used items in a ManyToMany Doctrine relation

Question

I have two entities (Item & Tag) linked by a bidirectional ManyToMany relation, and I'd like to display the entity (Tag) records that are actually used in a relation with the other entity (Item) :

Here is my Item entity:

class Item
{
    /**
     * @ORM\ManyToMany(targetEntity="MyBundle\Entity\Tag", inversedBy="items")
     */
    private $tags;
}

And my Tag enity :

class Tag
{
    /**
     * @ORM\ManyToMany(targetEntity="MyBundle\Entity\Item", mappedBy="tags")
     */
    private $items;
}

Now in my Tag Repository I've tried this :

class TagRepository extends \Doctrine\ORM\EntityRepository
{
    public function findAllUsed()
    {
        return $this->createQueryBuilder('t')
            ->leftJoin('t.items', 'items')
            ->groupBy('items.id')
            ->having('COUNT(t.id) > 0')
            ->orderBy('t.name', 'ASC')
            ->getQuery()
            ->getResult();
    }
}

But it doesn't give me the result I'm expecting... Can anyone help? Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

I did this from the top of my head, but it should work, I used an innerJoin instead of a leftJoin and then added a where to make sure you get the records that are linked:

public function findAllUsed()
    {
        return $this->createQueryBuilder('t')
            ->innerjoin('t.items', 'i')
            ->groupBy('i.id')
            ->where('i.id = t.items')
            ->having('COUNT(t.id) > 0')
            ->orderBy('t.name', 'ASC')
            ->getQuery()
            ->getResult();
    }
}

I used this example to formulate the answer: Query on a many-to-many relationship using Doctrine with Symfony2

edited May 23 '17 at 12:31

Community

1
1

answered Dec 30 '16 at 14:34

KevinTheGreat

634
5
22

I have the error " 'items GROUP BY': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected." now. – VinZ Dec 30 '16 at 14:57
Did you change the -groupBy('items.id') to ('i.id') – KevinTheGreat Dec 30 '16 at 15:07
You answer is redundant a little bit. Because `where('i.id = t.items')` is already included in the inner join. – Vinícius Fagundes Dec 30 '16 at 17:18

score 1 · Accepted Answer · answered Dec 30 '16 at 17:25

The Problem

I didn't test but It seems your error is in the count clause. You are counting tags having('COUNT(t.id) > 0'). So it will return all tags. Another error is you grouping by 'items' and selecting only 't'. You don't need group by.

Solutions

Change 'tags' for 'items' in having clause.

public function findAllUsed()
{
    return $this->createQueryBuilder('t')
        ->leftJoin('t.items', 'items')            
        ->having('COUNT(items.id) > 0')
        ->orderBy('t.name', 'ASC')
        ->getQuery()
        ->getResult();
}

Another possible way more simple is do a innerJoin like @KevinTheGreat, but check won't need having or where clauses any more:

public function findAllUsed()
{
    return $this->createQueryBuilder('t')
        ->innerJoin('t.items', 'items')           
        ->orderBy('t.name', 'ASC')
        ->getQuery()
        ->getResult();
}

Second solution is simple and easy! I should study a bit more my SQL language... thanks Vinicius! — VinZ, Jan 03 '17 at 08:06

How to select used items in a ManyToMany Doctrine relation

2 Answers2