Chapters

16 Chapters | 1:26:33 |

01

Doctrine DQL

7:38
02

The QueryBuilder

2:02
03

andWhere() and orWhere()

7:06
04

JOINs

3:45
05

JOINs and addSelect Reduce Queries

5:22
06

EXTRA_LAZY Relationships

6:07
07

SELECT the SUM (or COUNT)

6:15
08

Selecting Specific Fields

5:09
09

SELECTing into a New DTO Object

3:40
10

Raw SQL Queries

6:52
11

Reusing Queries in the Query Builder

4:42
12

Criteria: Filter Relation Collections

9:09
13

Filters: Automatically Modify Queries

6:59
14

WHERE IN()

2:07
15

Using RAND() or Other Non-Supported Functions

3:11
16

Using GROUP BY to Fetch & Count in 1 Query

6:29

> Symfony 6 > Go Pro with Doctrine Queries

Buy Access to Course

10.

Raw SQL Queries

Autoplay

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.

Start your All-Access Pass

Buy just this tutorial for $6.00

Previous Chapter

Next Chapter

With a Subscription, click any sentence in the script to jump to that part of the video!

The 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.

            Copy Code

                            Show All Lines

                        92 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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.

            Copy Code

                            Show All Lines

                        92 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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.

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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().

            Copy Code

                            Show All Lines

                        94 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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().

            Copy Code

                            Show All Lines

                        95 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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.

            Copy Code

                            Show All Lines

                        95 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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().

            Copy Code

                            Show All Lines

                        97 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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.

            Copy Code

                            Show All Lines

                        96 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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().

            Copy Code

                            Show All Lines

                        96 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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().

            Copy Code

                            Show All Lines

                        95 lines
            |
            src/Repository/FortuneCookieRepository.php
        
                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

Sort By

Peter-K 4 months ago edited

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

$data = [];
foreach($sth->executeQuery()->fetchAllAssociative() as $row) {
    $data[] = new CategoryFortuneStats($row['a'],$row['b'],$row['c']);
}

return $data;

1 | Reply |

MolloKhan SFCASTS Peter-K 4 months 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!

| Reply |

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

| Reply |

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!

| Reply |

Symfony 6

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

Previous Chapter

Next Chapter

Raw SQL Queries

Share this awesome video!

Keep on Learning!

The Connection Object

Writing & Executing the Query

A More Complex Query

Using bindValue()

Hydrating into an Object

4 Comments

Delete comment?

What PHP libraries does this tutorial use?

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
	}

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