Chapters

16 Chapters | 1:26:33 |

01

Doctrina DQL

7:38
02

El QueryBuilder

2:02
03

yDónde() y oDónde()

7:06
04

JOINs

3:45
05

Consultas de reducción JOINs y addSelect

5:22
06

Relaciones EXTRA_LAZY

6:07
07

SELECCIONA la SUMA (o CUENTA)

6:15
08

Seleccionar campos específicos

5:09
09

Seleccionar en un nuevo objeto DTO

3:40
10

Consultas SQL sin procesar

6:52
11

Reutilizar consultas en el Generador de consultas

4:42
12

Criterios: Filtrar colecciones de relaciones

9:09
13

Filtros: Modificar consultas automáticamente

6:59
14

DONDE EN()

2:07
15

Utilizar RAND() u otras funciones no compatibles

3:11
16

Utilizar GROUP BY para buscar y contar en una sola consulta

6:29

> Symfony > Hazte profesional con las consultas de Doctrine

Buy Access to Course

05.

Consultas de reducción JOINs y addSelect

|

|

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 Challenge

Next Challenge

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

Login Subscribe

Login or register to check out the script

10 Comments

Sort By

Login or Register to join the conversation

RM 4 months ago

Hey, team!
Found posible typo!
"if we just grab the fort_U_ne cookie data in the first"

|

Victor SFCASTS RM 4 months ago

Hey RM,

Good catch! Thanks for pointing it out, I fixed in https://github.com/SymfonyCasts/doctrine-queries/commit/3b42d2ec7047c8cf7190f8047f3045cdd66d4842

Cheers!

|

RM 3 years ago

Hello, Ryan!

[1] Really handy optimization for reducing query count with addSelect().

[2] Is there an equivalent method of reducing query count for a Tree?
(Tree - Nestedset behavior extension for Doctrine)

/**
 * @param int $root_node_id
 * @return array
 */
public function getCategoryTreeNodeArrayByRootId(int $root_node_id): array
{
    $query = $this->createQueryBuilder('c')
        ->from(Category::class, 'c')
        ->where('c.root = :root')
        ->setParameter('root', $root_node_id)
        ->orderBy('c.root, c.lft', 'ASC')
        ->getQuery();

    $query->setHint(Query::HINT_INCLUDE_META_COLUMNS, true);

    return $query->getArrayResult();
}

when i add ->addSelect('c')
i get an error [Semantical Error] line 0, col 73 near 'c WHERE c.root': Error: 'c' is already defined.

|

sadikoff RM 3 years ago edited

Hey @RM

You don't need to ->addSelect() with root alias as it's already added. So the question is which real queries do you want to minimize? Because this query should return the whole tree in scalar array view, and if you need additional data from relations you should add joins here and add this relation to ->addSelect()

Cheers.

|

RM sadikoff 3 years ago

Ok, thank you!

|

Jerzy 3 years ago

Hi Ryan,

The strange thing is that the counts of fortune cookies for categories have changed after adding the join. On video it's 2:40 vs 3:44. But it feels like these shouldn't change because it was a left join that was added. Perhaps some data was added/removed during video recording?

PS. thanks for another great course!

|

sadikoff Jerzy 3 years ago

Hey, hey I'm here with updates. Yeah that was a correct situation, because you are on search page and you are limiting results to "be" query, so adding select with join fixed issue with counting, now it shows the correct number of elements in that category

Because you have this fortuneCookie.fortune LIKE :searchTerm in query

Cheers!

1 |

Jerzy sadikoff 3 years ago

Hi Vladimir,
Thanks for checking!

So if I understand correctly:
At first the |length in Twig made it count all the records within a category, because Doctrine detected that the relation isn't hydrated and it fetched all the records from the db. What was tricky here for me is that this query was already filtering related fortuneCookies by the search query.

But adding an addSelect('fortuneCookie') made Doctrine hydrate the relation only with the records matching the LIKE condition, and then |length already had related(filtered out) rows. So Doctrine didn't need to resort back to the default relation mapping(no filtering) to fetch the related fortuneCookies.

I guess that makes sense now!

|

sadikoff Jerzy 3 years ago

Yeah that's correct! Sometimes it can be tricky!

Cheers and happy coding!

1 |

sadikoff Jerzy 3 years ago

That is a great catch, I'll check the details and get back to you =)

|

Symfony 6 Doctrine 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=8.2",
        "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 Challenge

Next Challenge