And WHERE Or WHERE

Edit on GitHub

The most common thing to do in a query is to add a WHERE clause. Unfortunately, Doctrine doesn't support that. I'm kidding!

I have a search box - let's search for "Lucky Number". This isn't hooked up yet, but it adds a query parameter ?q=lucky+number. Let's use that to only return categories matching that.

Back in FortuneController, add a Request $request argument to the controller. Below, let's look to see if there is a q query parameter on the URL or not. If there is, we'll search for it, otherwise, we'll keep finding all the categories. For the search, call a new method on the repository called search(), and pass in the term.

            
            53 lines
            
            src/AppBundle/Controller/FortuneController.php
        
                                                    ... lines 1 - 7
                            
            use Symfony\Component\HttpFoundation\Request;
        
                                                    ... lines 9 - 14
                            
                public function homepageAction(Request $request)
        
                {
        
                                                    ... lines 17 - 20
                            
                    $search = $request->query->get('q');
        
                    if ($search) {
        
                        $categories = $categoryRepository->search($search);
        
                    } else {
        
                        $categories = $categoryRepository->findAllOrdered();
        
                    }
        
                                                    ... lines 27 - 30
                            
                }
        
                                                    ... lines 32 - 53

Back in CategoryRepository, let's make that function:

            
            33 lines
            
            src/AppBundle/Entity/CategoryRepository.php
        
                                                    ... lines 1 - 12
                            
            class CategoryRepository extends EntityRepository
        
            {
        
                                                    ... lines 15 - 23
                            
                public function search($term)
        
                {
        
                                                    ... lines 26 - 30
                            
                }
        
            }

We'll make a QueryBuilder just like before, but do the entire query in just one statement. Start by calling createQueryBuilder() and passing it cat:

            
            33 lines
            
            src/AppBundle/Entity/CategoryRepository.php
        
                                                    ... lines 1 - 12
                            
            class CategoryRepository extends EntityRepository
        
            {
        
                                                    ... lines 15 - 23
                            
                public function search($term)
        
                {
        
                    return $this->createQueryBuilder('cat')
        
                                                    ... lines 27 - 30
                            
                }
        
            }

The only thing our query needs is a WHERE clause to match the term to the name of the Category. Let's chain!

Building AND WHERE into a Query

Use a function called andWhere(). Don't worry - Doctrine won't add an AND to the query, unless it's needed. Inside, write cat.name = . But instead of passing the variable directly into the string like this, use a placeholder. Type colon, then make up a term. On the next line, use setParameter to tell Doctrine what I want to fill in for that term. So type searchTerm, should be replaced with $term. This avoids SQL injection attacks, so don't muck it up! Finally, we call getQuery() - like before - and execute():

            
            33 lines
            
            src/AppBundle/Entity/CategoryRepository.php
        
                                                    ... lines 1 - 23
                            
                public function search($term)
        
                {
        
                    return $this->createQueryBuilder('cat')
        
                        ->andWhere('cat.name = :searchTerm')
        
                        ->setParameter('searchTerm', $term)
        
                        ->getQuery()
        
                        ->execute();
        
                }
        
                                                    ... lines 32 - 33

And just like that, we should be able to go back, refresh, and there's our "Lucky Number" category match. And on the homepage, we still see everything.

Query with LIKE

But if we just search for "Lucky", we get nothing back because we're doing an exact match. But just like with normal SQL, we know that's easy to fix. And you already know how: just change = to LIKE - just like SQL!

It's just like writing SQL people! For the parameter value, surround it by percent signs to complete things. Refresh! We've got a match!

            
            33 lines
            
            src/AppBundle/Entity/CategoryRepository.php
        
                                                    ... lines 1 - 23
                            
                public function search($term)
        
                {
        
                    return $this->createQueryBuilder('cat')
        
                        ->andWhere('cat.name LIKE :searchTerm')
        
                        ->setParameter('searchTerm', '%'.$term.'%')
        
                        ->getQuery()
        
                        ->execute();
        
                }
        
                                                    ... lines 32 - 33

OR WHERE

What about adding an OR WHERE to the query? The Category entity has an iconKey property, which is where we get this little bug icon. For "Lucky Number", it's set to fa-bug from Font Awesome. Search for that. No results of course!

Let's update our query to match on the name OR iconKey property. If you're guessing that there's an orWhere() method, you're right! If you're guessing that I'm going to use it, you're wrong!

The string inside of the andWhere is a mini-DQL expression. So you can add OR cat.iconKey LIKE :searchTerm:

            
            33 lines
            
            src/AppBundle/Entity/CategoryRepository.php
        
                                                    ... lines 1 - 23
                            
                public function search($term)
        
                {
        
                    return $this->createQueryBuilder('cat')
        
                        ->andWhere('cat.name LIKE :searchTerm OR cat.iconKey LIKE :searchTerm')
        
                        ->setParameter('searchTerm', '%'.$term.'%')
        
                        ->getQuery()
        
                        ->execute();
        
                }
        
                                                    ... lines 32 - 33

And the searchTerm placeholder is already being filled in:

Refresh! Another match!

Avoid orWhere() and where()

So even though there is an orWhere() function, don't use it - it can cause WTF moments. Imagine if Category had an enabled property, and we built a query like this:

$this->createQueryBuilder('cat')
    ->andWhere('cat.name LIKE :searchTerm')
    ->orWhere('cat.iconKey LIKE :searchTerm')
    ->andWhere('cat.enabled = :enabled')
    ->setParameter('searchTerm', '%'.$term.'%')
    ->setParameter('enabled', true)
    ->getQuery()
    ->execute();

What would the SQL look like for this? Would it have the three WHERE clauses in a row, or would it correctly surround the first two with parentheses?

SELECT * FROM category WHERE
    name LIKE '%lucky%' OR iconKey LIKE '%lucky%' AND enabled = 1;

SELECT * FROM category WHERE
    (name LIKE '%lucky%' OR iconKey LIKE '%lucky%') AND enabled = 1;

Doctrine does the second and the query works as expected. But it's a lot less clear to read. Instead, think of each andWhere() as being surrounded by its own parentheses, and put OR statements in there.

Oh, and there's also a where() function. Don't use it either - it removes any previous WHERE clauses on the query, which you might be doing accidentally.

In other words, always use andWhere(), it keeps life simple.

Leave a comment!

Ali | posted 4 months ago

Hello team! So, I am able to fetch data from the db by passing an array as the parameter to the repository function:
The entity has a property named "colors" which is an array in MySQL DB.
/**
* @param array|null $colors
* @return CardBase[] Returns an array of CardBase objects
*/
public function findCardsWithOnlyGivenColors(array $colors)
{
$qb = $this->createQueryBuilder('c');
foreach($colors as $color)
{
$qb
->andWhere('c.colors LIKE :color')
->setParameter('color', '%'.$color.'%');
}
return $qb
->orderBy('c.id', 'ASC')
->getQuery()
->getResult()
;
}

This will ensure that the fetched data have all the array elements (all the colors available in the PARAM). My problem is that I need to make another function which will fetch data having any of the elements of the searched array but not necessarily all of its elements. Given that in this tutorial, we are adviced not to use orWhere, then how to do that? findCardsWithAnyOfGivenColors{array $colors}? Thank you very much.

MolloKhan | SFCASTS | Ali | posted 4 months ago | edited

Hey Ali

If you're not going to add any other WHERE clauses to your query, for example WHERE id = :someId, then for this purpose would be simpler to rely on the $qb->orWhere() method. Otherwise, you will have to write the "OR" clause manually. Something like this


        $orClause = [];
        foreach($colors as $key => $color) {
            $orClause[] = "color LIKE :color-$key";
            $qb->setParameter("color-$key", "%$color%");
        }
        $orClauseString = implode(' OR ', $orClause);
        $qb->andWhere($orClauseString);

I used double quotes for simplicity, and didn't test the code but it should give you an idea of what to do :)

Cheers!

Ali | MolloKhan | posted 4 months ago

Thanks a lot! :)

Miky | posted 1 year ago

Hi, I followed all instructions.. also similar user case is in course Go Pro with Doctrine.. but i am on Symfony 5.2.6 and getting this error and spend all day on it with studying more videos or QueryBuilder doctrine documentation.. but i don't understand why i am getting issue it dont matter if i put string or number to search input. Do you know what exacly this part means ["%30%", "%30%"] ???

An exception
occurred while executing 'SELECT t0_.id AS id_0, t0_.title AS title_1,
t0_.status AS status_2, t0_.description AS description_3,
t0_.gps_address AS gps_address_4, t0_.gps_lat AS gps_lat_5, t0_.gps_lon
AS gps_lon_6, t0_.gps_status AS gps_status_7 FROM task_list t0_ WHERE
t0_.id LIKE ? OR t0_.title LIKE ? ORDER BY t0_.id DESC' with params
["%30%", "%30%"]:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown
LINE 1: ...s AS gps_status_7 FROM task_list t0_ WHERE t0_.id LIKE $1 OR... ^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.


        $qb = $this
            ->createQueryBuilder('c');
        if ($term) {
           $qb
               ->andWhere('c.id LIKE :term OR c.title LIKE :term')
               ->setParameter('term', '%' . $term . '%');
        }
        return $qb
            ->orderBy('c.id', 'DESC')
            ->getQuery()
            ->getResult();

MolloKhan | SFCASTS | Miky | posted 1 year ago | edited

Hey Miky

I believe you can't use the LIKE operator on an INT field, you would need to cast it to string first but be aware it may cause performance issues
WHERE CAST(c.id as CHAR) LIKE '%15%'

Cheers!

Miky | MolloKhan | posted 1 year ago

Hi, with help of Tarmo Leppannen from Symfony on Slack, we find solution which works

$qb
->orWhere('c.id = :id')
->setParameter(':id', (int)$term)
->orWhere('LOWER(c.title) LIKE :term')
->orWhere('c.title LIKE :term')
->orWhere('LOWER(c.gps_address) LIKE :term')
->orWhere('c.gps_address LIKE :term')
->setParameter('term', '%' . (string)$term . '%');

1 Reply

MolloKhan | SFCASTS | Miky | posted 1 year ago

Great! I'm glad to hear it

Christophe C. | posted 2 years ago

Hey, I heard using raw % like this could cause security issues. Is that true ? If so, how do you replace them ? Thanks :)

weaverryan | SFCASTS | Christophe C. | posted 2 years ago | edited

Hey Christophe C.!

Hmm, I don't think that's true. My guess is (but if you have some reference, please let me know) that people were warning you from making queries using % without prepared statements - like:


// DO NOT DO THIS
->andWhere('cat.name LIKE %'.$searchTerm.'%')

This *would* be a problem. But, we're adding the '%' around the search term inside setParameter(), which is used in a prepared statement. We could pass ANYTHING to setParameter and it would be safe - that's the point of "parameters" in a prepared statement: your database engine will make sure they're used in a safe/sanitized way.

Cheers!

Caim Astraea | posted 4 years ago

Hello , been having a bit of an issue with grouping ? Seems doctrine is very fussy about grouping these queries. Need a way to group a bunch of orWhere ? Found this syntax on SO but guess it's not up to date anymore for doctrine 2 ? Anyone bumped into this ?

if(!empty($searchValue)) {
$qb->setParameter('searchValue2', '%' . $searchValue . '%')
->andWhere('a.customerNote LIKE ? OR a.status LIKE ? OR a.flightNum LIKE ? OR a.convocPlace LIKE ? or a.customerNote LIKE ? or a.providerNote LIKE ? or a.departDate LIKE ?',
array(':searchValue2',':searchValue2',':searchValue2',':searchValue2',':searchValue2',':searchValue2',':searchValue2'));
}

victor | SFCASTS | Caim Astraea | posted 4 years ago

Hey Caim,

I always try to use named placeholders instead of "?". Will it work for you?


if(!empty($searchValue)) {
    $qb
        ->andWhere('a.customerNote LIKE :searchValue2 OR a.status LIKE :searchValue2 ...')
        ->setParameter('searchValue2', '%' . $searchValue . '%')
}

It works for me well.

Cheers!

1 Reply

Caim Astraea | Caim Astraea | posted 4 years ago

I think I found a solution can use the expression builder to group them like so
if(!empty($searchValue)) {
$qb->setParameter('searchValue', '%' . $searchValue . '%');
$qb->andWhere(
$qb->expr()->orX(
$qb->expr()->like('a.customerNote', ':searchValue'),
$qb->expr()->like('a.flightNum',':searchValue')
)
);
}

victor | SFCASTS | Caim Astraea | posted 4 years ago

Hey Caim,

Agree, it should work with expressions as well, but anyway this one is easier to read but also should work well: https://knpuniversity.com/s...

Cheers!

Nizar | posted 4 years ago

hi,
Can i define associations across different entity managers ?

thanks

victor | SFCASTS | Nizar | posted 4 years ago

Hey Ali,

From the docs: https://symfony.com/doc/cur... - I see:

> Entities cannot define associations across different entity managers. If you need that, there are several alternatives that require some custom setup: https://stackoverflow.com/q...

Hope this helps.

Cheers!

1 Reply

This course is built on Symfony 2, but most of the concepts apply just fine to newer versions of Symfony. If you have questions, let us know :).

Symfony 2

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=5.3.3, <7.3.0",
        "symfony/symfony": "2.6.*", // v2.6.13
        "doctrine/orm": "~2.2,>=2.2.3", // v2.4.8
        "doctrine/doctrine-bundle": "~1.2", // 1.6.4
        "twig/extensions": "~1.0", // v1.5.4
        "symfony/assetic-bundle": "~2.3", // v2.8.2
        "symfony/swiftmailer-bundle": "~2.3", // v2.3.12
        "symfony/monolog-bundle": "~2.4", // v2.12.1
        "sensio/distribution-bundle": "~3.0.12", // v3.0.36
        "sensio/framework-extra-bundle": "~3.0", // v3.0.29
        "incenteev/composer-parameter-handler": "~2.0", // v2.1.3
        "hautelook/alice-bundle": "0.2.*" // 0.2
    },
    "require-dev": {
        "sensio/generator-bundle": "~2.3" // v2.5.3
    }
}

Edit on GitHub

TRACK

Symfony 5 >

COURSE

Go Pro with Doctrine Queries

And WHERE Or WHERE

Keep on Learning!

Building AND WHERE into a Query

Query with LIKE

OR WHERE

Avoid orWhere() and where()

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 7
	use Symfony\Component\HttpFoundation\Request;
	... lines 9 - 14
	public function homepageAction(Request $request)
	{
	... lines 17 - 20
	$search = $request->query->get('q');
	if ($search) {
	$categories = $categoryRepository->search($search);
	} else {
	$categories = $categoryRepository->findAllOrdered();
	}
	... lines 27 - 30
	}
	... lines 32 - 53

	... lines 1 - 12
	class CategoryRepository extends EntityRepository
	{
	... lines 15 - 23
	public function search($term)
	{
	... lines 26 - 30
	}
	}

	... lines 1 - 23
	public function search($term)
	{
	return $this->createQueryBuilder('cat')
	->andWhere('cat.name = :searchTerm')
	->setParameter('searchTerm', $term)
	->getQuery()
	->execute();
	}
	... lines 32 - 33