Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
This tutorial has a new version, check it out!

Custom Queries

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 $12.00

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

Login Subscribe

How do you write custom queries in Doctrine? Well, you're already familiar with writing SQL, and, yea, it is possible to write raw SQL queries with Doctrine. But, most of the time, you won't do this. Instead, because Doctrine is a library that works with many different database engines, Doctrine has its own SQL-like language called Doctrine query language, or DQL.

Fortunately, DQL looks almost exactly like SQL. Except, instead of table and column names in your query, you'll use class and property names. Again, Doctrine really wants you to pretend like there is no database, tables or columns behind the scenes. It wants you to pretend like you're saving and fetching objects and their properties.

Introducing: The Query Builder

Anyways, to write a custom query, you can either create a DQL string directly, or you can do what I usually do: use the query builder. The query builder is just an object-oriented builder that helps create a DQL string. Nothing fancy.

And there's a pretty good example right here: you can add where statements order by, limits and pretty much anything else:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->orderBy('a.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
... lines 36 - 47
}

One nice thing is that you can do this all in any order - you could put the order by first, and the where statements after. The query builder doesn't care!

Oh, and see this andWhere()?

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
... lines 29 - 33
;
}
... lines 36 - 47
}

There is a normal where() method, but it's safe to use andWhere() even if this is the first WHERE clause. Again the query builder is smart enough to figure it out. I recommend andWhere(), because where() will remove any previous where clauses you may have added... which... can be a gotcha!

DQL - and so, the query builder - also uses prepared statements. If you're not familiar with them, it's a really simple idea: whenever you want to put a dynamic value into a query, instead of hacking it into the string with concatenation, put : and any placeholder name. Then, later, give that placeholder a value with ->setParameter():

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
... lines 30 - 33
;
}
... lines 36 - 47
}

This prevents SQL injection.

Writing our Custom Query

In our case, we won't need any arguments, and I'm going to simplify a bit. Let's say andWhere('a.publishedAt IS NOT NULL'):

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
... lines 29 - 31
;
}
... lines 34 - 45
}

You can totally see how close this is to normal SQL. You can even put OR statements inside the string, like a.publishedAt IS NULL OR a.publishedAt > NOW().

Oh, and what the heck does the a mean? Think of this as the table alias for Article in the query - just like how you can say SELECT a.* FROM article AS a.

It could be anything: if you used article instead, you'd just need to change all the references from a. to article..

Let's also add our orderBy(), with a.publishedAt, DESC:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
... lines 30 - 31
;
}
... lines 34 - 45
}

Oh, and this is a good example of how we're referencing the property name on the entity. The column name in the database is actually published_at, but we don't use that here.

Finally, let's remove the max result:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
... lines 30 - 31
;
}
... lines 34 - 45
}

Once you're done building your query, you always call getQuery() and then, to get the array of Article objects, getResult():

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 24
public function findAllPublishedOrderedByNewest()
{
return $this->createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 45
}

Below this method, there's an example of finding just one object:

... lines 1 - 14
class ArticleRepository extends ServiceEntityRepository
{
... lines 17 - 34
/*
public function findOneBySomeField($value): ?Article
{
return $this->createQueryBuilder('a')
->andWhere('a.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
}

It's almost the same: build the query, call getQuery(), but then finish with getOneOrNullResult().

So, in all normal situations, you always call getQuery(), then you'll either call getResult() to return many rows of articles, or getOneOrNullResult() to return a single Article object. Got it?

Now that our new findAllPublishedOrderedByNewest() method is done, let's go use it in the controller: $repository->, and there it is!

... lines 1 - 15
class ArticleController extends AbstractController
{
... lines 18 - 30
public function homepage(EntityManagerInterface $em)
{
$repository = $em->getRepository(Article::class);
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 35 - 38
}
... lines 40 - 79
}

Let's give it a try! Move over and, refresh! Perfect! The order is correct and the unpublished articles are gone.

Autowiring ArticleRepository

To make this even cooler, let me show you a trick. Instead of getting the entity manager and then calling getRepository() to get the ArticleRepository, you can take a shortcut: just type ArticleRepository $repository:

... lines 1 - 5
use App\Repository\ArticleRepository;
... lines 7 - 16
class ArticleController extends AbstractController
... lines 18 - 31
public function homepage(ArticleRepository $repository)
{
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 35 - 38
}
... lines 40 - 79
}

This works for a simple reason: all of your repositories are automatically registered as services in the container. So you can autowire them like anything else. This is how I actually code when I need a repository.

And when we refresh, no surprise, it works!

Custom queries are a big topic, and we'll continue writing a few more here and there. But if you have something particularly challenging, check out our Go Pro with Doctrine Queries tutorial. That tutorial uses Symfony 3, but the query logic is exactly the same as in Symfony 4.

Next, I want to show you two more tricks: one for re-using query logic between multiple queries, and another super shortcut to fetch any entity with zero work.

Leave a comment!

24
Login or Register to join the conversation
Default user avatar

hi symfonycast i have a question maybe related to this topic,so i have product repository with function findOneBySlug($slug) it working great and fine but... a horible event happen so in the product they have 2 table with name language_id 1 for indonesia 2 for english and whenever the findOneBySlug code running this code just return the language_id 1,i want add some modification when in url has locale example /en/single-origin/kopi-arabica-aceh-gayo/reviews/create they will return view product name with language_id 2 or /single-origin/kopi-arabica-aceh-gayo/reviews/create will get language_id 1 here some code in findOneBySlug

edit: sorry i forgot mention this i wanna add something like this query
SELECT * FROM seo s Join language l on l.id = s.language_id WHERE s.slug = 'kopi-arabica-aceh-gayo' and s.language_id = 1;

in findOneBySlug function

public function findOneBySlug($slug)
{
$qb = $this->getQueryBuilderByEnabled();
$query = $qb
->andWhere('seo.slug = :slug')
->andWhere('seo.relationTable = :relationTable')
->setParameters(['slug' => $slug, 'relationTable' => 'product'])
->setMaxResults(1)
->getQuery();

return $query->getOneOrNullResult();
}

1 Reply

Hey Lavin

I'm far from being an expert on translating a site but what I would do is to add a listener for detecting which language is chosen (parsing the URL) and storing it on a session key. Then I would create a doctrine filter for automatically inject the WHERE clause using the session.

Cheers!

Reply
Default user avatar

hi thank for the reply but ive done found the solution :) just put that query into querybuilder and viola its work

1 Reply
Brandon Avatar
Brandon Avatar Brandon | posted 2 years ago

Hello, I'm using Symfony 5, and when I do:
return $this->createQueryBuilder('o')->andWhere('o.orderstoo IS NOT NULL')
->getQuery()
->getResult()
I get the following error:
[Semantical Error] line
0, col 42 near 'orderstoo IS': Error: Invalid PathExpression.
StateFieldPathExpression or SingleValuedAssociationField expected.
Is there something different I need to use for IS NOT NULL?

Reply

Hey Brandon

Is the orderstoo field correctly spelled? It's case sensitive. That's the first thing I'd look at
Second: is that field a relationship? if so, check which entity has the owning side, if you're are working with the not-owning side, then I believe you have to also do a join

I hope it helps. Cheers!

Reply
Brandon Avatar

Diego,
orderstoo is spelled right, I know it looks odd but it what I went with. I also checked case sensitivity as well and I'm good there, I used all lower case. The field is a ManyToMany relationship so I think they are both the owning side. With that said, would a join still be useful? I tried an inner join to orders_email_list but it says that it is undefined.

What I am trying to achieve is that when a user fills out an order, they can choose to e-mail it by selecting check boxes populated from the EmailList table, or just save it in the database. What I would then like is to query for all orders that haven't been e-mailed. I used to do this by checking if that field in the database (orderstoo) was null or not. My old site used to implode an array of e-mail addresses and store them in the database orderstoo field, then explode them out to e-mail them. I didn't use a ManyToMany relationship (this is my first time with them). The more I think about it, what I'm trying to achieve probably won't work unless you have a suggestion?

Reply

So, what you have is an Order can have multiple Emails and an Email can be attached to multiple Orders, yes, it sounds like a ManyToMany relationship. In that kind of relationships Doctrine will handle the extra table internally, you won't even notice that it exists unless you check your Database.
I'd expect what you tried to work but seems like the IS NOT NULL works a bit different that I thought. Here you can see a couple of solutions you can try (the is empty looks promising!) https://stackoverflow.com/q...
Let me know if it worked :)

Reply
Brandon Avatar

Diego, yes sir using is empty and is not empty was it, thank you again!

1 Reply
Dung L. Avatar
Dung L. Avatar Dung L. | posted 3 years ago

Good Morning Symfonycasts,

I have tried to do it and tested but I still failed on a couple query syntax, I hope you can help me.

1) Original native query: update estate set status_id = 7 order by id desc limit 29;

2) I successfully translated it to "Querying with SQL" in my EstateRepository.php using this documentation https://symfony.com/doc/cur...$conn = $this->getEntityManager()->getConnection();
$sql = 'update estate set status_id = 7 order by id desc limit 29';
$stmt = $conn->prepare($sql);
$stmt->execute();

3) However I failed to write it using "Querying with the Query Builder" as in https://symfony.com/doc/cur...

Here is my attemp: $this->createQueryBuilder('a')
->update()
->set('a.statuses', 7)
->orderBy('a.id', 'DESC')
->setMaxResults(30)
->getQuery()
->execute();

4) And I failed to write it using "Doctrine Query Language" as in https://symfony.com/doc/cur...

Here is my attemp:$this->createQuery('a')
->update()
->set('a.statuses', 7)
->orderBy('a.id', 'DESC')
->setMaxResults(30)
->getQuery()
->execute();

My question is: would you please help me construct queries for types number 3) and 4) and where to I go for original documentation to learn from for this topic?

Many thanks!

Reply

Hey Dung L.!

Hmm, interesting question. The original SQL is a bit odd - I've never updated a value using an "ORDER BY"... but I guess that works :). When you try options (3) and (4), do you get an error? Does the query just not work? If it *does* work, you can use the web debug toolbar to see what query it generated. Also, I think you pasted (4) wrong - it matches (3) (it's just another query builder).

Cheers!

Reply
Dung L. Avatar
Dung L. Avatar Dung L. | weaverryan | posted 3 years ago | edited

Hello weaverryan - thank you for your reply

- yes the original native sql query does work.

- this is the error I got from option 3:


C:\xampp\htdocs\project>php bin/console import:data
In QueryException.php line 54:
[Syntax Error] line 0, col 46: Error: Expected end of string, got 'ORDER'
In QueryException.php line 43:
UPDATE App\Entity\Estate a SET a.statuses = 7 ORDER BY a.id DESC

- I lost/forgot how I did option 4 but I will find it again and post back.

What do you think of error in option 3 and what would be your query builder?

Thanks very much!

Reply
Dung L. Avatar

- for option 4 this is how I write it

// Querying for Objects with DQL
$query = $this->getEntityManager()->createQuery(
'update estate e set p.status_id = 7 order by p.id desc limit 29'
);
$query->execute();

- And for all options and queries I mentioned above are written in EstateRepository.php file -> ( https://uofc-my.sharepoint.... ) which is in Repository directory And the call made to them from JsonImport.php file -> ( https://uofc-my.sharepoint.... ) which is in Service directory, the call looks like this

$this->entityManager->getRepository('App:Estate')->resetStatusQueryNoneBuilder();

- The error message for option number 4 is

In QueryException.php line 65:
[Semantical Error] line 0, col 7 near 'estate e set': Error: Class 'estate' is not defined.
In QueryException.php line 43:
update estate e set p.status_id = 7 order by p.id desc limit 29

I apologize if I cannot explain myself very well but please let me know your suggestion. I am confused if you can simplify or help me sort out?

Thanks Ryan!

Reply

Hey Dung L.!

Hmm, ok, I think I know what's going on. Here is a Stack Overflow that talks about a similar problem but with LIMIT instead of ORDER BY: https://stackoverflow.com/q... - I think it's the same issue.

In short, occasionally there will be an SQL feature you use that isn't supported by all database systems. In those cases, because Doctrine is meant to work the same for MySQL, Postgresql, Mssql, Oracle, etc - Doctrine chooses not to support it natively. I have a feeling that this is what's going on in your situation.

So, my best advice is either to:

A) Keep with the raw query
B) Try to write a different query that will not suffer from this problem. You could do that actually with 2 queries: first query for all the "id"s ORDER BY a.id DESC LIMIT 29 and then make a second query to update only those ids - e.g. WHERE id IN (:ids).

Let me know if this helps!

Cheers!

Reply
Dung L. Avatar
Dung L. Avatar Dung L. | weaverryan | posted 2 years ago | edited

Hello weaverryan ,

I have been trying to write a different query that will not suffer from this problem as you suggested but failed. Again this is the query that works using

Querying with SQL Method:


$conn = $this->getEntityManager()->getConnection();
$sql = 'update estate set status_id = 7 order by id desc limit 29';
$stmt = $conn->prepare($sql);
$stmt->execute();

How can I do this with 2 queries using Query Builder Method?

first query for all the "id"s ORDER BY est.id DESC LIMIT 29 and then make
a second query to update only those ids - e.g. WHERE id IN (:ids).

I totally understand your instruction (I tried nested queries) it is just I am not able to translate that into query builder syntax :) sorry for being a such a dense person, if you have an example course tutorial somewhere I will learn and work it out from there, please point me to?

Thanks Ryan!

Best regards,

1 Reply

Hey Dung,

What exactly problem did you get writing that query? If you want to do low level queries using getConnection(), here's a screencast where we show how to do it in a Symfony project: https://symfonycasts.com/sc... - please, notice that we refer exact table and column names in MySQL DB, not class and property names.

Otherwise, if you want to do this in a few queries instead of one - you just need to fetch all the IDs you need in one query, I suppose you know how to do it, it should be pretty simple. You can add "->select(estate.id)" to your query builder to specify you want to fetch only ID column, and at the end instead of "->getQuery()->getResult()" call "->getQuery()->getScalarResult()" to avoid object hydration.

For the 2nd query, yes, use "IN (:ids)", e.g:
->andWhere('estate.id IN (:estate_ids)')

->setParameter('estate_ids', $estateIds);

Where $estateIds is just a PHP array of IDs, like "[2,7,8,23]".

I hope this helps!

Cheers!

Reply
Dung L. Avatar
Dung L. Avatar Dung L. | victor | posted 2 years ago | edited

victor it does help, i got it working, thank you so much Victor, so much to learn and I will keep learning :)


$qb = $this->createQueryBuilder('e')
->orderBy('e.id', 'ASC')
->select('e.id')
->setMaxResults(7)
->getQuery();
$scalarResult = $qb->getScalarResult();

$qb2 = $this->createQueryBuilder('e');
$qb2->update()
->set('e.statuses', ':statuses')
->setParameter('statuses', 2)
->andWhere('e.id IN (:e_ids)')
->setParameter('e_ids', $scalarResult)
->getQuery()
->execute();
Reply

Hey Dung,

Yay, I really happy to hear it helps! And thank you for sharing your final solution, it might be useful for others ;)

Cheers!

Reply
Dung L. Avatar

Great support and guidance I got form SymfonyCasts courses. Thanks!

Reply
Dung L. Avatar

Excellent! Thank you for the SOF link, your short explanation, B) solution. I will write it in 2 queries. Appreciate your guidance!

Reply

I have the repositoryClass="App\Repository\ArticleRepository" annotation in the Article.php class file but it's wanting to default to the ObjectRepository. Any suggestions?

Reply

hey brianbarrick

Is it related to course code or your personal? Could you please share your ArticleRepository definition code and Symfony version you are using?

Cheers!

Reply

Hi Vladimir, no this is the code from the code download section right out of the box. I just created a new project ran composer install and started with the tutorial. All of the class definitions should be exactly the same as the tutorial. I did however continue the video and passing the Article repository to the controller fixes it, but anyone else who comes along may run in to the same issue.

createQueryBuilder('a')
->andWhere('a.publishedAt IS NOT NULL')
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}

Reply

Sounds interesting! Does it throws an exception?

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.1.4
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.0.4
        "symfony/console": "^4.0", // v4.0.14
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/framework-bundle": "^4.0", // v4.0.14
        "symfony/lts": "^4@dev", // dev-master
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/twig-bundle": "^4.0", // v4.0.4
        "symfony/web-server-bundle": "^4.0", // v4.0.4
        "symfony/yaml": "^4.0" // v4.0.14
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.4
        "fzaninotto/faker": "^1.7", // v1.7.1
        "symfony/debug-bundle": "^3.3|^4.0", // v4.0.4
        "symfony/dotenv": "^4.0", // v4.0.14
        "symfony/maker-bundle": "^1.0", // v1.4.0
        "symfony/monolog-bundle": "^3.0", // v3.1.2
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.0.4
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.0.4
    }
}