Go Pro with Doctrine Queries

You already know how to query with SQL, so learning how to do complex queries with Doctrine should not give you a headache. In this series, we'll learn the language Doctrine speaks (DQL), as well as the query builder object, how to use complex query features and even how to run good ol' fashioned raw SQL queries with Doctrine. Because hey, sometimes simplicity rules :).


Your Guides

Ryan Weaver Leanna Pelham

Questions? Conversation?

  • 2016-08-15 weaverryan

    Hi Henri!

    Hmm, that's interesting! So, it appears that your query is retrieving a collection of User object, but you expect Post object (looking at the code, I also expect Post objects!). I'm not sure what the problem is, but I would remove different parts of your code until you get the desired result:

    A) Remove the ->select() and the addSelect() to see if this changes things.
    B) I'm not sure what the $this->addUserJoinAndSelect() is doing (I don't see that code here), so it could be part of the problem.

    Let me know what you find out! Mostly, it looks like you're doing just fine - there's something in there that's making "User" the main thing we're querying for, instead of Post... it's odd.

    Cheers!

  • 2016-08-15 Henri Tompodung

    I have 2 entities (Post & User) without relation association, but i create left join relation with query builder.

    When i'm retrieving data in twig, i get an error like this:

    Neither the property "title" nor one of the methods "title()", "gettitle()"/"istitle()" or "__call()" exist and have public access in class "ModelBundle\Entity\User" in FrontBundle:Blog:index.html.twig at line 42

    Repository:
    public function findPostJoin()
    {
    $qb = $this->createQueryBuilder('p');
    $qb->select('p', 'u')
    ->leftJoin('ModelBundle:User', 'u', 'WITH', 'u.id = p.user')
    ->addSelect('u')
    ->addOrderBy('p.id', 'DESC');
    $this->addUserJoinAndSelect($qb);
    $query = $qb->getQuery();
    return $query->getResult();
    }

    Controller:
    public function indexAction(Request $request)
    {
    $em = $this->getDoctrine()->getManager();
    $post = $em->getRepository('ModelBundle:Post')->findPostJoin();
    $category = $em->getRepository('ModelBundle:Postcategory')->findBy(['parent' => null]);

    $paginator = $this->get('knp_paginator');
    $pagination = $paginator->paginate(
    $post, /* query NOT result */
    $request->query->getInt('page', 1), /*page number*/
    10/*limit per page*/
    );

    return $this->render('FrontBundle:Blog:index.html.twig', [
    'pagination' => $pagination,
    'categories' => $category,
    ]);
    }

    Twig:
    {% for article in pagination %}
    {{ article.title }}
    {% endfor %}

  • 2016-08-03 Victor Bocharsky

    Haha, yeah, ORM do these things for you! :)

    P.S. You better avoid using "$this->_em" internal property. It'll help you to find a right place (right repository) for the query and a bit simplified query builder. So the next code:


    $qbt = $this->createQueryBuilder('conference');
    ->addSelect('place')
    ->addSelect('city')
    ->innerJoin('conference.place', 'place')
    ->innerJoin('place.city', 'city')
    ;

    should be in the "ConferenceRepository". Actually, "$this->createQueryBuilder('conference')" do `select()` and `from()` method calls for you ;)

    Cheers!

  • 2016-08-03 Caim Astraea

    Ohh >.< Thank ! Can't believe it was so simple when looking through the perspective of entity objects.
    Think was doing the exact opposite before.

  • 2016-08-03 Victor Bocharsky

    Hey Caim,

    You probably should use next query, because as you pointed: city is a property of place, which (place) is a property of conference. So use this chain in your query:


    $qbt = $this->_em->createQueryBuilder();
    $qbt
    ->select('conference')
    ->addSelect('place')
    ->addSelect('city')
    ->from('AppBundle:Conference', 'conference')
    ->innerJoin('conference.place', 'place')
    ->innerJoin('place.city', 'city')
    ;

    Cheers!

  • 2016-08-03 Caim Astraea

    Hello o/

    Having a really hard time thinking of this dql query. I posted on SO also [ http://stackoverflow.com/quest... ]

    Basically I'm looking for a way to return the results of the leftJoin with an (un-associated ?) entity in the same result set.

    Have a conference entity , a place entity , a city entity.

    place is a property of conference
    /**
    * @var \AppBundle\Entity\Place
    *
    * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Place")
    * @ORM\JoinColumns({
    * @ORM\JoinColumn(name="place_id", referencedColumnName="id", onDelete="SET NULL")
    * })
    */
    private $place;

    and city is a property of place
    /**
    * @var \AppBundle\Entity\City
    *
    * @ORM\ManyToOne(targetEntity="AppBundle\Entity\City")
    * @ORM\JoinColumns({
    * @ORM\JoinColumn(name="city_id", referencedColumnName="id")
    * })
    */
    private $city;

    I'm trying to retrieve the city information also in the conferences query but not even sure if it's possible to do so with dql?

    This is what I've tried

    $qbt = $this->_em->createQueryBuilder();
    $qbt->select('conference','diffusion','speaker','place','confcity')
    ->from('AppBundle:Conference', 'conference')
    ->leftJoin('conference.diffusion', 'diffusion')
    ->leftJoin('conference.speaker','speaker')
    ->leftJoin('conference.place','place')
    ->leftJoin('AppBundle:City','confcity','WITH', 'confcity.id = place.city' );

    return $qbt
    ->getQuery()
    ->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true)
    ->useQueryCache(true)
    ->useResultCache(true,3600)
    ->getArrayResult();

    However the confcity info is retrieved in another array instead of the main one. I'm thinking this is because there is no direct association between the conference and city.
    In the meantime I've rewritten the query in raw SQL and it looks something like this but would really love to learn how to do this in DQL :(

    $conn = $this->getEntityManager()->getConnection();
    $sql = 'SELECT
    c0_.id AS id,
    c0_.startAt AS startat,
    c0_.comment AS comment,
    d1_.id AS diffusion_id,
    d1_.hour AS diffusion_hour,
    s2_.id AS speaker_id,
    c0_.place_id AS place_id,
    c0_.sponsor_id AS sponsor_id,
    c0_.tour_id AS tour_id_8,
    d1_.movie_id AS diffusion_movie_id,
    s2_.contact_id AS speaker_contact_id,
    c6_.name AS ville_name,
    c6_.postal AS ville_post,
    c6_.department as ville_depart
    FROM
    conference c0_
    LEFT JOIN conference_diffusion c3_ ON c0_.id = c3_.conference_id
    LEFT JOIN diffusion d1_ ON d1_.id = c3_.diffusion_id
    LEFT JOIN conference_speaker c4_ ON c0_.id = c4_.conference_id
    LEFT JOIN speaker s2_ ON s2_.id = c4_.speaker_id
    LEFT JOIN place p5_ ON c0_.place_id = p5_.id
    LEFT JOIN city c6_ ON (c6_.id = p5_.city_id)';

    $stmt = $conn->prepare($sql);

    $stmt->execute();

    return $stmt->fetchAll();

    I'm guessing using QueryCache , ResultCache on this would be out of the question but maybe it's possible by createNativeQuery()

  • 2016-08-01 weaverryan

    Awesome, glad it helped! And yep, getTags()->add() will also work - basically the same as the setTags() method :).

    Cheers!

  • 2016-07-31 0x90

    Thanks. :) Yes, that screencast did help. Maybe pretty please update it for Symfony3? In the 2.4 screencast, it was done with getTags()->add(...) and that worked for what I was doing. Off to break ... I mean, write more code.

  • 2016-07-31 weaverryan

    Let me know if that helps :). ManyToMany relationships are both simple, and weird all at the same time. Mostly, it's all about setting the collection onto the collection field. For example, suppose you have a Product and Tag entity, and they are connected via a ManyToMany relationship. To add tags to a product, you would:


    $product = // somehow retrieve 1 product, e.g. query for it

    $tag1 = new Tag('cool stuff');
    $tag2 = new Tag('other stuff');

    $product->setTags(array($tag1, $tag2));

    $em = $this->getDoctrine()->getManager();
    $em->persist($product);
    $em->persist($tag1);
    $em->persist($tag2);
    $em->flush();

    Here, I'm creating 2 brand new Tags and saving those, but you could also query for 2 existing tags. But the point is that you actually pass an array of Tag objects to your Product entity. Also, one additional complication is with the owning versus inverse sides of a relationship (we talk about that in the Symfony2 and Symfony3 tutorials). Basically, in this example, you need the Product.tags property to be the "owning" side of the relationship for this to work. If Tag.products were the owning side, then you would need to set it instead (e.g. $tag->setProducts(...)).

    I hope this helps! Cheers!

  • 2016-07-30 0x90

    Aaaand nevermind. I found the Symfony 2.4 screencasts. I'll watch them to see if they answer my questions (probably do :P).

  • 2016-07-29 0x90

    Hi. I'm running in to a problem with persisting a many-to-many relationship. Could you maybe cover that in a tutorial or give me a direction to go for information? I found one link, but it was for Symfony 1.4 and not terribly detailed. I keep getting errors that the information I'm persisting needs to be of type Collection|array, which I do explode() it to an array, but ... I have no problem with the ManyToOne/OneToMany relationships, JOINs, etc. But this ManyToMany is giving me a headache. I can get the data just fine if I put it in the JoinTable() manually and display it, I just can't seem to be able to persist it.

  • 2016-04-01 weaverryan

    Hey Diego!

    You can only download each video individually now. We made that change so we could update the individual videos more quickly, without needing to re-encode and upload the big final one.

    Cheers!

  • 2016-03-30 Diego Aguiar

    Hello, is there a way to download the entire course instead of video per video ?
    I remember that before was possible

  • 2015-12-17 weaverryan

    Hey Jonathan!

    Thanks for the nice words! About the query: I'm not sure, I often have to play with things to see how they'll come out, but that's usually more because I'm trying to structure the SQL so that I get the exact result I want (e.g. 1 row, 2 columns, instead of 10 rows with 2 columns, like you have here).

    In this case, the SQL that's being generated looks like it's returning multiple rows. To fix that (if it's possible), it's probably a matter of structuring your joins, or something similar. In theory, if you did that correctly, you'd get an array result with just 1 items in it instead of 0 (which I realize still isn't quite perfect - I can't remember now if one of the other query methods will flatten this for you).

    But, I do have one suggestion: use select() instead of addSelect() unless you're purposefully intending to get that extra "stuff" back. By that I mean that you have an avgOne, avgTwo and a "0" index, which is an array of 10 objects. If you use select() instead of addSelect(), you should be able to get back just avgOne and avgTwo.

    I hope that's at least *somewhat* helpful :)

  • 2015-12-17 Jonathan Keen

    Ryan, another question for you if you have some time.

    When I'm doing something along the lines of:

     
    $qb = $this->createQueryBuilder('company')
    ->addSelect(
    'AVG(rs.score) as avgOne,
    AVG(CASE WHEN rs.category = :id THEN rs.score ELSE 0 END) as avgTwo'
    )
    ->leftJoin('company.categories', 'cats')
    ->innerJoin('company.scores', 'rs')
    ->andWhere('cats.id = :id')
    ->setParameter('id', $categoryId)
    ->groupBy('company.id')
    ->getQuery();

    $result = $qb->getArrayResult();

    Is there a more elegant way to return the values of avgOne and avgTwo so that they come back as a "pure" result set rather than "mixed"? I know I can loop over the results to combine them, I'm just wondering if there is something simple I'm missing. Right now I'm getting back:


    array:2 [▼
    0 => array:3 [▼
    0 => array:10 [▶] (myObject)
    "avgOne" => "4.6667"
    "avgTwo" => "4.4444"
    ]
    1 => array:3 [▶]
    ]

    Thanks for all that you do for the community and your customers!

  • 2015-10-20 Jonathan Keen

    Ryan,

    Thanks for the inspiration and advice. You rock.

  • 2015-10-19 weaverryan

    Hey Jonathan!

    I'll add a few more thoughts then :)

    1) Sometimes more queries is more performant than less queries
    2) If you're able to add caching later, then that'll be way more performance than even the best query
    3) Even if this page is hit a lot, performance probably doesn't matter yet. Worry about it later, and optimize using my favorite tool for this: Blackfire.

    Cheers!

  • 2015-10-19 Jonathan Keen

    Ryan,

    Thank you! First off, it's just reassuring to know that even someone as experienced as yourself may get a little hung up sometimes on something. Thanks for taking your time to help.

    Yes, I do feel like I might just skip it and go the straight SQL route. Then again I was also pondering the thought of creating those averages via PHP. After getting the hydrated results back, I could create functions such as "getAverageForCategory", "getAverageForCriteria", etc within the Company Entity and iterate over the Score objects. It wouldn't cause more database calls because I could grab the Score with a join while getting the company, but I would be grabbing a lot of Scores. I do need access to other parts of the Entity as well (name, avatar, other categories it has, etc).

    Heh, I am probably overthinking this a bit, but it's a page that would be hit a lot. I appreciate you letting me pick your brain a bit.

  • 2015-10-19 weaverryan

    Hey Jonathan!

    Thanks for the really nice words!!!! You're awesome!

    Ok, your query indeed sounds difficult, and is probably a decent use-case for skipping Doctrine and going to SQL. Can you do it with the QB or DQL? Of course! But don't kill yourself trying to figure it out. Afterall, you won't be hydrating (i.e. being returned) objects, so you're not really taking advantage of many of the ORM features in this query anyways.

    The best advice I can give on making the query is to go through https://knpuniversity.com/scre... again, and when you join, focus on the specific *property* names on your joins. For example, you'll probably have something like:

        $this->createQueryBuilder('product')
    ->leftJoin('product.scores', 'score')
    // ...

    This is *scores* because you have a OneToMany "scores" property on Product that points to Score. If your property is called something else, rename accordingly. If you haven't mapped this side of the relationship (because the OneToMany side is "inverse", and is optional), then you need to map it. Oh, and overall, with very difficult queries like this, I usually write them in SQL first, and *then* convert to DQL/QB (or maybe I don't care enough and leave it as SQL).

    Good luck!

  • 2015-10-19 Jonathan Keen

    Hey Ryan,

    Loved the course and I look forward to future courses from you. I think you and Leanna have a great way of teaching. Discovering knpuniversity made a world of difference for me. Would love to see some follow ups with more difficult queries.

    One of the things I keep getting stuck on is multiple select statements.

    For instance, I have an entity for a Product that contains a OneToMany relationship on Score. Score has a ManyToOne relationship with Criteria (criteria meaning value, quality, etc) and a ManyToOne with Category. I want to get back multiple companies and get back the results of multiple Scores. For example, an average of overall scores for a Company, an average of scores for a specific Category and Company, an average of scores for a specific Criteria and Company in a single call to the database.

    Is this just something I should do in SQL and skip trying to do in Doctrine? I've been driving myself a little crazy. I'm also guilty of over optimizing as well, I know.

  • 2015-07-01 Shairyar Baig

    Many thanks, I think you should add a feature to your website where developers can ask you queries related to the code they are working on and are stuck. Something like a monthly subscription, i would loveeee that feature and be the first one signing up for it.

  • 2015-06-30 weaverryan

    Hey Shairyar!

    So when you call $this->createQueryBuilder('u') (from within the UserRepository), this *implies* the following:


    $qb->from('AppBundle:User', 'u')

    ->select('u');

    In other words, by default, it selects all data from the User table only, regardless of joins. To grab more data, you have 2 options:

    1) Select the specific fields you want back: $qb->addSelect('u.id as userId, a.id as applicationId, o.id as offerId, o.details'); This will return an associative array, very similar to what you'd expect back from a raw SQL query.

    2) Simply get the User object back and then use your relationships on User to get the other data (something like):


    $applications = $user->getApplications();

    This assumes you've setup the OneToMany inverse side of the relationship (this would be an applications property on the User entity).

    This method will cause extra queries to be made, but this can avoid that with the strategy shown here: https://knpuniversity.com/scre...

    3) Make multiple queries - query for the User object, and then its applications, etc. Always a good fallback option if things get tricky.

    Cheers!

  • 2015-06-30 Shairyar Baig

    Hi,

    Thanks, reason I am running this query is I intend to pull a user's record whose data exist in User and Apply table, Apply table basically holds the applicant's application data and the Offer table hold the status of their application whether it was approved or rejected.

    Now I am able to pull this information using the following query


    $qb = $this->createQueryBuilder('u')
    ->leftJoin('CoreBundle:Apply', 'a', 'WITH', 'a.user = u')
    ->leftJoin('AdminBundle:Offer', 'o', 'WITH', 'o.application = a');


    But the problem with this is it only gives me the user's whose data exist in Offer table/Entity, how can i change the above code to fetch ALL users regardless of their data exist in Offer table or not as long as their data is in Apply table

  • 2015-06-29 weaverryan

    Hey Shairyar!

    If you want, you *can* just execute the raw SQL in Doctrine: https://knpuniversity.com/scre.... But I'm guessing you really want to know how to convert it. The key in converting is this: replace table references with *class* references. And a second key: don't include the ON parts in a JOIN - Doctrine is already aware of these. So, in your case, the true DQL query would look something like this:

    SELECT u FROM AppBundle:User u LEFT JOIN user.applications a LEFT JOIN a.offers o

    In order to LEFT JOIN from user to your Apply entity (pe_apply table), you need to have a User.applications property setup, which will be a OneToMany relationship. That's what the user.applications stuff means.

    But also, what's the goal of the query? If you do the above, it will only return User entities to you. In Doctrine, there is no point in doing JOINs, unless you actually select more columns or use the JOIN'ed tables in WHERE statements (for example). For example, we use a join and then actually select a few columns from the joined tables in this tutorial - you can see it here: https://knpuniversity.com/scre....

    Let me know if this makes sense!

  • 2015-06-29 Shairyar Baig

    I am trying to convert an sql query into a doctrine query and so far I am not having any luck this is the query I have which runs perfectly fine in phpmyadmin


    SELECT * FROM pe_users u
    LEFT JOIN pe_apply a ON u.id = a.user
    LEFT JOIN pe_offer o ON a.id = o.application

    However when I add this query in a custom repository things break down


    namespace Acme\AdminBundle\Repository;

    use Doctrine\ORM\EntityRepository;

    class AdminRepository extends EntityRepository{
    public function findAllAppByStatus(){
    $query = $this->getEntityManager()->createQuery(
    'SELECT * FROM pe_users u
    LEFT JOIN pe_apply a ON u.id = a.user
    LEFT JOIN pe_offer o ON a.id = o.application
    ');
    try {
    return $query->getSingleResult();
    } catch (\Doctrine\ORM\NoResultException $e) {
    return null;
    }
    }
    }

    I even tried


    $query = "SELECT * FROM pe_users u LEFT JOIN pe_apply a ON u.id = a.user LEFT JOIN pe_offer o ON a.id = o.application";
    $q = $this->getEntityManager()->createQuery($query);
    return $q->execute();

    but the error i see is
    [Syntax Error] line 0, col 7: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got '*'

    What am i doing wrong and how can i make it work?