This course is still being released! Check back later for more chapters.
Ordering a Relation and "fetch" type
Keep on Learning!
If you liked what you've learned so far, dive in! Subscribe to get access to this tutorial plus video, code and script downloads.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeClick into an "in progress" starship. Then open: templates/starship/show.html.twig. To list the parts, use for part in ship.parts.
This will work like a charm. But with a catch: the order of the parts isn't guaranteed. They pop out of the database in whatever order they want!
I'd rather have these ordered by name. Does this mean we need to write a custom query... and can't use our handy ship.parts anymore?
Fear not, friends! Let's learn some tricks!
Rearranging the Parts
Head over to the Starship entity and find the parts property. Above parts, add a new attribute: #[ORM\OrderBy(['name' => 'ASC'])], not position:
| // ... lines 1 - 13 | |
| class Starship | |
| { | |
| // ... lines 16 - 45 | |
| #[ORM\OrderBy(['name' => 'ASC'])] | |
| private Collection $parts; | |
| // ... lines 48 - 181 | |
| } |
Refresh the page, and got it!
If you're scratching your head wondering why T is coming before c, you haven't forgotten your ABCs. It's just that Postgres is a case-sensitive database. So the uppercase T apparently comes before lowercase C in alphabetical order.
Smart Queries
Check the queries for this page and view the formatted SQL. It queries from starship_part, where starship_id equals our ID, ordered by name ascending: it's exactly the query we want!
The N+1 Problem
Head back to the homepage and open up its template: templates/main/homepage.html.twig. After "arrived", add a div then print out the part count: ship.parts|length:
| // ... lines 1 - 4 | |
| {% block body %} | |
| <main class="flex flex-col lg:flex-row"> | |
| // ... lines 7 - 8 | |
| <div class="px-12 pt-10 w-full"> | |
| // ... lines 10 - 17 | |
| <div class="space-y-5"> | |
| {% for ship in ships %} | |
| <div class="bg-[#16202A] rounded-2xl pl-5 py-5 pr-11 flex flex-col min-[1174px]:flex-row min-[1174px]:justify-between"> | |
| <div class="flex justify-center min-[1174px]:justify-start"> | |
| // ... line 22 | |
| <div class="ml-5"> | |
| // ... lines 24 - 33 | |
| <div> | |
| Arrived: {{ ship.arrivedAt|ago }} | |
| </div> | |
| <div> | |
| Parts: {{ ship.parts|length }} | |
| </div> | |
| </div> | |
| </div> | |
| // ... lines 42 - 52 | |
| </div> | |
| {% endfor %} | |
| </div> | |
| // ... lines 56 - 71 | |
| </div> | |
| </main> | |
| {% endblock %} |
Back on the homepage, it works like a charm. Check out the queries for this page, they're interesting. Some of these look a bit wild because of our pagination, but essentially, we have one query for the starship, and if we search for starship_part, there are 5 extra queries for the parts for each starship.
Here's what's happening: we grab the starships, then as soon as we count ship.parts, Doctrine realizes it doesn't have that data yet. So it fetches all the parts for each ship one by one and counts them. This is a common situation: we have one query for the ships and then one extra query for the parts of each ship. It's known as the N+1 problem: 1 query for the starships, and N queries for the parts of each ship. It's a minor performance problem that we're going to tackle later.
Efficient Querying
But there's a bigger problem here! We query for every starship_part just to count them. We don't need the part data, we just need to know how many we've got. This is minor... until you have a ship with a ton of parts.
To fix this, in the OneToMany in the Starship entity, add a fetch option set to EXTRA_LAZY:
| // ... lines 1 - 13 | |
| class Starship | |
| { | |
| // ... lines 16 - 44 | |
| #[ORM\OneToMany(targetEntity: StarshipPart::class, mappedBy: 'starship', fetch: 'EXTRA_LAZY', orphanRemoval: true)] | |
| // ... line 46 | |
| private Collection $parts; | |
| // ... lines 48 - 181 | |
| } |
Let's go see what that did!
Counting the Parts
Head back to the homepage. Earlier, we had nine queries... Now??? Still nine queries, but the query for the parts changed. Instead of querying for all their data, it just counts them. Much smarter, right?
You might be wondering - I certainly did - why we don't use fetch="EXTRA_LAZY" all the time? First, this is a small performance optimization that you don't need to worry about unless you have a ship full of parts, and you just want to count them. More importantly, depending on if you count or loop over the parts first, this could cause an extra query.
The Criteria System
Onto our next challenge! What if we only want the related parts for a ship that cost above a certain price? Can we still use the ship.parts shortcut or do we need to do a custom query? Stay tuned, we're going to explore the criteria system next.
