And WHERE Or WHERE

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.

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!

Thanks a lot! :)

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%"] ???

<blockquote>
An exception
occurred while executing 'SELECT t0_.id AS id0, t0.title AS title1, t0.status AS status2, t0.description AS description3, t0.gps_address AS gps_address4, t0.gps_lat AS gps_lat5, t0.gps_lon
AS gps_lon6, t0.gps_status AS gps_status_7 FROM tasklist 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 tasklist 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.
</blockquote>

        $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();```

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!

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 . '%');```

Great! I'm glad to hear it

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

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!

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'));
}

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!

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')
)
);
}

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!

hi,
Can i define associations across different entity managers ?

thanks

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!