Buy

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.

What is the Repository?

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:

101 lines src/AppBundle/Controller/GenusController.php
... 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!

Creating your own Repository

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:

11 lines src/AppBundle/Repository/GenusRepository.php
... 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:

95 lines src/AppBundle/Entity/Genus.php
... 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.

Adding a Custom Query

Add a new public function called findAllPublishedOrderedBySize():

23 lines src/AppBundle/Repository/GenusRepository.php
... 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'):

23 lines src/AppBundle/Repository/GenusRepository.php
... 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.

WHERE

To add a WHERE clause, chain ->andWhere() with genus.isPublished = :isPublished:

23 lines src/AppBundle/Repository/GenusRepository.php
... 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):

23 lines src/AppBundle/Repository/GenusRepository.php
... 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.

ORDER BY

To order... well you can kind of guess. Add ->orderBy() with genus.speciesCount and DESC:

23 lines src/AppBundle/Repository/GenusRepository.php
... lines 1 - 14
return $this->createQueryBuilder('genus')
->andWhere('genus.isPublished = :isPublished')
->setParameter('isPublished', true)
->orderBy('genus.speciesCount', 'DESC')
... lines 19 - 23

Query, done!

Finishing the Query

To execute the query, add ->getQuery() and then ->execute():

23 lines src/AppBundle/Repository/GenusRepository.php
... 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[]:

23 lines src/AppBundle/Repository/GenusRepository.php
... lines 1 - 4
use AppBundle\Entity\Genus;
... lines 6 - 7
class GenusRepository extends EntityRepository
{
/**
* @return Genus[]
*/
public function findAllPublishedOrderedBySize()
{
... lines 15 - 20
}
}

Using the Custom Query

Our hard work is done - using the new method is simple. Replace findAll() with findAllPublishedOrderedBySize():

100 lines src/AppBundle/Controller/GenusController.php
... 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!

Leave a comment!

  • 2016-10-24 weaverryan

    Hey Terry!

    You got it then :). @ORM\Entity(..) ties the repository to the entity, and then @ORM\Table ties the entity to the table (and so, both put together effectively tie the repository to the table).

    And I *love* the way you said:

    > the query already knows to select from the GENUS Entity's table

    The "weird" (by design) thing about Doctrine sometimes is that it wants you to think in terms of entities and not worry about the database. So, your way of thinking about it here is perfect.

    Cheers!

  • 2016-10-24 Terry Caliendo

    Got it. I was focused on the table name. I knew the @ORM\Entity(...) line tied the two together (but forgot to explicitly state it in my question), and was ultimately verifying that the @ORM\Table(...) statement ultimately tied both the Entity and Repository to a particular table name.

    But at the Repository level, I hear from your answer that I don't need to worry about the table name, just that the Repository is tied to the Entity by the @ORM\Entity(...) statement and the Entity is in turn tied to the table by the @ORM\Table(...) statement.

    Thus, when you said "the query already knows to select from that table", I shouldn't have been thinking about a particular table but interpreted it as "the query already knows to select from the GENUS Entity's table (whatever name it may have).

    Thanks for the clarification.

  • 2016-10-24 weaverryan

    Hey Terry!

    You are thinking about the problem *perfectly* and are *so* close to being correct :). It knows because of the @ORM\Entity annotation on Genus (not the \Table annotation):

    /**
    * @ORM\Entity(repositoryClass="AppBundle\Repository\GenusRepository")
    * ...
    */
    class Genus

    We add this right after creating the repository class (https://knpuniversity.com/scre.... So, the GenusRepository class can live anywhere, and we connect that repository class to our Genus entity with that annotation. If you take away that annotation, Doctrine will fallback and use the generic, core EntityRepository class (which is what it's been using up until this point).

    Let me know if that makes sense! I like that you're questioning it :).

    Cheers!

  • 2016-10-22 Terry Caliendo

    When you create the Repository and Subsequent GenusRepository folders, you say the names are not important. Then when you are in the findAllPublishedOrderedBySize method within GenusRepository and call the createQueryBuilder('genus') you say that because "we are in the GenusRepository, the query already knows to select from that table". How does it know? It can't be because of the repository name, because you said the name was arbitrary. There's nothing else in that file that would indicate the 'genus' table. Thus, I assume it knows because of the @ORM statement at the top of the Genus Entity @ORM\Table(name="genus"). Am I correct? If so (or even if not) I could use a bit more clarification.

  • 2016-07-08 Sergio Medina

    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!

  • 2016-07-07 weaverryan

    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/scree...

    Any reason why you can't do it that way?

    Cheers!

  • 2016-07-07 Sergio Medina

    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.

  • 2016-06-22 Victor Bocharsky

    You're welcome! Keep learning :)

  • 2016-06-22 Dominik

    thank you, Victor :)

  • 2016-06-21 Victor Bocharsky

    Hey, Dominik!

    Ah, it was a bad cache. I fixed it!

    Thank you for reporting it!

  • 2016-06-21 Dominik

    Hello Ryan!
    I got a problem - can't see any script on this page. Can you fix this, please?

  • 2016-06-01 Raphael Schubert

    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

  • 2016-06-01 weaverryan

    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/scree....

    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!

  • 2016-06-01 Raphael Schubert

    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!

  • 2016-05-18 weaverryan

    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/scree.... 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!

  • 2016-05-14 Kosta Andonovski

    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!

  • 2016-05-05 weaverryan

    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!

  • 2016-05-05 Kosta Andonovski

    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!

  • 2016-04-15 Neandher Carlos

    Thank you for the answer '')

  • 2016-04-15 weaverryan

    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!

  • 2016-04-15 Neandher Carlos

    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.