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

4 Comments

Sort By

Login or Register to join the conversation

Peter-K 1 year 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 |

MolloKhan SFCASTS Peter-K 1 year 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!

|

Brent-M 2 years 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');

|

weaverryan SFCASTS Brent-M 2 years 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!

|

This tutorial is built on Symfony 6 but works great on Symfony 7 & 8!

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