Doctrine query works with SQLite, not with MySQL

Question

I've created two entities called Project and ProjectTag using Doctrine (doctrine/orm v2.6.1) and Symfony. I'd like to select all projects that have all tags with the names given in $tags. The following query from my ProjectRepository.php works in my dev environment with SQLite:

public function findByAllTags($tags)
{
    $queryBuilder = $this->createQueryBuilder('p');
    for ($i = 0; $i < count($tags); $i++) {
        $tagSubQueryBuilder = $this->createQueryBuilder("p$i")
            ->join('p.tags', "tags_joined_$i")
            ->andWhere("tags_joined_$i.name = ?$i");
        $queryBuilder->andWhere(
            $queryBuilder->expr()->exists($tagSubQueryBuilder->getDQL())
        );
    }
    $queryBuilder->orderBy('p.timeCreated','DESC');
    return $queryBuilder->getQuery()->setParameters($tags)->getResult();
}

This gets converted to

SELECT 
  p0_.id AS id_0, 
  p0_.name AS name_1
FROM 
  project p0_ 
WHERE 
  (
    EXISTS (
      SELECT 
        p1_.id 
      FROM 
        project p1_ 
        INNER JOIN projects_tags p3_ ON p0_.id = p3_.project_id 
        INNER JOIN project_tag p2_ ON p2_.id = p3_.project_tag_id 
      WHERE 
        p2_.name = ?
    )
  ) 
ORDER BY 
  p0_.created DESC

However, in my production environment with MySQL I get the following error:

An exception occurred while executing 'SELECT p0_.id AS id_0, p0_.name AS name_1 FROM project p0_ WHERE (EXISTS (SELECT p1_.id FROM project p1_ INNER JOIN projects_tags p3_ ON p0_.id = p3_.project_id INNER JOIN project_tag p2_ ON p2_.id = p3_.project_tag_id WHERE p2_.name = ?)) ORDER BY p0_.created DESC' with params ["video"]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p0_.id' in 'on clause'

This looks kinda confusing, what's that query supposed to return? — ccKep
The query should (and with SQLite does) return the project IDs and names (id_0 and name_1 in this case) of the projects that have all tags with the names in the array $tags. In the case of the automatic conversion to SQL that I posted, $tags only contained one element, so there is only one EXISTS clause. I'm using @ORM/ManyToMany for assigning tags to projects and vice versa: @ORM\ManyToMany(targetEntity="App\Entity\ProjectTag", inversedBy="projects") \n @ORM\JoinTable(name="projects_tags") This join table is what's being used in the inner joins above — Lumpiluk
Your nested query selects project p1_ while none of the join conditions reference that. Why not just inner join the tags table in a single non-nested query and skipping the EXISTS ? — ccKep
Can you give a short example? I'm not sure how this would allow me to select the projects that have both of the tags "video" and "picture", for example. — Lumpiluk

ccKep ccKep · Accepted Answer · 2018-04-16T10:51:50

In response to your comment, this is an untested approach to your problem without nested queries:

public function findAllByTags($tags)
{
    $qb = $this->createQueryBuilder('p');

    $qb->join('p.tags', 't');
    $qb->where($qb->expr()->in('t.name', ':tags'));
    $qb->groupBy('p.id');
    $qb->having($qb->expr()->eq('COUNT(t.id)', ':count'));

    $qb->setParameter('tags', $tags);
    $qb->setParameter('count', count($tags));

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

This assumes you pass the tag names as an array (or that the Tag class implements a __toString() method that returns the tags name).

Doctrine query works with SQLite, not with MySQL

1 Answers