| CARVIEW |
Chapters
-
Course Code
Subscribe to download the code!
Subscribe to download the code!
-
This Video
Subscribe to download the video!
Subscribe to download the video!
-
Subtitles
Subscribe to download the subtitles!
Subscribe to download the subtitles!
-
Course Script
Subscribe to download the script!
Subscribe to download the script!
Scroll down to the script below, click on any sentence (including terminal blocks) to jump to that spot in the video!
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 SubscribeThe QueryBuilder is fun to use and powerful. But if you're writing a super complex query... it might be tough to figure out how to transform it into the QueryBuilder format. If you find yourself in this situation, you can always resort to just... writing raw SQL! I wouldn't make this my first choice - but there's no huge benefit to spending hours adapting a well-written SQL query into a query builder.
The Connection Object
Let's see how raw SQL queries work. To start, comment out the ->createQueryBuilder() query. Then, we need to fetch the low-level Doctrine Connection object. We can get that with $conn = $this->getEntityManager()->getConnection(). Toss dd($conn) onto the end so we can see it.
|
Show Lines
|
// ... lines 1 - 18 |
| class FortuneCookieRepository extends ServiceEntityRepository | |
| { | |
|
Show Lines
|
// ... lines 21 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // $result = $this->createQueryBuilder('fortuneCookie') | |
|
Show Lines
|
// ... lines 29 - 40 |
| // ->getSingleResult(); | |
|
Show Lines
|
// ... line 42 |
| $conn = $this->getEntityManager()->getConnection(); | |
| dd($conn); | |
|
Show Lines
|
// ... lines 45 - 46 |
| } | |
|
Show Lines
|
// ... lines 48 - 90 |
| } |
Head over, refresh and... awesome! We get a Doctrine\DBAL\Connection object.
The Doctrine library is actually two main parts. First there's a lower-level part called "DBAL", which stands for "Database Abstraction Library". This acts as a wrapper around PHP's native PDO and adds some features on top of it.
The second part of Doctrine is what we've been dealing with so far: it's the higher-level part called the "ORM "or "Object Relational Mapper". That's when you query by selecting classes and properties... and get back objects.
For this raw SQL query, we're going to deal with the lower-level Connection object directly.
Writing & Executing the Query
Say $sql = 'SELECT * FROM fortune_cookie'. That's as boring as SQL queries can get. I used fortune_cookie for the table name because I know that, by default, Doctrine underscores my entities to make table names.
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 43 |
| $sql = 'SELECT * FROM fortune_cookie'; | |
|
Show Lines
|
// ... lines 45 - 46 |
| } | |
|
Show Lines
|
// ... lines 48 - 92 |
Now that we have the query string, we need to create a Statement with $stmt = $conn->prepare() and pass $sql.
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 44 |
| $stmt = $conn->prepare($sql); | |
|
Show Lines
|
// ... line 46 |
| return $result; | |
|
Show Lines
|
// ... lines 48 - 93 |
This creates a Statement object... which is kind of like the Query object we would create with the QueryBuilder by saying ->getQuery() at the end. It's... just an object that we'll use to execute this. Do that with $result = $stmt->executeQuery().
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 44 |
| $stmt = $conn->prepare($sql); | |
| $result = $stmt->executeQuery(); | |
|
Show Lines
|
// ... lines 47 - 48 |
| } | |
|
Show Lines
|
// ... lines 50 - 94 |
Finally, to get the actual data off of the result, say dd(result->)... and there are a number of methods to choose from. Use fetchAllAssociative().
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 46 |
| dd($result->fetchAllAssociative()); | |
|
Show Lines
|
// ... lines 48 - 49 |
| } | |
|
Show Lines
|
// ... lines 51 - 95 |
This will fetch all the rows and give each one to us as an associative array.
Watch: head back over and... perfect! We get 20 rows for each of the 20 fortune cookies in the system! This is the raw data coming from the database.
A More Complex Query
Okay, let's rewrite this entire QueryBuilder query up here in raw SQL. To save time, I'll paste in the final product: a long string... with nothing particularly special. We're selecting SUM, AS fortunesPrinted, the AVG, category.name, FROM fortune_cookie, and then we do our INNER JOIN over to category.
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 43 |
| $sql = 'SELECT SUM(fortune_cookie.number_printed) AS fortunesPrinted, AVG(fortune_cookie.number_printed) fortunesAverage, category.name FROM fortune_cookie INNER JOIN category ON category.id = fortune_cookie.category_id WHERE fortune_cookie.category_id = :category'; | |
|
Show Lines
|
// ... lines 45 - 49 |
| } | |
|
Show Lines
|
// ... lines 51 - 95 |
The big difference is that, when we do a JOIN with the QueryBuilder, we can just join across the relationship... and that's all we need to say. In raw SQL, of course, we need to help it by specifying that we're joining over to category and describe that we're joining on category.id = fortune_cookie.category_id.
The rest is pretty normal... except for fortune_cookie.category_id = :category. Even though we're running raw SQL, we're still not going to concatenate dynamic stuff directly into our query. That's a huge no-no, and, as we know, opens us up to SQL injection attacks. Instead, stick with these nice placeholders like :category. To fill that in, down where we execute the query, pass 'category' =>. But this time, instead of passing the entire $category object like we did before, this is raw SQL, so we need to pass $category->getId().
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 45 |
| $result = $stmt->executeQuery([ | |
| 'category' => $category->getId(), | |
| ]); | |
|
Show Lines
|
// ... lines 49 - 51 |
| } | |
|
Show Lines
|
// ... lines 53 - 97 |
Ok! Spin over and check this out. Got it! So writing raw SQL doesn't look as awesome... but if your query is complex enough, don't hesitate to try this.
Using bindValue()
By the way, instead of using executeQuery() to pass the category, we could, replace that with $stmt->bindValue() to bind category to $category->getId(). That's going to give us the same results as before, so your call.
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 45 |
| $stmt->bindValue('category', $category->getId()); | |
|
Show Lines
|
// ... lines 47 - 50 |
| } | |
|
Show Lines
|
// ... lines 52 - 96 |
But, hmm, I'm realizing now that the result is an array inside another array. What we really want to do is return only the associative array for the one result. No problem: instead of fetchAllAssociative(), use fetchAssociative().
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 47 |
| dd($result->fetchAssociative()); | |
|
Show Lines
|
// ... lines 49 - 50 |
| } | |
|
Show Lines
|
// ... lines 52 - 96 |
And now... beautiful! We get just that first row.
Hydrating into an Object
Now, you may remember that our method is supposed to return a CategoryFortuneStats object that we created earlier. Can we convert our array result into that object? Sure! It's not fancy, but easy enough.
We could return a new CategoryFortuneStats()... and then grab the array keys from $result->fetchAssociative()... and pass them as the correct arguments.
Or, you can be even lazier and use the spread operator along with named arguments. Check it out: the arguments are called fortunesPrinted, fortunesAverage, and categoryName. Over here, they are fortunesPrinted, fortunesAverage, and name... not categoryName. Let's fix that. Down here, add as categoryName. And then... yep! It's called categoryName.
Now we can use named arguments. Remove the dd() and the other return. To CategoryFortuneStats, pass ...$result->fetchAssociative().
|
Show Lines
|
// ... lines 1 - 25 |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
|
Show Lines
|
// ... lines 28 - 48 |
| return new CategoryFortuneStats(...$result->fetchAssociative()); | |
| } | |
|
Show Lines
|
// ... lines 51 - 95 |
This will grab that array and spread it out across those arguments so that we have three correctly named arguments... which is just kind of fun.
And now... our page works!
Next: Let's talk about organizing our repository so we can reuse parts of our queries in multiple methods.
4 Comments
MolloKhan
SFCASTS
Peter-K
1 year ago
edited
Hey @Peter-K
There are a few ways to do that. The first and most complex is by using the ResultSetMapping of Doctrine. Here's the docs: https://www.doctrine-project.org/projects/doctrine-orm/en/3.2/reference/native-sql.html
A second approach would be using the NEW statement in your query, but I believe it only works with DTOs, let me know if I'm wrong - here's more info about it https://dev.to/jszutkowski/fast-way-to-create-dto-using-doctrine-2dlf
Lastly, my preferred way, using two queries. First I'll write that complex, raw query and only fetch the entity ids, then in a second query, I fetch the entity objects by id. It's not the most performant way but it's way simpler
Cheers!
Brent-M
1 year ago
Hello, I have a complex query with multiple joins and would like to use a raw query. I also have multiple schemas. When I setup up my connection in my repository is there a way to specify the the schema so it the connection does not use the default? Thank you in advance.
$conn = $this->getEntityManager()->getConnection('specify schema');
weaverryan
SFCASTS
Brent-M
1 year ago
Heya @Brent-M!
Sorry for the slow reply! I'm far from an expert on this topic. However, my impression is that:
A) Each entity belongs to one specific entity manager
B) Each entity manager belong to one specific connection
So if you're inside of a repository for an entity, then there should only be one "connection" that for that entity. However, I still think your question is valid: you could put a query in a repository that, perhaps, talks to totally different tables.
To get another connection, what you need is the manager registry - and that is actually injected into your repository already - https://symfony.com/doc/current/doctrine.html#querying-for-objects-the-repository. So, you could tweak the __construct() of your repository to store this registry on a property, then grab the connection from that when you need it - $this->registry->getConnection('specify schema').
Let me know if that helps!
Cheers!
"Houston: no signs of life"
Start the conversation!
What PHP libraries does this tutorial use?
// composer.json
{
"require": {
"php": ">=8.1",
"ext-ctype": "*",
"ext-iconv": "*",
"beberlei/doctrineextensions": "^1.3", // v1.3.0
"doctrine/doctrine-bundle": "^2.7", // 2.9.1
"doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
"doctrine/orm": "^2.13", // 2.15.1
"symfony/asset": "6.2.*", // v6.2.7
"symfony/console": "6.2.*", // v6.2.10
"symfony/dotenv": "6.2.*", // v6.2.8
"symfony/flex": "^2", // v2.2.5
"symfony/framework-bundle": "6.2.*", // v6.2.10
"symfony/proxy-manager-bridge": "6.2.*", // v6.2.7
"symfony/runtime": "6.2.*", // v6.2.8
"symfony/twig-bundle": "6.2.*", // v6.2.7
"symfony/webpack-encore-bundle": "^1.16", // v1.16.1
"symfony/yaml": "6.2.*" // v6.2.10
},
"require-dev": {
"doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.4
"symfony/maker-bundle": "^1.47", // v1.48.0
"symfony/stopwatch": "6.2.*", // v6.2.7
"symfony/web-profiler-bundle": "6.2.*", // v6.2.10
"zenstruck/foundry": "^1.22" // v1.32.0
}
}
How would the code change if you would want to return array of objects of CategoryFortuneStats?
Would you have a foreach loop in repository?
Something like
?