Seleccionar en un nuevo objeto DTO

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

Tener la flexibilidad de seleccionar los datos que queramos es genial. Tratar con la matriz asociativa que obtenemos de vuelta es... ¡menos alucinante! Me gusta trabajar con objetos siempre que sea posible. Afortunadamente, Doctrine nos ofrece una forma sencilla de mejorar esta situación: consultamos los datos que queremos... pero le decimos que nos dé un objeto.

Crear el DTO

En primer lugar, necesitamos crear una nueva clase que contenga los datos de nuestra consulta. Crearé un nuevo directorio llamado src/Model/... pero podría llamarse como quieras. Llama a la clase... ¿qué tal CategoryFortuneStats.

El único propósito de esta clase es contener los datos de esta consulta específica. Así que añade un public function __construct() con unas cuantas propiedades public para simplificar:public int $fortunesPrinted, public float $fortunesAverage, ypublic 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,
        
                )
        
                {
        
                }
        
            }

¡Estupendo!

De vuelta al repositorio, en realidad no necesitamos ninguna magia de Doctrine para utilizar esta nueva clase. Podríamos consultar la matriz asociativa, devolver new CategoryFortuneStats()y pasarle cada clave.

Es una gran opción, muy sencilla y además este método del repositorio devolvería un objeto en lugar de un array. Pero... Doctrine lo hace aún más fácil gracias a una función poco conocida.

Añade un nuevo ->select() que contendrá todas estas selecciones en una. Añade también un sprintf(): verás por qué en un minuto. Dentro, ¡mira esto! DiNEW %s() y luego pasa CategoryFortuneStats::class por ese marcador de posición. Básicamente, estamos diciendo NEW App\Model\CategoryFortuneStats()... Sólo quería evitar escribir ese nombre de clase tan largo.

Dentro de NEW, coge cada una de las 3 cosas que queremos seleccionar y pégalas, como si las pasáramos directamente como primer, segundo y tercer argumento al constructor de nuestra nueva clase.

            
            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
                            
            }

¿No es genial? ¡Vamos a dd($result) para ver cómo queda!

Sin aliasing con NEW

Si nos dirigimos y actualizamos... oh... me aparece un error: T_CLOSE_PARENTHESIS, got 'AS'. Cuando seleccionamos datos en un objeto, el aliasing ya no es necesario... ni está permitido. Y tiene sentido: Doctrine pasará lo que sea esto al primer argumento de nuestro constructor, esto al segundo argumento y esto al tercero. Como los alias ya no tienen sentido... elimínalos.

            
            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

Si lo comprobamos ahora... ¡lo tengo! ¡Me encanta! ¡Tenemos un objeto con nuestros datos dentro!

Vamos a celebrarlo limpiando nuestro método. En lugar de un array, vamos a devolver un CategoryFortuneStats. Elimina también el dd($result) de aquí abajo.

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

De vuelta en el controlador, para mostrar lo bonito que es esto, cambia $result por... qué tal $stats. Entonces podemos utilizar $stats->fortunesPrinted, $stats->fortunesAverage, y $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,
        
                    ]);
        
                }
        
            }

Ahora que lo hemos arreglado un poco, comprobemos si sigue funcionando. Y... funciona.

Siguiente: A veces las consultas son tan complejas... que la mejor opción es escribirlas en SQL nativo. Hablemos de cómo hacerlo.

Leave a comment!

vince-amstz | posted hace 7 días | 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 hace 7 días | 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 hace 7 días

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 hace 7 días | 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 hace 20 días | 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 hace 20 días | 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 hace 20 días

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 hace 20 días | 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 hace 23 días

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 hace 22 días | 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 hace 20 días

Thanks Victor

julien_bonnier | posted hace 2 meses

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 hace 2 meses | edited

You're welcome @julien_bonnier :)

Sidi-LEKHAIFA | posted hace 2 meses

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 hace 2 meses | 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 hace 2 meses | 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

Hazte profesional con las consultas de Doctrine

Seleccionar en un nuevo objeto DTO

Keep on Learning!

Crear el DTO

Sin aliasing con 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,
	]);
	}
	}