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.
Time to put that lazy isPublished field to work. I only want to show published
genuses on the list page. Up until now, we've been the lazy ones - by using findAll()
to return every Genus object. We've avoided writing queries.
There are a few other methods besides findAll() that you can use to customize
things a bit, but look: someday we're going to need to grow up and write a custom
query. It's time to grow up.
To query, we always use this repository object. But, uh, what is that object anyways?
Be curious and dump $em->getRepository('AppBundle:Genus) to find out:
| ... lines 1 - 11 | |
| class GenusController extends Controller | |
| { | |
| ... lines 14 - 33 | |
| public function listAction() | |
| { | |
| $em = $this->getDoctrine()->getManager(); | |
| dump($em->getRepository('AppBundle:Genus')); | |
| ... lines 39 - 44 | |
| } | |
| ... lines 46 - 99 | |
| } |
Refresh! I didn't add a die statement - so the dump is playing hide-and-seek down
in the web debug toolbar. Ah, it turns out this is an EntityRepository object -
something from the core of Doctrine. And this class has the helpful methods on
it - like findAll() and findOneBy().
Ok, wouldn't it be sweet if we could add more methods to this class - like findAllPublished()?
Well, I think it would be cool. So let's do it!
No no, not by hacking Doctrine's core files: we're going to create our own repository
class. Create a new directory called Repository. Inside, add a new class - GenusRepository.
None of these names are important. Keep the class empty, but make it extend that
EntityRepository class so that we still have the original helpful methods:
| ... lines 1 - 2 | |
| namespace AppBundle\Repository; | |
| use Doctrine\ORM\EntityRepository; | |
| class GenusRepository extends EntityRepository | |
| { | |
| } |
Next, we need to tell Doctrine to use this class instead when we call getRepository().
To do that, open Genus. At the top, @ORM\Entity is empty. Add parentheses,
repositoryClass=, then the full class name to the new GenusRepository:
| ... lines 1 - 6 | |
| /** | |
| * @ORM\Entity(repositoryClass="AppBundle\Repository\GenusRepository") | |
| * @ORM\Table(name="genus") | |
| */ | |
| class Genus | |
| ... lines 12 - 95 |
That's it! Refresh! Now the dump shows a GenusRepository object. And now we
can start adding custom functions that make custom queries. So, each entity that needs
a custom query will have its own repository class. And every custom query you write
will live inside of these repository classes. That's going to keep your queries
super organized.
Add a new public function called findAllPublishedOrderedBySize():
| ... lines 1 - 7 | |
| class GenusRepository extends EntityRepository | |
| { | |
| ... lines 10 - 12 | |
| public function findAllPublishedOrderedBySize() | |
| { | |
| ... lines 15 - 20 | |
| } | |
| } |
I'm following Doctrine's naming convention of findAllSOMETHING for
an array – or findSOMETHING for a single result.
Fortunately, custom queries always look the same: start with, return
$this->createQueryBuilder('genus'):
| ... lines 1 - 7 | |
| class GenusRepository extends EntityRepository | |
| { | |
| ... lines 10 - 12 | |
| public function findAllPublishedOrderedBySize() | |
| { | |
| return $this->createQueryBuilder('genus') | |
| ... lines 16 - 20 | |
| } | |
| } |
This returns a QueryBuilder. His favorite things are pizza and helping you
easily write queries. Because we're in the GenusRepository, the query already
knows to select from that table. The genus part is the table alias - it's like
in MySQL when you say SELECT * FROM genus g - in that case g is an alias you
can use in the rest of the query. I like to make my aliases a little more descriptive.
To add a WHERE clause, chain ->andWhere() with genus.isPublished = :isPublished:
| ... lines 1 - 14 | |
| return $this->createQueryBuilder('genus') | |
| ->andWhere('genus.isPublished = :isPublished') | |
| ... lines 17 - 23 |
I know: the :isPublished looks weird - it's a parameter, like a placeholder.
To fill it in, add ->setParameter('isPublished', true):
| ... lines 1 - 14 | |
| return $this->createQueryBuilder('genus') | |
| ->andWhere('genus.isPublished = :isPublished') | |
| ->setParameter('isPublished', true) | |
| ... lines 18 - 23 |
We always set variables like this using parameters to avoid SQL injection attacks. Never concatenate strings in a query.
To order... well you can kind of guess. Add ->orderBy() with genus.speciesCount
and DESC:
| ... lines 1 - 14 | |
| return $this->createQueryBuilder('genus') | |
| ->andWhere('genus.isPublished = :isPublished') | |
| ->setParameter('isPublished', true) | |
| ->orderBy('genus.speciesCount', 'DESC') | |
| ... lines 19 - 23 |
Query, done!
To execute the query, add ->getQuery() and then ->execute():
| ... lines 1 - 14 | |
| return $this->createQueryBuilder('genus') | |
| ->andWhere('genus.isPublished = :isPublished') | |
| ->setParameter('isPublished', true) | |
| ->orderBy('genus.speciesCount', 'DESC') | |
| ->getQuery() | |
| ->execute(); | |
| ... lines 21 - 23 |
That's it! Your query will always end with either execute() - if you want an array
of results - or getOneOrNullResult() - if you want just one result... or obviously
null if nothing is matched.
Let's really show off by adding some PHP doc above the method. Oh, we can do better
than @return mixed! We know this will return an array of Genus objects - so
use Genus[]:
| ... lines 1 - 4 | |
| use AppBundle\Entity\Genus; | |
| ... lines 6 - 7 | |
| class GenusRepository extends EntityRepository | |
| { | |
| /** | |
| * @return Genus[] | |
| */ | |
| public function findAllPublishedOrderedBySize() | |
| { | |
| ... lines 15 - 20 | |
| } | |
| } |
Our hard work is done - using the new method is simple. Replace findAll() with
findAllPublishedOrderedBySize():
| ... lines 1 - 11 | |
| class GenusController extends Controller | |
| { | |
| ... lines 14 - 33 | |
| public function listAction() | |
| { | |
| ... lines 36 - 37 | |
| $genuses = $em->getRepository('AppBundle:Genus') | |
| ->findAllPublishedOrderedBySize(); | |
| ... lines 40 - 43 | |
| } | |
| ... lines 45 - 98 | |
| } |
Go back, refresh... and there it is! A few disappeared because they're unpublished. And the genus with the most species is first. Congrats!
We have an entire tutorial on doing crazy custom queries in Doctrine. So if you want to start selecting only a few columns, using raw SQL or doing really complex joins, check out the Go Pro with Doctrine Queries.
Woh guys - we just crushed all the Doctrine basics - go build something cool and tell me about it. There's just one big topic we didn't cover - relationships. These are beautiful in Doctrine, but there's a lot of confusing and over-complicated information about there. So let's master that in the next tutorial. Seeya guys next time!
Hey Peter,
Doctrine model, i.e. Genus entity in our case, or any other entity - can't query the database, it's an architectural pattern of Doctrine. To query the database - you need to use entity repository, so you can some default methods in default repository like findAll(), findBy(), etc. *or* extend this default repository with your own, where you can define more custom methods like findAllPublishedOrderedBySize(), etc. Sometimes, for some simple queries, the default repository methods are enough, but if you want write complex queries, use JOIN, GROUP BY, etc. - you will need custom queries.
Actually, you can write custom queries in controllers, but it's a bad practice, because you mix layers and also you can't reuse your custom queries in this case. So better write it in custom repositories, where you can easily reuse and make them more readable. Actually, that was the advantages of custom repositories.
I think it's clearer for you now. If you have more questions - let us know.
Cheers!
Dear Victor,
I totally understand. Thank you very much for your detailed explanation!
Cheers
Peter
Hi Ryan, first of all thanks for the great tutorials. Its fun to learn with. Really great.
In this one I am stuck. I am using symfony 3.2.4 and went trough this tutorial for creating customer queries. I've added Repository Class and ORM Notation but ... Symfony is telling me:
The method name must start with either findBy or findOneBy!
I am out, I have no clue anymore.
After some research I found the solution. Don't know why but I had to add
repository-class="AcmeBundle\Repository\AcmeRepository"
within the <entity>-Key to the Resources\doctrine xml-File. Why?
Hey Thomas!
Good find on your solution! You're getting "bit" by a bit of a bad error message. In this chapter (https://knpuniversity.com/screencast/symfony-doctrine/custom-queries#creating-your-own-repository), we talk about how, out-of-the-box, when you call $em->getRepository('AppBundle\Entity\Genus'), Doctrine returns a generic EntityRepository class. This has a few methods on it, like find(), findOneBy(), findAll(), etc. But as soon as you want to add your own custom methods to this, then you'll create your own repository class (AcmeRepository in your case). But, how does Doctrine know to use your class instead of its generic EntityRepository class? The answer: by adding (if you're using XML mapping) the repository-class option to your XML file. If you're using annotations (like we are), then we have that same config above the class (repositoryClass=".."). With this config, Doctrine now knows to use your class when you ask for the repository.
So when you get the The method name must start with either findBy or findOneBy! error, it was because you were calling your custom method on the internal, EntityRepository class. Really, you should have seen a message more like Undefined method findAcmeStuff() on class EntityRepository. With this error, it would have been a bit more obvious that Doctrine was not using your class. But, the EntityRepository has a magic __call method, which allows you to make "magic" calls to it - e.g. you can say "findOneBySlug('foo')" - that method doesn't exist, but the __call method translates it into a findOneBy(['slug' => 'foo']). I never take advantage of this, but it's the reason for the error. You can see it right in the EntityRepository class: https://github.com/doctrine/doctrine2/blob/e4704beaf9fad5a521fe7592dec382ae209b3cc1/lib/Doctrine/ORM/EntityRepository.php#L224
tl;dr; You make the right change! And before this change, Doctrine wasn't using your custom class (so you got this error from the core of Doctrine).
Let me know if this helps! And Cheers!
Hi Ryan, thanks a lot for making this clear.
Additional I found out why symfony/doctrine was checking the XML Files instead of my Entites ... the XML-Files was created caused by Reverse Engineering of existent database. Thats why doctrine is checking these files instead of using the annotations within entities.
Now I ran into equal issues while using relationships. Just deleted the XML-Files and now all is fine ;-)
Nice job Thomas L.! That is a gotcha of reverse engineering! As soon as Doctrine finds *one* metadata format, it just stops looking for the other formats. Good debugging!
I swear I'd run into this before, but I can't for the life of me remember how it happened.
I'm calling a custom repository method that I created, but I'm getting an error as if it does not exist.
BadMethodCallException<br />HTTP 500 Internal Server Error<br />Undefined method 'findAllByCustomerOrInternal'. The method name must start with either findBy, findOneBy or countBy!
Now, if I change the method to start with findBy, it throws a different error:Entity 'App\Model\Entity\Location' has no field 'customerOrInternal'. You can therefore not call 'findByCustomerOrInternal' on the entities' repository
Which is true, but I'm not trying to filter on a single field, I'm trying to use a custom query builder.
I'm using similar methods to the first method elsewhere in my codebase, but it's not throwing an error.
This return all object, but maybe unnecessary fields.
$qb = $this->createQueryBuilder('cat')
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc');
This return array, but only with the fields i want.
$qb = $this->createQueryBuilder('cat')
->select('cat.id')
->addSelect('cat.name')
->leftJoin('cat.fortuneCookies', 'fc')
->addSelect('fc.id')
->addSelect('fc.fortune');
I believe, about perfomance, second option is better. But many courses teach only the first option.
Hi there!
You're absolutely right! The first way is taught more commonly because it's easier to be returned objects (it's nice when you're working with the same object 99% of the time, instead of an array with certain fields) *and* performance often doesn't matter. I'd recommend doing the first way *until* you find that you need to increase performance. And once you know that you need more performance, I'd also recommend using Blackfire.io to identify what's slow: you might find that the queries are not the problem anyways :).
Cheers!
Hello Ryan! One more time i`m here...
I`m in love with symfony... but i still have some doubts... i learned how to do some querys... But now i need perform some advanced querys...
For sample... I did implemented this query:
$em = $this->getDoctrine()->getManager();
$query = "SELECT *, COUNT(1) AS count
FROM ( SELECT * FROM visualizacoes WHERE idperfil = $idperfil ) a
GROUP BY idusuario, idperfil, data";
$stmt = $em->getConnection()->prepare($query);
$stmt->execute();
$stmt->fetchAll();
How can i implement it with doctrine? because the result of this will be an list of how much times a people viewed an profile... but a profile can have lots of views from lots of users....
i really wanna implement it in SF3
Thanks!
Yo Raphael!
For tough queries like this, where you don't expect to get back an object (but instead, an array), it's *ok* if you need to make straight SQL queries. So, I honestly wouldn't worry too much about this, as long as you're not doing too much of this stuff. Btw, even if you are writing SQL queries, I highly recommend putting this inside repository methods - like http://knpuniversity.com/sc....
However, I will say that I don't fully understand your schema setup, but this query *does* look pretty complicated, even in SQL - it's a bit confusing to look at :). It's possible that your database schema could be setup better to make this query easier. But again, I only have a small part of the whole picture :).
Oh, and you *could* probably write a query like this with DQL or with the query builder. I was going to try to translate it for you, but it's such an odd query (because the FROM is actually selecting a sub-query), that it's actually a bit hard for me too :).
Cheers!
Thank you Ryan! I`m not a good guy when talking about Database... maybe there is how to do what i`m doing in a best way.... But thanks... I`ll have a look in that tutorial... i need learn more also about Symfony Services... Create objects that will be shared across all the application... i`ll take a look in that tutos also... thanks
Hello again! Towards the end of the tutorial you replace @return mixed with @return Genus[], in the GenusRepository.php - I dont fully understand why this has been done. I understand where it automatically adds use AppBundle\Entity\Genus; Is there any other reason you did that? Thank you so much for all your help sir!
Hi Kosta!
Yes, really good question! There is only *one* reason I do this: PHPStorm auto-completion. Doing this doesn't change any behavior in my application. But now, when I call the findAllPublishedOrderedBySize() method, PhpStorm knows that it returns an *array* of Genus objects - the [] tells it that it's an array. If I iterate over that array, PhpStorm will auto-complete the methods on the Genus object :). So basically, I *love* autocompletion, so I do these types of things.
Cheers!
hey again ryan, I am so thankful for all our help and tutorials brother they are awesome! I have come to a little problem and I know the answer is simple. I have two databse tables memo and users. when a memo is made it saves the id of the user who made it. eg.1, now if I'm viewing the memo list on the html page. I can see the message and under the html colum "created by" - it will show currently the number 1. not the name of the user because the memo db table only has stored the number 1. My question is what is the best way to get all the details of that user (from the users table) so that I can display his name. Should I create a public function getAuthor() and place it inside the entity class memo.php or do I get it inside the controller for that page. Also if there is already a tutorial that covers this similar thing as it would happen all the time, can you point me to the tutorial, thank you so much for everything!
Hi Kosta!
My pleasure - I'm so happy you this stuff is useful for you!
Ok, about your situation: Do you have a ManyToOne relationship from Memo to User? It *sounds* like you a just storying the id of the user, and not using an actual Doctrine relationship. In the database, both options result in the same structure: your memo table will have a user_id column. But if you map this as a true ManyToOne relationship, then you *should* be able to say (in php( $memor->getCreatedBy()->getUsername(). Or, equivalently in Twig, memo.createdBy.username.
If I'm right that you're not using a ManyToOne relationship, then check out: http://knpuniversity.com/sc.... But if you *do* have this relationship mapped correctly, you should be able to simply say {{ memo.createdBy.username }} to print out the username of the user who created this memo.
Let me know if this helps!
Thank you for the video series. It's fun to watch and very informative.
This is my second day with Symfony. I've used Laravel for a while. I was just wondering if all this code for entity manager is necessary:
$em = $this->getDoctrine()->getManager();
$genuses = $em->getRepository('AppBundle:Genus')->findAll();
I'm used to Laravel where you just type:
$genuses = Genus::all();
Is there a shortcut to get rid of the boilerplate code?
Yo Mateusz Sobczak !
Yes, very good question! Eloquent and Doctrine use the 2 different major "types" of ORM. Eloquent is called an "Active Record"... which basically means that when you query, you actually use the class (e.g. Genus). Doctrine is known as a data mapper, where your class (Genus) is just a simple, non-magic class that holds data (and then you do your queries through a different object). Which is better is a major area of debate: data mappers are "cleaner OO code", but take more work. Using static calls that an active record requires is usually not a great idea... but in practice, it usually works well enough.
So that's the explanation about why there are two objects floating around :). In reality, I usually do shortcut things a bit - you can't get as short as Eloquent due to the difference above, but you can get closer. For example, if you create your own base controller (that extends Symfony's normal Controller) and add a protected function getEm(), then you could have this:
$genuses = $this->getEm()->getRepository(Genus::class)->findAll();
Or you could even add a protected function getRepository($class) and shorten to:
$genuses = $this->getRepository(Genus::class)->findAll();
That's probably about as short as you'll get it - not as short, but a lot closer :). I personally like the data mapper way of doing things because it makes sense to have one object that simply holds data, and another object that's really good at querying (separation of concerns).
Cheers and welcome to Symfony :)
Thanks much for your explanation! It helps me get a better grasp of the bigger picture. And I'll definitely use getRepository helper :-)
Hi again Ryan!
I've got an annoying problem =( I'm trying to use Fixtures + Alice and while doing simple things it works perfect, when I try to query an entity to populate other tables I get and empty array.
I have followed the documentation and added:
use Symfony\Component\DependencyInjection\ContainerAwareInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;
I'm doing this:
public function account()
{
$em = $this->container->get('doctrine')->getManager();
/** @var \App\Bundle\CoreBundle\Entity\Repository\AccountRepository $account */
$account = $em->getRepository('CoreBundle:Account')->find(1);
return $account->getId();
}
But all I get is an empty array:
array(0) {
}
Any ideas what may I be doing wrong? Thanks in advance for your help.
Hi Sergio
Is this a custom "formatter"? So you have something like this in your YAML: <account()>?
My guess is that Alice actually "flushes" all of the data at once. What I mean is, even though Alice may have parsed your YAML file for the CoreBundle:Account entity, these have not actually be flushed to the database yet. So, there's nothing in the database to query.
Typically, you relate entities by using the '@' symbol in YAML - http://knpuniversity.com/sc...
Any reason why you can't do it that way?
Cheers!
You're a legend mate!
That worked just great!
Never mind the other question just find that it was my bad, haven't defined the relationship on the entity =( It works all good now!
Hi. I want to tell you that this track explains Symphony in a really friendly way.
However, after this Doctrine course, I prefer to use plane PHP for connecting to my database (keep things simpler). But I wonder if I can take advantage of some Symfony features for this. For example, use the parameters.yml file or use a special service?
Do you have any tutorials about this or any recommendations? I will appreciate it.
Hey Cesar,
With Doctrine you get both ORM and DBAL. We explain ORM in this course, it's higher level and more fun. However, ORM is based on the more lower level like DBAL. Actually, you can think of Doctrine DBAL as about PDO, it's just a fancier wrapper for PDO objects. And we have a screencast about using it in your project: https://knpuniversity.com/s... . So if you don't want to use ORM yet, take a look at Doctrine DBAL instead of PDO or other plane PHP for connecting to your database. I think here's the most interesting article in docs for you: http://docs.doctrine-projec... .
So what about your questions: yes, you can! But you even don't need to configure anything if you want to try Doctrine DBAL since it configured out-of-the-box. But if you still want to write your custom service for it - it's up to you, you can do it as well.
Cheers!
Thanks Victor. I am using DBAL now and it's great. Maybe it's because I am not an expert but I couldn't find the advantages of the ORM.
Hey Cesar,
The main advantage of ORM is that you operate objects and ORM lib like Doctrine do a lot of work for you. But yeah, it depends on your aims.
Cheers!
Hi, i'm doing my own project following you guide, i did try many ways but i'm still getting this:
Dump: https://gyazo.com/9bf0e8594...
PlayersController.php on line 28:
EntityRepository {#347 â–¼
#_entityName: "AppBundle\Entity\Players"
#_em: EntityManager {#457 …11}
#_class: ClassMetadata {#369 â–¶}
}
i dont know why is not recognizing my PlayersRepository, i checked everything, maybe some config?
PlayersEntity: https://gyazo.com/c9f5917ac...
PlayersRepository: https://gyazo.com/12e46654f...
PlayersController: https://gyazo.com/26b36675a...
Hey Yeison J. Espinoza
Your repository configuration looks good to me.
Which version of Symfony are you using ?
The dump that you printed is for this line ?`
$this->em->getRepository('AppBundle:Players');`
if so, it looks like you are getting the right repository and your code should just work, so I'm guessing that for any reason you are having troubles with the cache (I suppose you are running in "dev" environment), try executing $ bin/console cache:clear
I hope it helps you, cheers!
Alright, so what you mean with "Not recognizing" I see in your dump that your Players entity class is been mapped, what happens when you execute
$this->em->getRepository('AppBundle:Players')->findAllOrderedByName()```
Also you don't have to inject the EntityManager into a Controller Method, you can gather it directly from the container
$this->getDoctrine()->getManager();
"i dont know why is not recognizing my PlayersRepository, i checked everything, maybe some config?"
It shouldn't be the case, you are using all the standards, but let me check your config.yml file.
Are you running in dev or prod environment ?
I'm still waiting to get some help :S
I remake the whole project and follow all steps and still getting EntityRepository instead of my custom Repository
Hey Yeison,
Try fetching the EntityManager from the Container instead of injecting it via the ParamConverter, something like this:
// PlayerController
public function playersAction() {
$em = $this->getDoctrine()->getManager();
$repository = $em->getRepository(Players::class);
$players = $repository->findAllOrderedByName();
}
Cheers!
Hey Yeison,
Are you trying to do so in dev environment? Because for the prod one you probably need to clear the cache. Actually, I advise you to clear the cache for both dev and prod environments, or better manually remove everything in the cache folder with the next command:
rm -rf var/cache/*
Btw, do you use any bytecode cache engines for doctrine annotations, like OPCache or APCu? If so, you need manually clear that cache as well.
Cheers!
Hey Yeison,
Let's figure it out why this is happening.
First, try creating a new Entity and a new Repository, but name it in singular i.e. Player, then dump it and let's see if it works.
If it does not work, I believe it's caused by Doctrine's cache, so run this, and try it again
$ bin/console doctrine:cache:clear-metadata
I hope this will do the trick
Cheers!
I'm not getting a way to fix that problem, here is the whole code if some one can check and help
---> https://mega.nz/#!hUkijIQb!...
Hey Yeison J. Espinoza
Finally I could find your problem, you are mixing your entities configuration, you have a YML file that defines your Player entity, but also, you have annotations on it, you have to decide which one you want to use, look's like symfony by default detects yml first, and you are not defining which is it's repository class, so you just have to add this into Players.orm.yml:
AppBundle\Entity\Players:
repositoryClass: AppBundle\Repository\PlayersRepository
Cheers!
// composer.json
{
"require": {
"php": ">=5.5.9",
"symfony/symfony": "3.1.*", // v3.1.4
"doctrine/orm": "^2.5", // v2.7.2
"doctrine/doctrine-bundle": "^1.6", // 1.6.4
"doctrine/doctrine-cache-bundle": "^1.2", // 1.3.0
"symfony/swiftmailer-bundle": "^2.3", // v2.3.11
"symfony/monolog-bundle": "^2.8", // 2.11.1
"symfony/polyfill-apcu": "^1.0", // v1.2.0
"sensio/distribution-bundle": "^5.0", // v5.0.22
"sensio/framework-extra-bundle": "^3.0.2", // v3.0.16
"incenteev/composer-parameter-handler": "^2.0", // v2.1.2
"composer/package-versions-deprecated": "^1.11", // 1.11.99
"knplabs/knp-markdown-bundle": "^1.4", // 1.4.2
"doctrine/doctrine-migrations-bundle": "^1.1" // 1.1.1
},
"require-dev": {
"sensio/generator-bundle": "^3.0", // v3.0.7
"symfony/phpunit-bridge": "^3.0", // v3.1.3
"nelmio/alice": "^2.1", // 2.1.4
"doctrine/doctrine-fixtures-bundle": "^2.3" // 2.3.0
}
}
Dear Ryan, first of all thank you for your awesome tutorials, they help perfectly to get a grip on Symfony!
I have more like architectural question regarding the custom GenusRepository you create:
What, in your words, is the advantage of overwriting the default Repository over just defining findAllPublishedOrderedBySize() as a method of the Genus model?
Thank you in advance and have a great day
Peter