WHERE IN()

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

We have categories for "Pets" and "Love", but if we search up here for "pets love"... no results! That makes sense. We're searching to see if this string is matching the name or the iconKey. Let's make our search smarter to see if we can match both of those categories by searching word by word.

The query for this lives in CategoryRepository... on the search() method. The $term argument is the string we type in. Down here, let's say $termList = then explode that string into an array by splitting on empty spaces. If you want a really rich search, you should use a real search system. But we can do some pretty cool stuff just with the database.

            
            119 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 18
                            
            class CategoryRepository extends ServiceEntityRepository
        
            {
        
                                                    ... lines 21 - 41
                            
                public function search(string $term): array
        
                {
        
                    $termList = explode(' ', $term);
        
                                                    ... lines 45 - 51
                            
                }
        
                                                    ... lines 53 - 117
                            
            }

Here's the goal: I want to also match results where category.name is in one of the words in the array.

Using the IN

Right after category.name LIKE :searchTerm, add OR category.name IN. The only tricky thing about this is the syntax. Add (). If we were writing a raw SQL query, we would write a list here, like 'foo', 'bar'. But with the query builder, instead, put a placeholder - like :termList. Below pass that in: ->setParameter('termList', $termList).

            
            120 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 41
                            
                public function search(string $term): array
        
                {
        
                                                    ... lines 44 - 46
                            
                    return $this->addFortuneCookieJoinAndSelect($qb)
        
                        ->andWhere('category.name LIKE :searchTerm OR category.name IN (:termList) OR category.iconKey LIKE :searchTerm OR fortuneCookie.fortune LIKE :searchTerm')
        
                                                    ... line 49
                            
                        ->setParameter('termList', $termList)
        
                                                    ... lines 51 - 52
                            
                }
        
                                                    ... lines 54 - 120

The key thing is that, when you use IN, you will need the parentheses like normal... but inside of that, instead of a comma-separated list, you'll set an array. Doctrine will transform that for us.

And now... nice! Once you know how it works, it's just that easy.

Next: You're probably familiar with the RAND() function for MySQL, or maybe the YEAR() function... or one of the many MySQL or PostgreSQL functions that exist. Well, you might be surprised to learn that some of those don't work out of the box.

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

Go Pro with Doctrine Queries

WHERE IN()

Keep on Learning!

Using the IN

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 18
	class CategoryRepository extends ServiceEntityRepository
	{
	... lines 21 - 41
	public function search(string $term): array
	{
	$termList = explode(' ', $term);
	... lines 45 - 51
	}
	... lines 53 - 117
	}

	... lines 1 - 41
	public function search(string $term): array
	{
	... lines 44 - 46
	return $this->addFortuneCookieJoinAndSelect($qb)
	->andWhere('category.name LIKE :searchTerm OR category.name IN (:termList) OR category.iconKey LIKE :searchTerm OR fortuneCookie.fortune LIKE :searchTerm')
	... line 49
	->setParameter('termList', $termList)
	... lines 51 - 52
	}
	... lines 54 - 120