Go Pro with Doctrine Queries (Legacy)

Hey Jonathan!

Thanks for the nice words! About the query: I'm not sure, I often have to play with things to see how they'll come out, but that's usually more because I'm trying to structure the SQL so that I get the exact result I want (e.g. 1 row, 2 columns, instead of 10 rows with 2 columns, like you have here).

In this case, the SQL that's being generated looks like it's returning multiple rows. To fix that (if it's possible), it's probably a matter of structuring your joins, or something similar. In theory, if you did that correctly, you'd get an array result with just 1 items in it instead of 0 (which I realize still isn't quite perfect - I can't remember now if one of the other query methods will flatten this for you).

But, I do have one suggestion: use select() instead of addSelect() unless you're purposefully intending to get that extra "stuff" back. By that I mean that you have an avgOne, avgTwo and a "0" index, which is an array of 10 objects. If you use select() instead of addSelect(), you should be able to get back just avgOne and avgTwo.

I hope that's at least *somewhat* helpful :)

Hi Henri!

Hmm, that's interesting! So, it appears that your query is retrieving a collection of User object, but you expect Post object (looking at the code, I also expect Post objects!). I'm not sure what the problem is, but I would remove different parts of your code until you get the desired result:

A) Remove the ->select() and the addSelect() to see if this changes things.
B) I'm not sure what the $this->addUserJoinAndSelect() is doing (I don't see that code here), so it could be part of the problem.

Let me know what you find out! Mostly, it looks like you're doing just fine - there's something in there that's making "User" the main thing we're querying for, instead of Post... it's odd.

Cheers!

Hey Shairyar!

If you want, you can just execute the raw SQL in Doctrine: https://knpuniversity.com/screencast/doctrine-queries/raw-sql-queries. But I'm guessing you really want to know how to convert it. The key in converting is this: replace table references with class references. And a second key: don't include the ON parts in a JOIN - Doctrine is already aware of these. So, in your case, the true DQL query would look something like this:

SELECT u FROM AppBundle:User u LEFT JOIN user.applications a LEFT JOIN a.offers o

In order to LEFT JOIN from user to your Apply entity (pe_apply table), you need to have a User.applications property setup, which will be a OneToMany relationship. That's what the user.applications stuff means.

But also, what's the goal of the query? If you do the above, it will only return User entities to you. In Doctrine, there is no point in doing JOINs, unless you actually select more columns or use the JOIN'ed tables in WHERE statements (for example). For example, we use a join and then actually select a few columns from the joined tables in this tutorial - you can see it here: https://knpuniversity.com/screencast/doctrine-queries/select-specific-fields.

Let me know if this makes sense!

Hi,

Thanks, reason I am running this query is I intend to pull a user's record whose data exist in User and Apply table, Apply table basically holds the applicant's application data and the Offer table hold the status of their application whether it was approved or rejected.

Now I am able to pull this information using the following query

$qb = $this->createQueryBuilder('u')
    ->leftJoin('CoreBundle:Apply', 'a', 'WITH', 'a.user = u')
    ->leftJoin('AdminBundle:Offer', 'o', 'WITH', 'o.application = a');

But the problem with this is it only gives me the user's whose data exist in Offer table/Entity, how can i change the above code to fetch ALL users regardless of their data exist in Offer table or not as long as their data is in Apply table

Hey Shairyar!

So when you call $this->createQueryBuilder('u') (from within the UserRepository), this implies the following:

$qb->from('AppBundle:User', 'u')

    ->select('u');

In other words, by default, it selects all data from the User table only, regardless of joins. To grab more data, you have 2 options:

1) Select the specific fields you want back: $qb->addSelect('u.id as userId, a.id as applicationId, o.id as offerId, o.details'); This will return an associative array, very similar to what you'd expect back from a raw SQL query.

2) Simply get the User object back and then use your relationships on User to get the other data (something like):

$applications = $user->getApplications();

This assumes you've setup the OneToMany inverse side of the relationship (this would be an applications property on the User entity).

This method will cause extra queries to be made, but this can avoid that with the strategy shown here: https://knpuniversity.com/screencast/doctrine-queries/joins-reduce-queries

3) Make multiple queries - query for the User object, and then its applications, etc. Always a good fallback option if things get tricky.

Cheers!

Many thanks, I think you should add a feature to your website where developers can ask you queries related to the code they are working on and are stuck. Something like a monthly subscription, i would loveeee that feature and be the first one signing up for it.

Hey Jonathan!

Thanks for the really nice words!!!! You're awesome!

Ok, your query indeed sounds difficult, and is probably a decent use-case for skipping Doctrine and going to SQL. Can you do it with the QB or DQL? Of course! But don't kill yourself trying to figure it out. Afterall, you won't be hydrating (i.e. being returned) objects, so you're not really taking advantage of many of the ORM features in this query anyways.

The best advice I can give on making the query is to go through https://knpuniversity.com/screencast/doctrine-queries/joins again, and when you join, focus on the specific property names on your joins. For example, you'll probably have something like:

    $this->createQueryBuilder('product')
        ->leftJoin('product.scores', 'score')
        // ...

This is scores because you have a OneToMany "scores" property on Product that points to Score. If your property is called something else, rename accordingly. If you haven't mapped this side of the relationship (because the OneToMany side is "inverse", and is optional), then you need to map it. Oh, and overall, with very difficult queries like this, I usually write them in SQL first, and then convert to DQL/QB (or maybe I don't care enough and leave it as SQL).

Good luck!

Ryan,

Thank you! First off, it's just reassuring to know that even someone as experienced as yourself may get a little hung up sometimes on something. Thanks for taking your time to help.

Yes, I do feel like I might just skip it and go the straight SQL route. Then again I was also pondering the thought of creating those averages via PHP. After getting the hydrated results back, I could create functions such as "getAverageForCategory", "getAverageForCriteria", etc within the Company Entity and iterate over the Score objects. It wouldn't cause more database calls because I could grab the Score with a join while getting the company, but I would be grabbing a lot of Scores. I do need access to other parts of the Entity as well (name, avatar, other categories it has, etc).

Heh, I am probably overthinking this a bit, but it's a page that would be hit a lot. I appreciate you letting me pick your brain a bit.

Hey Jonathan!

I'll add a few more thoughts then :)

1) Sometimes more queries is more performant than less queries
2) If you're able to add caching later, then that'll be way more performance than even the best query
3) Even if this page is hit a lot, performance probably doesn't matter yet. Worry about it later, and optimize using my favorite tool for this: Blackfire.

Cheers!

Ryan,

Thanks for the inspiration and advice. You rock.

Aaaand nevermind. I found the Symfony 2.4 screencasts. I'll watch them to see if they answer my questions (probably do :P).

Let me know if that helps :). ManyToMany relationships are both simple, and weird all at the same time. Mostly, it's all about setting the collection onto the collection field. For example, suppose you have a Product and Tag entity, and they are connected via a ManyToMany relationship. To add tags to a product, you would:

$product = // somehow retrieve 1 product, e.g. query for it

$tag1 = new Tag('cool stuff');
$tag2 = new Tag('other stuff');

$product->setTags(array($tag1, $tag2));

$em = $this->getDoctrine()->getManager();
$em->persist($product);
$em->persist($tag1);
$em->persist($tag2);
$em->flush();

Here, I'm creating 2 brand new Tags and saving those, but you could also query for 2 existing tags. But the point is that you actually pass an array of Tag objects to your Product entity. Also, one additional complication is with the owning versus inverse sides of a relationship (we talk about that in the Symfony2 and Symfony3 tutorials). Basically, in this example, you need the Product.tags property to be the "owning" side of the relationship for this to work. If Tag.products were the owning side, then you would need to set it instead (e.g. $tag->setProducts(...)).

I hope this helps! Cheers!

Thanks. :) Yes, that screencast did help. Maybe pretty please update it for Symfony3? In the 2.4 screencast, it was done with getTags()->add(...) and that worked for what I was doing. Off to break ... I mean, write more code.

Awesome, glad it helped! And yep, getTags()->add() will also work - basically the same as the setTags() method :).

Cheers!

Hey Diego!

You can only download each video individually now. We made that change so we could update the individual videos more quickly, without needing to re-encode and upload the big final one.

Cheers!

Hey Caim,

You probably should use next query, because as you pointed: city is a property of place, which (place) is a property of conference. So use this chain in your query:

$qbt = $this->_em->createQueryBuilder();
$qbt
    ->select('conference')
    ->addSelect('place')
    ->addSelect('city')
    ->from('AppBundle:Conference', 'conference')
    ->innerJoin('conference.place', 'place')
    ->innerJoin('place.city', 'city')
;

Cheers!

Ohh >.< Thank ! Can't believe it was so simple when looking through the perspective of entity objects.
Think was doing the exact opposite before.

Haha, yeah, ORM do these things for you! :)

P.S. You better avoid using "$this->_em" internal property. It'll help you to find a right place (right repository) for the query and a bit simplified query builder. So the next code:

$qbt = $this->createQueryBuilder('conference');
    ->addSelect('place')
    ->addSelect('city')
    ->innerJoin('conference.place', 'place')
    ->innerJoin('place.city', 'city')
;

should be in the "ConferenceRepository". Actually, "$this->createQueryBuilder('conference')" do select() and from() method calls for you ;)

Cheers!

Hi there!

This is a very interesting question! My views on views? Hmm :). Most of the time, I'm "against" views... but not strongly. For most developers, the clearest path is to create entities, make queries, etc. In fact, most developers have little to no experience with views - so it's an "extra" thing to learn and manage. However, if you're already familiar with using views, I don't think it's a problem at all - it's just another tool that you should use.

However, for better or worse, Doctrine won't help you with your views - it has almost nothing built into it for working with them. That's not a problem - it just means that you will need to do the work to create the views (unlike with entities, where it writes the SQL for you for tables) and also you will need to write all the mapping code if you want your views to be mapped back to some object (maybe someone has done some work to help with this, but I haven't seen it). Of course, once you've done this, I think the setup would be very nice! But in general, just be careful not to overdo it, especially if you choose to create views but NOT map them back to classes (i.e. you just query directly on the views). This can become a bit magic - since the view logic lives *only* on the database, a new developer will be confused by how we're querying these seemingly "non-existent" tables.

Anyways, I'd be curious to hear what you end up doing with this! There are a lot of ways to handle complex queries - figuring them out, writing raw SQL, writing raw SQL and then mapping back to objects with the ResultSetMapping stuff, creating views - just find one that works for you.

Cheers, and keep in lorrying yourself ;).

I 100% agree that it doesnt seem right to not map a view to classes, seems easier if writing raw sql queries to do that in the entity repo. I was trying the resultSetMapping and it does look pretty complicated. My current solution for joins without a constraint is to, raw sql and PDO::FETCH_CLASS, and have my custom class deal with mapping the result back into object. Its rather unwieldily and can be unclear.

So, in short, the problem I'm trying to solve for is to map 'mostly joined unconstraint foreign key tables' to classes, easily.
I was playing around and using https://github.com/mysql-wo.... There is an option for sortTablesAndViews, but seems unsupported. However if i manually create the 'view' entity class, it works fine. So from my perspective, and correct me if I'm wrong, the raw sql query will still need to written and one can do it on the app side, or a database view. I'm leaning towards making a 'view' but many uncertainties, like if indexes work from the 'copied' table. Or maybe its just easier to do it in the app repo and have a simple foreach to map to an entity obj. I could be over thinking it. What do u think?

I think i'm leaning away from view. Separation of concerns does not seem like it fits with the idea of 'views'