Buy
Buy

I love JOINs. I do! I mean, a query isn't truly interesting unless you're joining across tables to do some query Kung fu. Doctrine makes JOINs really easy - it's one of my favorite features! Heck, they're so easy that I think it confuses people. Let me show you.

Right now, our search matches fields on the Category, but it doesn't match any of the fortunes in that Category. So if we search for cat, we get the no-results frowny face. Time to fix it!

LEFT JOIN in SQL

The query for this page is built in the search() function. Let's think about what we need in SQL first. That query would select FROM category, but with a LEFT JOIN over to the fortune_cookie table ON fortune_cookie.categoryId = category.id. Once we have the LEFT JOIN in normal SQL land, we can add a WHERE statement to search on any column in the fortune_cookie table.

SELECT cat.* FROM category cat
    LEFT JOIN fortune_cookie fc ON fc.categoryId = cat.id
    WHERE fc.fortune LIKE '%cat%';

ManyToOne and OneToMany Mapping

In Doctrine-entity-land, all the relationships are setup. The FortuneCookie has a ManyToOne relationship on a category property:

... lines 1 - 12
class FortuneCookie
{
... lines 15 - 23
/**
* @var Category
*
* @ORM\ManyToOne(targetEntity="Category", inversedBy="fortuneCookies")
* @ORM\JoinColumn(nullable=false)
*/
private $category;
... lines 31 - 174
}

And inside Category, we have the inverse side of the relationship: a OneToMany on a property called fortuneCookies:

... lines 1 - 13
class Category
{
... lines 16 - 38
/**
* @ORM\OneToMany(targetEntity="FortuneCookie", mappedBy="category")
*/
private $fortuneCookies;
... lines 43 - 111
}

Mapping this side of the relationship is optional, but we'll need it to do our query.

Adding the leftJoin Query

Let's go add our LEFT JOIN to the query builder! If you're thinking there's a leftJoin method, winner! And this time, we are going to use it. Join on cat.fortuneCookies. Why fortuneCookies? Because this is the name of the property on Category for this relationship.

The second argument to leftJoin() is the alias we want to give to FortuneCookie, fc::

... lines 1 - 23
public function search($term)
{
return $this->createQueryBuilder('cat')
... lines 27 - 29
->leftJoin('cat.fortuneCookies', 'fc')
... lines 31 - 33
}
... lines 35 - 36

And right away, we can see why Doctrine JOINs are so easy, I mean confusing, I mean easy. This is all we need for a JOIN - we don't have any of the LEFT JOIN ON fortune_cookie.categoryId = category.id kind of stuff. Sure, this will be in the final query, but we don't need to worry about that stuff because Doctrine already knows how to join across this relationship: all the details it needs are in the relationship's annotations.

The cat.fortuneCookies thing only works because we have the fortuneCookies OneToMany side of the relationship. Adding this mapping for the inverse side is optional, but if we didn't have it, we'd need to add it right now: our query depends on it.

LEFT JOIN, check! And just like normal SQL, we can use the fc alias from the joined table to update the WHERE clause. I'll break this onto multiple lines for my personal sanity and then add OR fc.fortune LIKE :searchTerm because fortune is the name of the property on FortuneCookie that holds the message:

... lines 1 - 23
public function search($term)
{
return $this->createQueryBuilder('cat')
->andWhere('cat.name LIKE :searchTerm
OR cat.iconKey LIKE :searchTerm
OR fc.fortune LIKE :searchTerm')
->leftJoin('cat.fortuneCookies', 'fc')
->setParameter('searchTerm', '%'.$term.'%')
->getQuery()
->execute();
}
... lines 35 - 36

Moment of truth! We've got a match! Our fortunes are being searched.

JOINing, but Querying for the same Data

Even though we now have a LEFT JOIN, the result of the query is no different: it still returns an array of Category objects. We can and will do some JOINs in the future that actually select data from the joined table. But if all you do is JOIN like we're doing here, it doesn't change the data that's returned.

Leave a comment!

  • 2018-03-09 Victor Bocharsky

    Hey Peter,

    Haha, ah, yes, looks like it was a necessary change for my query ;) Glad you got it working!

    Cheers!

  • 2018-03-09 Peter Kosak

    Thanks Victor,

    I have created another query with Left Join that also didnt work :D so I was starting to be curious where is the mistake. I added OneToMany relationship on Tag Entity and magically everything is working LOL :D

    Thank you guys!

  • 2018-03-09 Victor Bocharsky

    Hey Peter,

    Probably you need to obviously return tag ID, not Tag:


    $dql = 'SELECT t
    FROM App\Entity\Tag t
    WHERE t.platform = :platform AND t.id NOT IN
    (
    SELECT tt.id
    FROM App\Entity\Certificate c
    INNER JOIN App\Entity\Tag tt
    WHERE c.status = :status
    )';
    $query = $this->getEntityManager()->createQuery($dql);
    return $query->execute([
    'platform' => $platform,
    'status' => 'Deleted'
    ]);

    But in this case you can simplify your query: just use join without subquery. If you want to avoid joins, I'd recommend you to execute 2 queries separately, first one is to fetch all the IDs WHERE c.status = :status, and then another one where you can pass IDs from the 1st query.

    Cheers!

  • 2018-03-09 Peter Kosak

    I have tried it. It would work but the issue is that I need to return Tag Entity instead of array of fields. I could create from results Entity but this is just ridiculous that such an easy sql query can not be done easily using dql. I believe weaverryan will have an answer and I am making some stupid mistake somewhere.

  • 2018-03-08 Diego Aguiar

    Hey Peter Kosak
    I think you have to select fields like this:


    SELECT t.* FROM table t

    Also try using your table names instead of the namespace of an entity, i.e change `App\Entity\Tag` to 'tags_table_name`
    And just in case, try using the connection for executing the query as shown in this video: https://knpuniversity.com/s...

    Cheers!

  • 2018-03-08 Peter Kosak

    Hi,

    I am struggling to create sub query using doctrine.

    My raw query works fine and return records.

    Raw SQL

    SELECT *
    FROM tag
    WHERE platform_id = 2 AND id NOT IN
    (
    SELECT tag_id
    FROM certificate
    WHERE status = "Deleted"
    );

    I have tried this

    $dql = 'SELECT t
    FROM App\Entity\Tag t
    WHERE t.platform = :platform AND t.id NOT IN
    (
    SELECT c.tag
    FROM App\Entity\Certificate c
    WHERE c.status = :status
    )';
    $query = $this->getEntityManager()->createQuery($dql);
    return $query->execute([
    'platform' => $platform,
    'status' => 'Deleted'
    ]);

    but it will throw query exception

    [Semantical Error] line 0, col 155 near 'tag
    ': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

    So what I am doing wrong?

  • 2016-05-22 weaverryan

    It looks like $permission is an array! I assumed it was a Role object. In that case, because it's an array, in Twig, when you call permission.description, it is equivalent to $permission['description'] in PHP. So, change your code to that :).

    Cheers!

  • 2016-05-19 BondashMaster

    That makes sense.
    I made this but now I get an error that says "Error: Call to a member function getDescription() on array".

    foreach ($permissions as $permission)
    {
    $description=$permission->getDescription();
    $routa = $permission->getUrl();
    $menu->addChild($description , array('route' => $routa));
    }

  • 2016-05-19 weaverryan

    Hi there!

    When you call permission.description in Twig, it actually calls $permission->getDescription() in PHP. You can't use $permission->description because description is likely a private property, so that's why that fails :).

    I hope this helps!

  • 2016-05-19 BondashMaster

    How can I use the QueryBuilder Result in a class instead of using them in twig?
    My MarkedRolePermission Repository returns exactly what I want (I tested it with a twig loop.)
    But instead of getting that data. I would like to use it lo populate a menu like this:

    $menu = $factory->createItem('root');

    $permissions = $this->container->get('doctrine')->getManager()
    ->getRepository('MenugenBundle:Role')->MarkedRolePermissions('1');

    foreach ($permissions as $permission)
    {
    $menu->addChild($permission->description, array('route' => $permission->url));
    $menu->addChild($permission.description, array('route' => $permission.url));
    }

    return $menu;
    The thing is that $permision->description or $permision.description are not working. The last one is the one that I use in twig to loop and print.
    Thanks

  • 2015-09-11 weaverryan

    Hey Juan!

    It's a good question :). Imagine there is a Tag entity, and a FortuneCookie has a ManyToMany relationship with Tag. In the database, this means there is probably a join table - something like fortune_cookie_tag.

    The way you query is actually exactly the same as with a ManyToOne or OneToMany relationship. Imagine the Tag entity has a 'tagString' property, which is the actual text for the tag. Here, we want to make a query that only returns FortuneCookies that have a relationship to a Tag matching this 'tagString'. Also, imagine that the ManyToMany relationship is done on a "tags" property in the FortuneCookie entity:


    // FortuneCookieRepository.php
    public function findAllMatchingTag($tagString)
    {
    return $this->createQueryBuilder('fc')
    ->leftJoin('fc.tags', 'tags')
    ->andWhere('tags.tagString = :tagString')
    ->setParameter('tagString', $tagString)
    ->getQuery()
    ->execute();
    }

    The important (maybe weird) thing is this: you completely ignore the fact that there is a join table (e.g. fortune_cookie_tag) in the database. You join directly from the FortuneCookie's "tags" property over to the Tag entity - leftJoin('fc.tags', 'tags'). In the background, Doctrine actually joins to the join table AND then from the join table to the tag table. But for us, we just join right across the property, as if this were just a simple OneToMany relationship (as we showed in this chapter).

    Does that make sense? Cheers!

  • 2015-09-11 Juan Luis Garcia

    How can I do queries Many To Many with QueryBuilder?
    I question that after watching the tutorial.

    Thanks Ryan

    Greetings