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 6 > Go Pro with Doctrine Queries

Buy Access to Course

03.

andWhere() and orWhere()

Autoplay

Previous Challenge

Next Challenge

Our site has this nifty search box, which... doesn't work. If I hit "enter" to search for "lunch", it does add ?q=lunch to the end of the URL... but the results don't change. Let's hook this thing up!

Grabbing the Search Query Parameter

Spin over and find our controller: FortuneController. To read the query parameter, we need Symfony's Request object. Add a new argument - it doesn't matter if it's first or last - type-hinted with Request - the one from Symfony - hit "tab" to add that use statement, and say $request. We can get the search term down here with $searchTerm = $request->query->get('q').

            Copy Code

                            Show All Lines

                        37 lines
            |
            src/Controller/FortuneController.php
        
                Show Lines

                                                    // ... lines 1 - 7
                            
            use Symfony\Component\HttpFoundation\Request;
        
                Show Lines

                                                    // ... lines 9 - 11
                            
            class FortuneController extends AbstractController
        
            {
        
                Show Lines

                                                    // ... line 14
                            
                public function index(Request $request, CategoryRepository $categoryRepository): Response
        
                {
        
                    $searchTerm = $request->query->get('q');
        
                Show Lines

                                                    // ... lines 18 - 26
                            
                }
        
                Show Lines

                                                    // ... lines 28 - 35
                            
            }

We're using q... just because that's what I chose in my template... you can see it down here in templates/base.html.twig. This is built with a very simple form that includes <input type="text", name="q". So we're reading the q query parameter and setting it on $searchTerm.

Below, if we have a $searchTerm, set $categories to $categoryRepository->search() (a method we're about to create) and pass $searchTerm. If we don't have a $searchTerm, reuse the query logic that we had before.

            Copy Code

                            Show All Lines

                        37 lines
            |
            src/Controller/FortuneController.php
        
                Show Lines

                                                    // ... lines 1 - 14
                            
                public function index(Request $request, CategoryRepository $categoryRepository): Response
        
                {
        
                Show Lines

                                                    // ... line 17
                            
                    if ($searchTerm) {
        
                        $categories = $categoryRepository->search($searchTerm);
        
                    } else {
        
                        $categories = $categoryRepository->findAllOrdered();
        
                    }
        
                Show Lines

                                                    // ... lines 23 - 26
                            
                }
        
                Show Lines

                                                    // ... lines 28 - 37

Adding a WHERE Clause

Awesome! Let's go create that search() method!

Over in our repository, say public function search(). This will take a string $term argument and return an array. Like last time, I'll add some PHPDoc that says this returns an array of Category[] objects. Remove the @param... because that doesn't add anything.

            Copy Code

                            Show All Lines

                        89 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 17
                            
            class CategoryRepository extends ServiceEntityRepository
        
            {
        
                Show Lines

                                                    // ... lines 20 - 37
                            
                /**
        
                 * @return Category[]
        
                 */
        
                public function search(string $term): array
        
                {
        
                }
        
                Show Lines

                                                    // ... lines 45 - 87
                            
            }

Ok: our query will start like before... though we can get fancier and return immediately. Say $this->createQueryBuilder() and use the same category alias. It's a good idea to always use the same alias for an entity: it'll help us later to reuse parts of a query builder.

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                Show Lines

                                                    // ... lines 44 - 47
                            
                }
        
                Show Lines

                                                    // ... lines 49 - 93

For the WHERE clause, use ->andWhere(). There is also a where() method... but I don't think I've ever used it! And... you shouldn't either. Using andWhere() is always ok - even if this is the first WHERE clause... and we don't really need the "and" part. Doctrine is smart enough to figure that out.

andWhere() vs where()

What's wrong with ->where()? Well, if you added a WHERE clause to your QueryBuilder earlier, calling ->where() would remove that and replace it with the new stuff... which probably isn't what you want. ->andWhere() always adds to the query.

Inside say category, and since I want to search on the name property of the Category entity, say category.name =. This next part is very important. Never ever, ever add the dynamic part directly to your query string. This opens you up for SQL injection attacks. Yikes. Instead, any time you need to put a dynamic part in a query, put a placeholder instead: like :searchTerm. The word searchTerm could be anything... and you fill it in by saying ->setParameter('searchTerm', $term).

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                        ->andWhere('category.name = :searchTerm')
        
                        ->setParameter('searchTerm', $term)
        
                Show Lines

                                                    // ... lines 46 - 47
                            
                }
        
                Show Lines

                                                    // ... lines 49 - 93

Perfecto! The ending is easy: ->getQuery() to turn that into a Query object and then ->getResult() to execute that query and return the array of Category objects.

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                        ->andWhere('category.name = :searchTerm')
        
                        ->setParameter('searchTerm', $term)
        
                        ->getQuery()
        
                        ->getResult();
        
                }
        
                Show Lines

                                                    // ... lines 49 - 93

Sweet! If we head over and try this... got it!

Making the Query Fuzzy

But if we take off a few letters and search again... we get nothing! Ideally, we want the search to be fuzzy: matching any part of the name.

And that's easy to do. Change our ->andWhere() from = to LIKE... and down here, for searchTerm... this looks a bit weird, but add a percent before and after to make it fuzzy on both sides.

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                        ->andWhere('category.name LIKE :searchTerm')
        
                        ->setParameter('searchTerm', '%'.$term.'%')
        
                Show Lines

                                                    // ... lines 46 - 47
                            
                }
        
                Show Lines

                                                    // ... lines 49 - 93

If we try it now... eureka!

Being Careful with orWhere

But let's get tougher! Every category has its own icon - like fa-quote-left or the one below it has fa-utensils. This is also a string that's stored in the database!

Could we make our search also search on that property? Sure! We just need to add an OR to our query.

Down here, you might be tempted to use this nice ->orWhere() passing category. with the name of that property... which... if we look in Category real quick... is $iconKey. So category.iconKey LIKE :searchTerm.

And yes, we could do that. But don't! I recommend never using orWhere(). Why? Because... things can get weird. Imagine we had a query like this: ->andWhere('category.name LIKE :searchTerm'), ->orWhere('category.iconKey LIKE :searchTerm') ->andWhere('category.active = true').

Do you see the problem? What I'm probably trying to do is search for categories... but only every match active categories. In reality, if the searchTerm matches iconKey, a Category will be returned whether it's active or not. If we wrote this in SQL, we would include parenthesis around the first two parts to make it behave. But when you use ->orWhere(), that doesn't happen.

So what's the solution? Always use andWhere()... and if you need an OR, put it right inside that! Yup, what you pass to andWhere() is DQL, so we can say OR category.iconKey LIKE :searchTerm.

            Copy Code

                            Show All Lines

                        93 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                        ->andWhere('category.name LIKE :searchTerm OR category.iconKey LIKE :searchTerm')
        
                Show Lines

                                                    // ... lines 45 - 47
                            
                }
        
                Show Lines

                                                    // ... lines 49 - 93

That's it! In the final SQL, Doctrine will put parentheses around this WHERE.

Let's try it! Spin over and try searching for "utensils". I'll type part of the word and... got it! We're matching on the iconKey!

Oh, and to keep this consistent with the normal homepage, let's include ->addOrderBy('category.name', 'DESC').

            Copy Code

                            Show All Lines

                        94 lines
            |
            src/Repository/CategoryRepository.php
        
                Show Lines

                                                    // ... lines 1 - 40
                            
                public function search(string $term): array
        
                {
        
                    return $this->createQueryBuilder('category')
        
                Show Lines

                                                    // ... lines 44 - 45
                            
                        ->addOrderBy('category.name', Criteria::DESC)
        
                Show Lines

                                                    // ... lines 47 - 48
                            
                }
        
                Show Lines

                                                    // ... lines 50 - 94

Now, if we go to the homepage and just type the letter "p" in the search bar, yup! It's sorting alphabetically.

And if you have any doubts about your query, you can always head into the Doctrine profiler to see the formatted version. That's exactly what we expected.

Next: Let's extend our query, so we can search on the fortune cookies that are inside each category. To do that, we'll need a JOIN.

15 Comments

Sort By

achabrzyk 3 months 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 | Reply |

Victor SFCASTS achabrzyk 3 months ago edited

Hey @achabrzyk ,

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

Cheers!

| Reply |

S-H 1 year 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 | Reply |

Victor SFCASTS S-H 1 year 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 | Reply |

Darius S-H 1 year ago

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

| Reply |

Darius Darius 1 year ago

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

| Reply |

Victor SFCASTS Darius 1 year 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!

| Reply |

Octavio-B 2 months 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.

| Reply |

Victor SFCASTS Octavio-B 2 months 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 | Reply |

Peter-K 11 months 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

| Reply |

Victor SFCASTS Peter-K 11 months 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!

| Reply |

Stanislaw 1 year 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?

| Reply |

sadikoff SFCASTS Stanislaw 1 year ago

Hey

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

Cheers

| Reply |

unknown 1 year ago edited

Comment was deleted.

sadikoff SFCASTS unknown 1 year ago

Hello,

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

Cheers

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

Previous Challenge

Next Challenge

andWhere() and orWhere()

Share this awesome video!

Grabbing the Search Query Parameter

Adding a WHERE Clause

andWhere() vs where()

Making the Query Fuzzy

Being Careful with orWhere

15 Comments

Delete comment?

What PHP libraries does this tutorial use?

Show Lines	// ... lines 1 - 7
	use Symfony\Component\HttpFoundation\Request;
Show Lines	// ... lines 9 - 11
	class FortuneController extends AbstractController
	{
Show Lines	// ... line 14
	public function index(Request $request, CategoryRepository $categoryRepository): Response
	{
	$searchTerm = $request->query->get('q');
Show Lines	// ... lines 18 - 26
	}
Show Lines	// ... lines 28 - 35
	}

Show Lines	// ... lines 1 - 14
	public function index(Request $request, CategoryRepository $categoryRepository): Response
	{
Show Lines	// ... line 17
	if ($searchTerm) {
	$categories = $categoryRepository->search($searchTerm);
	} else {
	$categories = $categoryRepository->findAllOrdered();
	}
Show Lines	// ... lines 23 - 26
	}
Show Lines	// ... lines 28 - 37

Show Lines	// ... lines 1 - 17
	class CategoryRepository extends ServiceEntityRepository
	{
Show Lines	// ... lines 20 - 37
	/**
	* @return Category[]
	*/
	public function search(string $term): array
	{

	}
Show Lines	// ... lines 45 - 87
	}

Show Lines	// ... lines 1 - 40
	public function search(string $term): array
	{
	return $this->createQueryBuilder('category')
Show Lines	// ... lines 44 - 47
	}
Show Lines	// ... lines 49 - 93