JOINs!

Hi,

Is it possible to do this raw SQL with DQL?

`SELECT from prospect INNER JOIN ( SELECT pd.
FROM prospect_details pd
INNER JOIN (

  SELECT max(id) as detail_id
  FROM prospect_details
  WHERE prospect_details.user_id = 14
  GROUP BY prospect_id

) pb on pb.detail_id = pd.id
) pd on pd.prospect_id = prospect.id;`

Between prospect and prospect_details, the relation is ManyToOne (One prospect can have multiple prospect_details but a prospect_details has only one prospect).

I see that I can use raw SQL to do it but was wondering if this would be possible with DQL.

Thx in advance!

Hey be_tnt

I'm not an expert on transforming complex SQL's to DQL but what I usually do when I'm on those situations is to write the raw SQL first, and then try to convert it into DQL but if that's taking me a lot of time what I rather do is to execute the raw SQL but fetching only the ids, and then, execute another query for fetching the objects of *those* ids.

Cheers!

Hello!

Maybe I am thinking in a wrong way. My goal is to get the last prospectDetails row (the one with the biggest ID) linked to each prospect. What if I do:

`$this->createQueryBuilder('p')

->select('p')
->join('p.prospectDetails', 'pd')
->addSelect('pd')
->orderBy('p.createdAt', 'ASC')
->addOrderBy('pd.id', 'ASC')`

and in twig when needed to display the column of the last prospect details, I first get the last element of prospectDetails array:

prospect.prospectDetails|last

I tested it and it's working BUT what about performance? I do not need all prospectDetails row at that time. If I do not have a lot of rows, that's ok but what if I have thousand of rows per prospect?? What do you think about it?

Hey Lydie,

When we're talking about performance - it's difficult to say for sure, you just need to try and see if you have any problems as your project grows. But agree, it sounds like you better need to execute the second query to fetch the only one latest ID instead of fetching all the related data and use only latest. But as always, it depends. You can go this way and just monitor things, and when you will notice some delays in this query - it would be time do some performance optimization. Just don't try to do performance optimization too early. But of course, if you see the potential problem and know 100% that it will become a real problem later - better to go the correct way in the beginning. Though it may also depends how difficult performance optimization might be, probably better to do it later when needed then worry too much on the early stage and spend a lot of time on it. So, it depends :)

Cheers!

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

Thanks Ryan

Greetings

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!

Hy Ryan
First of all, thanks a lot for the great symfony casts!

I've nearly the same question, as you have answered above, but not exactly ;-)
I've the tables media and tags with a ManyToMany relationship. Now I would like to query for all medias wich have ALL the selected tags. I found three ways for this:
first way:<br />$qb->leftJoin('m.tags', 't');<br />$qb->andWhere('t.id IN(:tags)');<br />$qb->setParameter('tags', $tags);

second way:<br />foreach ($tags as $tag){<br />$qb->andWhere('t.id = :tags');<br />$qb->setParameter('tags', $tag);<br />

third way:`
$i = 0;
foreach ($tags as $tag){

        $qb->leftJoin('m.tags', 't_' . $i);
        $qb->andWhere('t_' . $i . '.id = :tags');
        $qb->setParameter('tags', $tag);
        $i++;
    }

`

But in all ways I get the medias where only one tag matches. But I would like to query only for medias where ALL the selected tags match.

Thanks a lot for a hint!
Michael

Hi Michael K. ,
Did you solve this ? I'm looking to do exactly the same.

Hi kaizoku

I solved the problem like this:
`
foreach($tags as $key => $tag) {

            $inTag = [$tag];
            $field = sprintf('tag_%s', $key);
            $qb->leftJoin('m.tags', 't_' . $key);
            $qb->andWhere(sprintf('t_' . $key . ' IN (:%s)', $field));
            $qb->setParameter($field, $inTag);
        }`

Does it also work for you?

Cheers Michael

Thank you very much Michael K. I'll give it a try !

Hey Michael K.

I'm not sure about this but probably you could firstly get all tag objects and then execute this query:

// MediaRepository.php
$qb->createQueryBuilder('m')
      ->leftJoin('m.tags', 't');
      ->andWhere('t IN(:tags)');
      ->setParameter('tags', $tags);

Cheers!

Hi Diego

Thanks a lot for the fast answer!
I build now an array of all tag objects from the tag ids and use your queryBuilder like:`
array:2 [▼
59 => Tag {#1289 ▼

#id: 59
-medias: PersistentCollection {#1282 ▶}
#name: "Burg"

}

67 => Tag {#1279 ▼

#id: 67
-medias: PersistentCollection {#1278 ▶}
#name: "Bus"

}
]

$qb = $this->createQueryBuilder('m')

        ->leftJoin('m.tags', 't')
        ->andWhere('t IN(:tags)')
        ->setParameter('tags', $tags);

`
but the result is still the same;-)
Have you an other idea?

Best regards!
Michael

Hmm, that's a tough one!

Let's use a similar version of your second option:

$qb->createQueryBuilder('m')
      ->leftJoin('m.tags', 't');

foreach($tags as $key => $tag) {
    $inTag = [$tag];
    $field = sprintf('tag_%s', $key); 

    $qb->andWhere(sprintf('t' IN (:%s)', $field)
          ->setParameter($field, $inTag);
}

I'm not sure if this is going to work but give it a try and let know if it worked :)

With your code, it works for only one selected tag. But when I select two or more tags, no medias will be found.
After some tries I found a working solution:`
$qb = $this->createQueryBuilder('m');

        foreach($tags as $key => $tag) {
            $inTag = [$tag];
            $field = sprintf('tag_%s', $key);
            $qb->leftJoin('m.tags', 't_' . $key);
            $qb->andWhere(sprintf('t_' . $key . ' IN (:%s)', $field));
            $qb->setParameter($field, $inTag);
        }

`
In the web I found a hint, that we have to join the table tag for every tag.
Do you think this is the correct way to do this?

Cheers Michael

But now, the other andWhere will be ignored. I add them like:<br />if (array_key_exists('author', $searchData) && !!$searchData['author']) {<br />$qb->andWhere('m.author LIKE :author');<br />$qb->setParameter('author', '%' . $searchData['author'] . '%');<br />}

An idea why only the tag queries are working?

Hey Michael K.!

Oof, this IS tough. I'm glad you found the solution.

> In the web I found a hint, that we have to join the table tag for every tag.

This IS what I was also thinking - though I was kind of expecting an inner join. Honestly, this is the type of super-complex query that I just try to get working in MySQL first by manually trying queries, googling, etc until I get it right. And THEN I translate it back to the query builder... unless it's complex - than I don't even do that (and just use raw SQL).

So, for this last piece, that would be my advice: try the query in raw SQL. As a starting point, you could click on the web debug toolbar icon, find this query, and click "Display runnable query" (text might not be exact). Copy that, then start tweaking it to see what happens (by the way, some of the table and column aliases they use in that "copiable" query are confusing - so I sometimes clean them up a little bit.

My guess is that the problem is due to the left joins. When you do a left join, each row in media may suddenly return 10 results (if you're left joining over to 10 rows). I would try an INNER JOIN, or you may ultimately need a sub-query. Honestly, there are FAR smarter people than me when it comes to complex SQL queries - so I can't give you a great answer - just some debugging direction.

Let us know how it goes! Cheers!

Hi Ryan

First, happy new year to you!

Thanks a lot for your detailed answer! With this hints I tried hard to find a solutions for all my queries. There are even more than I described above. ;-) But now, uff, they are working fine!
One thing I noticed is the order of the "andWhere"- queries is important and sometimes not that logical for me.

Best regards!
Michael

Hey Michael K.!

Woo! Nice job! About the order of the andWhere, it "shouldn't" matter, but yes, in *can* matter in some cases - especially if you have an "OR" in your query. I often will group multiple things into a single andWhere() if I need to be more clear about how I want it to work. We talk a little bit about that here: https://symfonycasts.com/sc...

Happy new year to you too!

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

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!

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));
}

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!

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
<br />[Semantical Error] line 0, col 155 near 'tag<br />': Error: Invalid PathExpression. Must be a StateFieldPathExpression.<br />

So what I am doing wrong?

Hey Peter K.
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/screencast/doctrine-queries/raw-sql-queries

Cheers!

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 MolloKhan will have an answer and I am making some stupid mistake somewhere.

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!

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!

Hey Peter,

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

Cheers!