Raw SQL Queries
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.
| // ... lines 1 - 18 | |
| class FortuneCookieRepository extends ServiceEntityRepository | |
| { | |
| // ... lines 21 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // $result = $this->createQueryBuilder('fortuneCookie') | |
| // ... lines 29 - 40 | |
| // ->getSingleResult(); | |
| // ... line 42 | |
| $conn = $this->getEntityManager()->getConnection(); | |
| dd($conn); | |
| // ... lines 45 - 46 | |
| } | |
| // ... 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.
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 43 | |
| $sql = 'SELECT * FROM fortune_cookie'; | |
| // ... lines 45 - 46 | |
| } | |
| // ... lines 48 - 92 |
Now that we have the query string, we need to create a Statement with $stmt = $conn->prepare() and pass $sql.
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 44 | |
| $stmt = $conn->prepare($sql); | |
| // ... line 46 | |
| return $result; | |
| // ... 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().
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 44 | |
| $stmt = $conn->prepare($sql); | |
| $result = $stmt->executeQuery(); | |
| // ... lines 47 - 48 | |
| } | |
| // ... 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().
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 46 | |
| dd($result->fetchAllAssociative()); | |
| // ... lines 48 - 49 | |
| } | |
| // ... 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.
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... 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'; | |
| // ... lines 45 - 49 | |
| } | |
| // ... 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().
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 45 | |
| $result = $stmt->executeQuery([ | |
| 'category' => $category->getId(), | |
| ]); | |
| // ... lines 49 - 51 | |
| } | |
| // ... 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.
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 45 | |
| $stmt->bindValue('category', $category->getId()); | |
| // ... lines 47 - 50 | |
| } | |
| // ... 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().
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 47 | |
| dd($result->fetchAssociative()); | |
| // ... lines 49 - 50 | |
| } | |
| // ... 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().
| // ... lines 1 - 25 | |
| public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats | |
| { | |
| // ... lines 28 - 48 | |
| return new CategoryFortuneStats(...$result->fetchAssociative()); | |
| } | |
| // ... 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.
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.