SELECTing into a New DTO Object

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

Having the flexibility to select any data we want is awesome. Dealing with the associative array that we get back is... less awesome! I like to work with objects whenever possible. Fortunately, Doctrine gives us a simple way to improve this situation: we query for the data we want... but tell it to give us an object.

Creating the DTO

First, we need to create a new class that will hold the data from our query. I'll make a new directory called src/Model/... but it could be called anything. Call the class... how about CategoryFortuneStats.

The entire purpose of this class is to hold the data from this specific query. So add a public function __construct() with a few public properties for simplicity: public int $fortunesPrinted, public float $fortunesAverage, and public string $categoryName.

            
            15 lines
            
            src/Model/CategoryFortuneStats.php
        
                                                    ... lines 1 - 4
                            
            class CategoryFortuneStats
        
            {
        
                public function __construct(
        
                    public int $fortunesPrinted,
        
                    public float $fortunesAverage,
        
                    public string $categoryName,
        
                )
        
                {
        
                }
        
            }

Beautiful!

Back in the repository, we actually don't need any Doctrine magic to use this new class. We could query for the associative array, then return new CategoryFortuneStats() and pass each key into it.

That's a great option, dead simple and then this repository method would return an object instead of an array. But... Doctrine makes this even easier thanks to a little-known feature.

Add a new ->select() that will contain all of these selects in one. Also add a sprintf(): you'll see why in a minute. Inside, check this out! Say NEW %s() then pass CategoryFortuneStats::class for that placeholder. Basically, we're saying NEW App\Model\CategoryFortuneStats()... I just wanted to avoid typing that long class name.

Inside of NEW, grab each of the 3 things that we want to select and paste them, as if we're passing them directly as the first, second and third arguments to our new class's constructor.

            
            90 lines
            
            src/Repository/FortuneCookieRepository.php
        
                                                    ... lines 1 - 18
                            
            class FortuneCookieRepository extends ServiceEntityRepository
        
            {
        
                                                    ... lines 21 - 25
                            
                public function countNumberPrintedForCategory(Category $category): array
        
                {
        
                    $result = $this->createQueryBuilder('fortuneCookie')
        
                        ->select(sprintf(
        
                            'NEW %s(
        
                                SUM(fortuneCookie.numberPrinted) AS fortunesPrinted,
        
                                AVG(fortuneCookie.numberPrinted) fortunesAverage,
        
                                category.name
        
                            )',
        
                            CategoryFortuneStats::class
        
                        ))
        
                                                    ... lines 37 - 44
                            
                }
        
                                                    ... lines 46 - 88
                            
            }

Isn't that cool? Let's dd($result) so we can see what it looks like!

No Aliasing with NEW

If we head over and refresh... oh... I get an error: T_CLOSE_PARENTHESIS, got 'AS'. When we select data into an object, aliasing is no longer needed... or allowed. And it makes sense: Doctrine will pass whatever this is to the first argument of our constructor, this to the second argument, and this to the third. Since aliases don't make sense anymore... remove them.

            
            90 lines
            
            src/Repository/FortuneCookieRepository.php
        
                                                    ... lines 1 - 25
                            
                public function countNumberPrintedForCategory(Category $category): array
        
                {
        
                    $result = $this->createQueryBuilder('fortuneCookie')
        
                        ->select(sprintf(
        
                            'NEW %s(
        
                                SUM(fortuneCookie.numberPrinted),
        
                                AVG(fortuneCookie.numberPrinted),
        
                                category.name
        
                            )',
        
                            CategoryFortuneStats::class
        
                        ))
        
                                                    ... lines 37 - 44
                            
                }
        
                                                    ... lines 46 - 90

If we check it now... got it! I love it! We have an object with our data inside!

Let's celebrate by cleaning up our method. Instead of an array, we're returning a CategoryFortuneStats. Also remove the dd($result) down here.

            
            89 lines
            
            src/Repository/FortuneCookieRepository.php
        
                                                    ... lines 1 - 25
                            
                public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats
        
                {
        
                                                    ... lines 28 - 43
                            
                }
        
                                                    ... lines 45 - 89

Back in the controller, to show off how nice this is, change $result to... how about $stats. Then we can use $stats->fortunesPrinted, $stats->fortunesAverage, and $stats->categoryName.

            
            47 lines
            
            src/Controller/FortuneController.php
        
                                                    ... lines 1 - 12
                            
            class FortuneController extends AbstractController
        
            {
        
                                                    ... lines 15 - 30
                            
                public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
        
                {
        
                                                    ... lines 33 - 36
                            
                    $stats = $fortuneCookieRepository->countNumberPrintedForCategory($category);
        
                                                    ... line 38
                            
                    return $this->render('fortune/showCategory.html.twig',[
        
                        'category' => $category,
        
                        'fortunesPrinted' => $stats->fortunesPrinted,
        
                        'fortunesAverage' => $stats->fortunesAverage,
        
                        'categoryName' => $stats->categoryName,
        
                    ]);
        
                }
        
            }

Now that we've tidied up a bit, let's check to see if this still works. And... it does.

Next: Sometimes queries are so complex... the best option is just to write the darn thing in raw, native SQL. Let's talk about how to do that.

Leave a comment!

vince-amstz | posted 7 days ago | edited

Hello there!
I have a question on the SQL functions that we used in this tuto are available because we used in this tutorial beberlei/DoctrineExtensions. But currently, this project is "well blocked" to PHP 8.0 instead of PHP 8.2, and in November PHP 8.3.

Has anyone found a solution to this? I searched on packagist and github and found nothing maintained.

Should I switch to native SQL queries to be on the safe side for the future on long-term projects? But you lose the object ...

MolloKhan | SFCASTS | vince-amstz | posted 7 days ago | edited

Hey @vince-amstz

This tutorial was built on PHP 8.1, and I assume it should work with 8.2 too. Anyways, I noticed that DoctrineExtensions library added support for PHP 8.2 https://github.com/beberlei/DoctrineExtensions/commit/7cfeb9ce1265f43d5007362d0ef9f7c9c68015ef.
Did you get a problem trying to use it in your project?

Cheers!

vince-amstz | MolloKhan | posted 7 days ago

Yes, you're right, it works. I thought PHP was in 8.0 on the tutorial and that's why it worked.

I had this question just before using it in another project, I saw the commit you sent but also that the last relase was 3 years old and supported PHP 8.0
https://github.com/beberlei/DoctrineExtensions/releases/tag/v1.3.0

Am I missing something?

MolloKhan | SFCASTS | vince-amstz | posted 7 days ago | edited

Oh, yea, you're looking at version 1.3.0, they are still active but it seems like they have not released any new version yet, they have been committing everything to master https://github.com/beberlei/DoctrineExtensions/commits/master
I don't know the reason but perhaps you could open a ticket in the repository

1 Reply

Sidi-LEKHAIFA | posted 20 days ago | edited

hello,
I have a question regarding the use of aliases for the DTO, I'm trying to do this, except that for each entity I have the name field that I want to hydrate the DTO with this, but I can't do it!

$qb
            ->select(
                  sprintf(
                    'NEW %s(
                        c.id,
                        c.email,
                        p.name,
                        m.name,
                        so.name
                    )',
                    OneCustomerBODTO::class
                )
            )
            ->leftJoin('c.partenaire', 'p')
            ->leftJoin('c.medium', 'm')
            ->leftJoin('c.sourceOrigin', 'so');
            // ...otherCode

AND when I try to do this, I get an error, if someone could help me please

$qb
            ->select(
                  sprintf(
                    'NEW %s(
                        c.id,
                        c.email,
                        p.name as partenaire,
                        m.name as medium,
                        so.name as source
                    )',
                    OneCustomerBODTO::class
                )
            )
            ->leftJoin('c.partenaire', 'p')
            ->leftJoin('c.medium', 'm')
            ->leftJoin('c.sourceOrigin', 'so');
            // ...otherCode

Error message :
[Syntax Error] line 0, col 587: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_CLOSE_PARENTHESIS, got 'as'

Victor | SFCASTS | Sidi-LEKHAIFA | posted 20 days ago | edited

Hey @Sidi-LEKHAIFA ,

Hm, tricky case... you can try to remove that as keyword, it should be option in SQL, so writing field aliases as p.name AS partenaire and p.name partenaire are both valid. Maybe it might work this way... but I think it will not work either because seems that does not support aliases at all. If so, the only possible solution is to name the field in the DB to make it unique. That will not be ideal, but still a valid workaround.

But even with duplicated column names, I suppose it should still work? Did you try to use it without aliases? Is "partenaire" overridden by "medium" and then by "source"? IIRC that should pass fields to arguments by ordinal numbers.

Cheers!

Sidi-LEKHAIFA | Victor | posted 20 days ago

Hello @Victor,
in fact, renaming fields doesn't seem very sensible to me, since I use these fields in a lot of places.

I thought it would overload the values since they'd have the same aliases, but no, it works perfectly, so I don't need the aliases.

Thank you very much

Victor | SFCASTS | Sidi-LEKHAIFA | posted 20 days ago | edited

Hey @Sidi-LEKHAIFA

Perfect! Yeah, that's how it's supposed to work I think. And thanks for confirming it works without overrides :)

Cheers!

seb-jean | posted 23 days ago

Hello,
Great this video :).

In my query I have both a select() and an addSelect(). How could I do to have this New DTO Object system?

Victor | SFCASTS | seb-jean | posted 22 days ago | edited

Hey @seb-jean ,

I think you need to rewrite your logic into a single select(), i.e. prepare everything you need before the select() and use it there :)

Cheers!

seb-jean | Victor | posted 20 days ago

Thanks Victor

julien_bonnier | posted 2 months ago

Woah!!!

This is the reason why I keep doing almost every SymfonyCasts tutorials. Even though I'm very familiar with Doctrine, I did not know that trick! It made my day!

Keep it up, friends!

MolloKhan | SFCASTS | julien_bonnier | posted 2 months ago | edited

You're welcome @julien_bonnier :)

Sidi-LEKHAIFA | posted 2 months ago

It's really great this way, do you know if it only works if the DTO hydrates through the constructor or if the DTO can hydrate through the getters and setters?

MolloKhan | SFCASTS | Sidi-LEKHAIFA | posted 2 months ago | edited

Hey @Sidi-LEKHAIFA

That's a good question. I believe Doctrine does not support setter methods. They are not explicit about it in the docs though https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#new-operator-syntax
I'd just give it a try to confirm it

Cheers!

1 Reply

Sidi-LEKHAIFA | MolloKhan | posted 2 months ago | edited

Thanks @MolloKhan for your answer, yes I confirm that it doesn't work with setters, I just tried it. but it's good to know

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

Edit on GitHub

TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

SELECTing into a New DTO Object

Keep on Learning!

Creating the DTO

No Aliasing with NEW

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 4
	class CategoryFortuneStats
	{
	public function __construct(
	public int $fortunesPrinted,
	public float $fortunesAverage,
	public string $categoryName,
	)
	{
	}
	}

	... lines 1 - 18
	class FortuneCookieRepository extends ServiceEntityRepository
	{
	... lines 21 - 25
	public function countNumberPrintedForCategory(Category $category): array
	{
	$result = $this->createQueryBuilder('fortuneCookie')
	->select(sprintf(
	'NEW %s(
	SUM(fortuneCookie.numberPrinted) AS fortunesPrinted,
	AVG(fortuneCookie.numberPrinted) fortunesAverage,
	category.name
	)',
	CategoryFortuneStats::class
	))
	... lines 37 - 44
	}
	... lines 46 - 88
	}

	... lines 1 - 25
	public function countNumberPrintedForCategory(Category $category): array
	{
	$result = $this->createQueryBuilder('fortuneCookie')
	->select(sprintf(
	'NEW %s(
	SUM(fortuneCookie.numberPrinted),
	AVG(fortuneCookie.numberPrinted),
	category.name
	)',
	CategoryFortuneStats::class
	))
	... lines 37 - 44
	}
	... lines 46 - 90

	... lines 1 - 25
	public function countNumberPrintedForCategory(Category $category): CategoryFortuneStats
	{
	... lines 28 - 43
	}
	... lines 45 - 89

	... lines 1 - 12
	class FortuneController extends AbstractController
	{
	... lines 15 - 30
	public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
	{
	... lines 33 - 36
	$stats = $fortuneCookieRepository->countNumberPrintedForCategory($category);
	... line 38
	return $this->render('fortune/showCategory.html.twig',[
	'category' => $category,
	'fortunesPrinted' => $stats->fortunesPrinted,
	'fortunesAverage' => $stats->fortunesAverage,
	'categoryName' => $stats->categoryName,
	]);
	}
	}