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

03.

yDónde() y oDónde()

|

|

Autoplay

Previous Challenge

Next Challenge

Login or register to check out the script

15 Comments

Sort By

Login or Register to join the conversation

achabrzyk 1 year ago edited

If anyone is slinging with PostgreSQL and wondering how to use the ILIKE operator, this code should help:

    $rsm = new ResultSetMappingBuilder($this->getEntityManager());
    $rsm->addRootEntityFromClassMetadata(Category::class, 'category');
    $sql = "SELECT * FROM category WHERE category.name ILIKE :searchTerm ORDER BY category.name DESC";
    $query = $this->getEntityManager()
        ->createNativeQuery($sql, $rsm)
        ->setParameter('searchTerm', '%' . $searchTerm . '%');
    return $query->getResult();

1 |

Victor SFCASTS achabrzyk 1 year ago edited

Hey @achabrzyk ,

Thank you for sharing this tip with our PostgreSQL friends :)

Cheers!

|

S-H 3 years ago edited

If someone wants as little 'raw' dql as possible, the where condition could also be written this way:

           ->andWhere(
                $qb->expr()->orX(
                    $qb->expr()->like('category.name', ':searchTerm'),
                    $qb->expr()->like('category.iconKey', ':searchTerm'),
                )
            )

1 |

Victor SFCASTS S-H 3 years ago

Hey S-H,

Thanks for sharing an alternative of building that WHERE part of the query with Doctrine Criteria - we will cover them too but a bit further in this course: https://symfonycasts.com/screencast/doctrine-queries/criteria - also will show some use cases where they can be useful. IMO this definitely looks a bit more complex than the way shown in this video :)

Cheers!

1 |

Darius S-H 2 years ago

I wrote query by this example, but it does not generate or condition, no matter what.

|

Darius Darius 2 years ago

turns out there was mistake in parantheses but I was not getting error, just wrong query.

|

Victor SFCASTS Darius 2 years ago

Hey Darius,

Yeah, that happens, and it's the most complex bug to debug because you don't have an error. Glad to hear you figured it out, good job!

Cheers!

|

Octavio-B 1 year ago edited

Hi! I didn't know where to ask this so I'll do it in here because is about a search bar.

I already have a search bar for all of my data and it works fine, just like in this tutorial. The problem is when I want to search through specific data that each user has. I will show you what I've tried so far.

This the function I have in my Vehicle Repository:

        public function createSearchResultsFromUser($slug = null, string $q = null): QueryBuilder
        {
            if ($slug && $q) {
                $queryBuilder = $this->addOrderedByArrivedAtQueryBuilder()
                    ->innerJoin('vehicle.publicEmployee', 'publicEmployee')
                    ->addSelect('publicEmployee')
                    ->andWhere('publicEmployee.owner = :slug')
                    ->setParameter('slug', $slug)
                    ->andWhere('vehicle.model LIKE :q OR publicEmployee.name LIKE :q OR publicEmployee.department LIKE :q')
                    ->setParameter('q', '%' . $q . '%');

            }
            
            return $queryBuilder;
        }

This is my controller:

    #[Route('/user/search/', name: 'app_user_search')]
    public function search(VehicleRepository $vehicleRepository, Request $request, $slug = null, $q = null, ): Response
    {
        $queryBuilder = $vehicleRepository->createSearchResultsFromUser([$slug, $request->query->get('q')]);
        $adapter = new Queryadapter($queryBuilder);
        $pagerfanta = Pagerfanta::createForCurrentPageWithMaxPerPage(
            $adapter,
            $request->query->get('page', 1),
            9
        );
        $vehicles = $vehicleRepository->findAll();
        $myVehicle = $vehicles[array_rand($vehicles)];
        return $this->render('main/homepage.html.twig', [
            'myVehicle' => $myVehicle,
            'vehicles' => $vehicles,
            'pager' => $pagerfanta,
        ]);

    }

and finally this is in my template:

                {% elseif is_granted('IS_AUTHENTICATED_REMEMBERED') %}
                    <form action="{{ path('app_user_search', { slug: app.user.id}) }}">
                        <input
                                type="search"
                                name="q"
                                value="{{ app.request.query.get('q') }}"
                                class="form-control text-black"
                                placeholder="Buscar..."
                                aria-label="Search"
                        >
                    </form>
                {% endif %}

It seems to be that one of my problems(or may be the only one) is that I'm not passing the 'q' argument, because the error says that I have not defined the querybuilder, meaning that either $q or $slug is no present, but I have tried both and the $q is the one missing.

I'd really appreciate any help with this matter. BTW, I love your tutorials.

|

Victor SFCASTS Octavio-B 1 year ago edited

Hey Octavio,

First of all, it seems that your code is fragile. The signature of the createSearchResultsFromUser() allow both args to be null, which means you will not hit that if ($slug && $q) in some cases. To make it work, you need to create a query builder first in that method, and only then add that if ($slug && $q) that will modify it, instead of creating. The correct code would be something like this:

        public function createSearchResultsFromUser($slug = null, string $q = null): QueryBuilder
        {
            $queryBuilder = $this->addOrderedByArrivedAtQueryBuilder()
                    ->innerJoin('vehicle.publicEmployee', 'publicEmployee')
                    ->addSelect('publicEmployee');

            if ($slug && $q) {
                $queryBuilder
                    ->andWhere('publicEmployee.owner = :slug')
                    ->setParameter('slug', $slug)
                    ->andWhere('vehicle.model LIKE :q OR publicEmployee.name LIKE :q OR publicEmployee.department LIKE :q')
                    ->setParameter('q', '%' . $q . '%');

            }
            
            return $queryBuilder;
        }

But I would suggest you to improve it even more and separate those search args in different if statements, like this:

        public function createSearchResultsFromUser($slug = null, string $q = null): QueryBuilder
        {
            $queryBuilder = $this->addOrderedByArrivedAtQueryBuilder()
                    ->innerJoin('vehicle.publicEmployee', 'publicEmployee')
                    ->addSelect('publicEmployee');

            if ($slug) {
                $queryBuilder
                    ->andWhere('publicEmployee.owner = :slug')
                    ->setParameter('slug', $slug);
            }
            if ($q) {
                $queryBuilder
                    ->andWhere('vehicle.model LIKE :q OR publicEmployee.name LIKE :q OR publicEmployee.department LIKE :q')
                    ->setParameter('q', '%' . $q . '%');
            }
            
            return $queryBuilder;
        }

This way the slug and q will be independent, and you will add them only when they are not null.

Or just revisit your search business logic one more time.

Also, you have an error in the place where you call the method, you're passing those args as an array:
$queryBuilder = $vehicleRepository->createSearchResultsFromUser([$slug, $request->query->get('q')]);

While it should be passed as 2 separate args:
$queryBuilder = $vehicleRepository->createSearchResultsFromUser($slug, $request->query->get('q'));

I hope this helps! Sorry, but I can't help you more on it because it's a personal project question, we have bandwidth to answer tutorial questions only. Thank you for your understanding!

Cheers!

1 |

Peter-K 2 years ago

The most confusing for me is always using WHERE something IN () and second one is using UNION queries so I hope something will be in this course about it I end up using raw sql for these 2 scenarios

|

Victor SFCASTS Peter-K 2 years ago

Hey Peter,

Yes we will cover WHERE IN() in this course, see: https://symfonycasts.com/screencast/doctrine-queries/where-in - that's a pretty simple thing, so it should not be a problem to understand that I think.

About UNION - we don't cover it in this tutorial, but with the knowledge you get in this course you should be able to leverage that MySQL function yourself as well

Cheers!

|

Stanislaw 2 years ago

Do you know why my search is case sensitive? I worked around this by adding LOWER()

->andWhere('LOWER(category.name) LIKE LOWER(:searchTerm)')

but wondering if it's just different version or another issue?

|

sadikoff Stanislaw 2 years ago

Hey

I think it's a PostgreSQL feature, and this workaround is pretty acceptable to use.

Cheers

|

unknown 2 years ago edited

Comment was deleted.

sadikoff unknown 2 years ago

Hello,

It depends on your query and your database server can you provide more information?

Cheers

|

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