SymfonyCasts stands united with the people of Ukraine
If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeWe just learned that when you ask for a repository, what you actually get back is a custom class. Well, technically you don't have to have a custom repository class - and if you don't, Doctrine will just give you an instance of EntityRepository. But in practice, I always have custom repository classes.
Anyways, when we ask for the repository for the Question entity, we get back an instance of this QuestionRepository. The cool thing is that we can add custom methods to hold custom queries. In fact, every time I write a custom query, I'll put it in a repository class.
Here's the new goal: I want to change the query on the homepage so that it hides any questions WHERE askedAt IS NULL. This will hide "unpublished" questions.
We know that we use SQL queries to talk to databases. Internally, Doctrine has a slightly different language called DQL: Doctrine Query Language. But don't worry, it's almost identical to SQL. The main difference is that, with DQL, you reference class and property names instead of table and column names. Otherwise, it basically looks the same.
Now, you can absolutely write DQL strings by hand and execute them. Or you can use a super handy object called the QueryBuilder, which allows you to build that DQL string using a convenient object. That is what you see here.
The $this->createQueryBuilder() line creates the QueryBuilder object. And because we're inside of the QuestionRepository, the QueryBuilder will already know to query FROM the question table. The q is basically the table alias, like SELECT * FROM question as q. We'll use that everywhere to refer to properties on Question.
Then, most of the methods on QueryBuilder are pretty intuitive, like, andWhere() and orderBy(). setMaxResults() is probably one of the least intuitive and it's still pretty simple: this adds a LIMIT.
Check out the andWhere(): q.exampleField = :value. Doctrine uses prepared statements... which is a fancy way of saying that you should never concatenate a dynamic value into a string. This allows for SQL injections.
Instead, whenever you have something dynamic, set it to a placeholder - like :value and then set that placeholder with setParameter(). This is how prepared statements work. It's not unique at all to Doctrine, but I wanted to point it out.
Ok: let's clear out these four lines and make our own query. Start with ->andWhere('q.askedAt IS NOT NULL').
| ... lines 1 - 14 | |
| class QuestionRepository extends ServiceEntityRepository | |
| { | |
| ... lines 17 - 24 | |
| public function findAllAskedOrderedByNewest() | |
| { | |
| return $this->createQueryBuilder('q') | |
| ->andWhere('q.askedAt IS NOT NULL') | |
| ... line 29 | |
| ->getQuery() | |
| ->getResult() | |
| ; | |
| } | |
| ... lines 34 - 45 | |
| } |
I'm using askedAt because that's the name of the property... even though the column in the table is asked_at. Now add ->orderBy() with q.askedAt and DESC.
| ... lines 1 - 14 | |
| class QuestionRepository extends ServiceEntityRepository | |
| { | |
| ... lines 17 - 24 | |
| public function findAllAskedOrderedByNewest() | |
| { | |
| return $this->createQueryBuilder('q') | |
| ->andWhere('q.askedAt IS NOT NULL') | |
| ->orderBy('q.askedAt', 'DESC') | |
| ->getQuery() | |
| ->getResult() | |
| ; | |
| } | |
| ... lines 34 - 45 | |
| } |
Oh, and notice that I'm using andWhere()... even though there are no WHERE clauses before this! I'm doing this for 2 reasons. First... because it's allowed! Doctrine is smart enough to figure out if it needs an AND statement or not. And second, there is a where() method... but it's kind of dangerous because it will override any where() or andWhere() calls that you had earlier. So, I never use it.
Once we're done building our query, we always finish with getQuery() to transform it into a finished Query object. Then, the getResult() method will return an array of Question objects. My @return already says this! Woo!
The other common final method is getOneOrNullResult() which I use when I want to find a single record.
Ok: with any luck, this will return the array of Question objects we need! Let's try it! Find your browser, refresh and... no errors! But I can't exactly tell if it's hiding the right stuff. Let's click on the web debug toolbar to see the query. I think that's right! Click "View formatted query". That's definitely right!
We're not going to talk too much more about creating custom queries, but we do have an entire tutorial about Doctrine queries. It's built on an old version of Symfony, but all of the info about Doctrine queries hasn't changed.
And yes, if you ever have a super duper custom complex query and you just want to write it in normal SQL, you can absolutely do that. The Doctrine queries tutorial will show you how.
Anyways, whenever we need to query for something, we're going to get the repository for that entity and either call a custom method that we created or a built-in method. And actually... I've been making us do too much work! There's an easier way to get the repository. Instead of autowiring the entity manager and calling getRepository(), the QuestionRepository itself is a service in the container. That means we can autowire it directly!
Check it out: remove the EntityManagerInterface argument and replace it with QuestionRepository $repository. Celebrate by deleting the getRepository() call.
| ... lines 1 - 5 | |
| use App\Repository\QuestionRepository; | |
| ... lines 7 - 13 | |
| class QuestionController extends AbstractController | |
| { | |
| ... lines 16 - 28 | |
| public function homepage(QuestionRepository $repository) | |
| { | |
| $questions = $repository->findAllAskedOrderedByNewest(); | |
| return $this->render('question/homepage.html.twig', [ | |
| 'questions' => $questions, | |
| ]); | |
| } | |
| ... lines 37 - 99 | |
| } |
If we move over and refresh... it still works! In practice, when I need to query for something, this is what I do: I autowire the specific repository I need. The only time that I work with the entity manager directly is when I need to save something - like we're doing in the new() method.
Thanks to the QueryBuilder object, we can leverage a pattern inside our repository that will allow us to reuse pieces of query logic for multiple queries. Let me show you how next.
Hey HudyWeas,
> Project doesn't see QuestionRepository.php as a part of project
Hm, what do you mean? Please, make sure you have the correct namespace for that class first.
About advanced autcompletion - make sure you installed and enabled Symfony Plugin in PhpStorm for your project. Most of the cool autocompletion comes from there. If still not work - try to restart PhpStorm. Also, you can easily teach your PhpStorm what is object exactly is set to a variable with inlined "/** @var ClassName $varName" above the variable on which you don't have autorompleteion.
I hope this helps!
Cheers!
thats what php storm said to me when i wanted to edit this file. There opened a window asking me if i want to edit file that isnt a part of a project
Hey HudyWeas,
Oh, wait, you're probably talking about editing vendor files, you should not do this 😅 Better, create a new variable in *your* repository and add that var annotation - this should help :)
Cheers!
im not talking about files in vendor directory. Im talking about files created like Question.php and QuestionRepository. There is set namespace but still its not seen. When i set use statement manually in QuestionController.php use App\Repository\QuestionRepository;
it's yellow and says "Undefinied namespace Repository"
QuestionRepository.phpnamespace App\Repository;
Hey HudyWeas,
Ah, ok... Hm, something is not right probably. Fairly speaking, I've never seen this error myself. I'd recommend you to close your PhpStorm and then remove the project completely with running "rm -rf .idea/" command in the project folder. It will remove the PhpStorm's project config files. Then run the PhpStorm again and try to open a directory with your project, i.e. click on "File" -> "Open" -> and choose the folder with your project if you have Mac. This will open your Symfony project and creates a new PhpStorm project for it. If you have troubles with using PhpStorm - I'd recommend you to watch this free course about it: https://symfonycasts.com/sc...
I hope this helps!
Cheers!
I'm getting this error with the custom query. There is no parameter defined in the function. I've cleared the cache. Is this new behavior?
You need to pass a parameter to 'findByAllAskedOrderedByNewest'
Hey @Aaron!
Ah, I think it's just a tiny typo in your method name :). You have an extra "By" in there - it should just be findAllAskedOrderedByNewest. You're getting this weird error because the entity repository class has some "magic" in it: if you call an undefined method on it that starts with "findBy" it tries to parse it and create a query from it. So Doctrine basically tries to make a query to find by "orderedByNewest = the argument", which makes no sense of course :p. Without that magic, you would just get a "method not found error" and it would be easier to debug.
Cheers!
LOL the number of times a typo has caused me grief is insane. Symfony is like 20 - 0 in the battle of who's the problem with me. Not enough coffee I guess.
Hello!
First off all, your tutorials really helped me understand symfony and its surroundings.
But I have 1 simple problem I couldn't get fixed.
You are saying that I can ether use the Querybuilder to build my SQL statements or raw SQL statements and I would like to just write raw SQL statements. After looking at the linked tutorial regarding Raw SQL Queries Link I saw that he's not getting an Object returned from the Raw SQL. It just returns him an Array If I am correct.
So he's still using the QueryBuilder + raw SQL.
Is it possible to just use Raw SQL? if so could you give me the function with only raw SQL as an example? Thanks!
Keep doing tutorials, I hope there's a tutorial for Login etc. ;)
Hi Janik S.!
After looking at the linked tutorial regarding Raw SQL Queries Link I saw that he's not getting an Object returned from the Raw SQL. It just returns him an Array If I am correct.
Yes, that is correct. You can absolutely write raw SQL queries. But what is returned from the database is a "raw array of data". And that's usually fine - because usually if you are choosing to write very complex queries, then returning an entity object doesn't make sense. For example, you might have a raw query that selects a AVG or SUM of some fields. It only makes sense to return that as an array (your return set doesn't have the same data as your entity object).
However, there are 2 ways to use raw SQL and get an objec:
1) Do it yourself! Suppose you're writing some complex SQL query that returns 3 columns - a SUM, AVG and count of something. If you would like to put this into an object, you could create a class (in any directory, this is not managed by Doctrine) that looks like this:
class SalesStatistics
{
public $totalSales;
public $averageSales;
public $totalCustomers;
}
I'm using public properties for simplicity - you could also make them private with getters/setters. The point is, after making your SQL query (and getting an array back), you would create this object manually, set the data on it (from the array) and then return this object. This is a really nice solution.
2) Doctrine has a way to do a raw SQL query but then have it convert that to an object. I've not used it much, but it's called result set mapping https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/native-sql.html#examples
Let me know if that helps :).
Cheers!
// composer.json
{
"require": {
"php": "^7.4.1",
"ext-ctype": "*",
"ext-iconv": "*",
"composer/package-versions-deprecated": "^1.11", // 1.11.99
"doctrine/doctrine-bundle": "^2.1", // 2.1.1
"doctrine/doctrine-migrations-bundle": "^3.0", // 3.0.2
"doctrine/orm": "^2.7", // 2.8.2
"knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
"knplabs/knp-time-bundle": "^1.11", // v1.16.0
"sensio/framework-extra-bundle": "^6.0", // v6.2.1
"sentry/sentry-symfony": "^4.0", // 4.0.3
"stof/doctrine-extensions-bundle": "^1.4", // v1.5.0
"symfony/asset": "5.1.*", // v5.1.2
"symfony/console": "5.1.*", // v5.1.2
"symfony/dotenv": "5.1.*", // v5.1.2
"symfony/flex": "^1.3.1", // v1.17.5
"symfony/framework-bundle": "5.1.*", // v5.1.2
"symfony/monolog-bundle": "^3.0", // v3.5.0
"symfony/stopwatch": "5.1.*", // v5.1.2
"symfony/twig-bundle": "5.1.*", // v5.1.2
"symfony/webpack-encore-bundle": "^1.7", // v1.8.0
"symfony/yaml": "5.1.*", // v5.1.2
"twig/extra-bundle": "^2.12|^3.0", // v3.0.4
"twig/twig": "^2.12|^3.0" // v3.0.4
},
"require-dev": {
"doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
"symfony/debug-bundle": "5.1.*", // v5.1.2
"symfony/maker-bundle": "^1.15", // v1.23.0
"symfony/var-dumper": "5.1.*", // v5.1.2
"symfony/web-profiler-bundle": "5.1.*", // v5.1.2
"zenstruck/foundry": "^1.1" // v1.5.0
}
}
I have real problem with autocompleting syntaxes like here. It doesnt show me ->orderBy or anything else. Am i missing an option or something? I think I installed wverything right in previous courses but I may be wrong.
Another thing. Project doesn't see QuestionRepository.php as a part of project.