Search code examples
doctrineinner-joinsymfony4

Doctrine inner join three tables

I'm trying to get Posts by Tags in Symfony 4 with Doctrine. I have three tables like this:

Post

------------------------------
| id | title | content | ... |
------------------------------

Tag

-------------
| id | name |
-------------

TagPost (which makes the association between tags and posts)

--------------------
| tag_id | post_id |
--------------------

There can be several tags by posts and a tag can be used for several posts, that's why I use an association table.

I already succeeded to get it but only with raw sql, I have tried multiple times with the query builder and no way to get it. Any advices ?

The query (working): 

"SELECT post.id, post.title, post.author_id, post.content, post.datetime, 
post.tile FROM post
INNER JOIN tag_post ON post.id = tag_post.post_id
INNER JOIN tag ON tag_post.tag_id = tag.id
WHERE tag.id = " . $tag_id;
Solution

  • Assuming you're writing this inside a method in your PostRepository (https://symfony.com/doc/3.3/doctrine/repository.html), you'd write:

    $qb = $this->createQueryBuilder('post')
  ->select('post.id, post.title, post.author_id, post.content, post.datetime, post.tile')
  ->innerJoin('post.tag', 't')
  ->where('t.id = :tagid')
  ->setParameter('tagid', $tag_id)
;

$result = $qb->getQuery()->getResult();

    Several things to note:

    • The createQueryBuilder method is not exactly the same in a Repository and in an EntityManager. The EntityManager one needs a ->from() method, while the Repository one guesses the 'from' table and takes instead just a constructor argument for the alias.
    • The field names, like 'id', 'author_id', 'datetime' etc should not be the names of the fields in your database, but the names of the properties of your doctrine entities. They might be the same, but they're probably camelCase instead of snake_case (e.g., 'authorId'), or they could be completely different. Check your entities to make sure.
    • Similarly, I'm assuming that the $post entities have a $tag field properly defined via doctrine as a ManyToMany relation. If that's the case, Doctrine will know on its own how to join that property by its name, so that the ->innerJoin method will only need one additional parameter: the alias. If you could include your entities definitions, that would help troubleshoot any additional problems.
    • Is post.tile (at the end of the SELECT clause) on purpose or is it a misspelling of post.title?