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'
id_0
andname_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 oneEXISTS
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 – Lumpilukproject 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 theEXISTS
? – ccKep