Chapters

10 Chapters | 50:57 |

01

Doctrine DQL

4:58
02

The QueryBuilder

2:02
03

And WHERE Or WHERE

4:27
04

JOINs!

3:27
05

Joins and addSelect Reduce Queries

6:03
06

SELECT the SUM (or COUNT)

4:51
07

Selecting Specific Fields

5:04
08

Raw SQL Queries

6:29
09

Reusing Queries with the Query Builder

3:07
10

Filters

10:29

This tutorial has a new version, check it out!

> Symfony 5 > Go Pro with Doctrine Queries (Legacy)

Buy Access to Course

03.

And WHERE Or WHERE

|

Autoplay

Keep on Learning!

If you liked what you've learned so far, dive in! Subscribe to get
access to this tutorial plus video, code and script downloads.

Start your All-Access Pass

Buy just this tutorial for $6.00

Previous Chapter

Next Chapter

Login or register to check out the script

15 Comments

Sort By

Login or Register to join the conversation

Ali_M 3 years 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_M 3 years 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_M MolloKhan 3 years ago

Thanks a lot! :)

|

Miky 5 years ago edited

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

|

MolloKhan SFCASTS Miky 5 years 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 5 years ago edited

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 |

MolloKhan SFCASTS Miky 5 years ago

Great! I'm glad to hear it

|

Christophe C. 6 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. 6 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 8 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 8 years ago edited

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 |

Caim Astraea Caim Astraea 8 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 8 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 8 years ago

hi,
Can i define associations across different entity managers ?

thanks

|

Victor SFCASTS Nizar 8 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 |

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

Previous Chapter

Next Chapter