a good SQL query with Doctrine2 to get items from a list of tags

Question

I am using Symfony2 with Doctrine2 and MySQL.

I created 2 entities:

Article
Tag With a many-to-many link between them.

I am trying, from a list of ID of tags, to get the OR and the AND of the Articles.

An exemple may be better:

Article1 <-> Tag1, Tag2
Article2 <-> Tag2, Tag3, Tag4
Article3 <-> Tag1, Tag4

I would from a list of my ID of tags [2, 3], do a AND and a OR request, to get some articles:

AND => [Article2] is the only which contains the both tags.
OR => [Article1, Article2] are the only which have the tag2 and tag3

I successed to do the OR query with multiple requests in the PHP code with a loop, which is really not optimized at all. And I am afraid to do a combinatorial explosion with the number of requests for the AND. I know there is a proper way to do it in MySQL with JOIN, and I guess Doctrine2 language, both in one request, but the more doc I read and try, the more I figure out that it is not my computing level.

If anybody can help me on this point, I will be thankful.

EDIT: Here is my current solution, which is not optimized:

class TagRepository extends EntityRepository
{
    public function getItemsFromIds($ids)
    {
        $qb = $this->createQueryBuilder('a');
        $qb->where('a.id IN (:ids)')
            ->setParameter('ids', $ids);

        return $qb->getQuery()->getResult();
    }
}

Then in my DefaultController.php, in the function which process the stuff :

$finalarticles = array();

$tagsrepository = $this->getDoctrine()
                   ->getEntityManager()
                   ->getRepository('ElephantTagsBundle:Tag');

$tags = $tagsrepository->getItemsFromIds($tagids);
$nbtags = count($tags);

foreach ($tags as $tag) {
    $articles = $tag->getArticles();
    foreach ($articles as $article) {
        $articlestags = $article->getTags();
        $tmpnbtags = 0;
        foreach ($articlestags as $articlestag) {
            if (in_array($articlestag->getId(), $tagids))
                $tmpnbtags += 1;
        }
        if ($tmpnbtags == $nbtags)
            $finalarticles[$article->getId()] = $article;
    }
}

As you can see, this method include 3 for loops (+one in the "array_in" I guess), which include a lot of MySQL call too. I am sure there is a proper way to do it in the repository!

Upvote Upvote · Accepted Answer · 2012-09-25T13:10:59

Please provide your entity definition from resources/config folder. What you are looking for is called Bidirectional Association Mapping. Have a look at the documentation:

http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html#many-to-many-bidirectional

In your article:

<many-to-many target-entity="Tag" inversed-by="articles" field="articles"/>

and in your tag:

<many-to-many field="articles" target-entity="Article" mapped-by="tags"/>

When your class is generated you can add and get tag or article from both sides, bidirectional.

Remember, if you want to remove a tag from an article you have to implement a custom method in article and tag class like this:

In article class:

public function removeTag(\YourBundle\Entity\Tag $tag){
        return $this->tags->removeElement($tag);
}

In tag class:

public function removeArticle(\YourBundle\Entity\Article $article){
        return $this->articles->removeElement($article);
}

a good SQL query with Doctrine2 to get items from a list of tags

1 Answers