Go Pro with Doctrine Queries

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! 

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 :)

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 %}

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!

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?

Hey Shairyar!

If you want, you can just execute the raw SQL in Doctrine: https://knpuniversity.com/screencast/doctrine-queries/raw-sql-queries. 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/screencast/doctrine-queries/select-specific-fields.

Let me know if this makes sense!

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

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/screencast/doctrine-queries/joins-reduce-queries

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

Cheers!

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.

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.

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/screencast/doctrine-queries/joins 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!

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.

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!

Ryan,

Thanks for the inspiration and advice. You rock.

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.

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

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!

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.

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

Cheers!

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

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!

Hello o/

Having a really hard time thinking of this dql query. I posted on SO also [ http://stackoverflow.com/questions/38722752/doctrine-left-join-on-unrelated-entity ]

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()

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!

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

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!

Hi Ryan,

I would like to know your views on mysql 'views' with regards to doctrine.
So, I was doing a join without a foreign key constraint and try to map it back to an entity obj, it wasnt a pleasant experience. And it only occur to me that I could do a view in mysql with that join and then export into a doctrine entity obj. What do u think about this? Also, will looking around, some commentators suggest putting a @onlyRead to the entity but that was a couple of years ago. What do u also think about updating and inserting through the 'view' entity.

Keep on trucking, or in my part of the world, lorrying :)

Hi there!

This is a very interesting question! My views on views? Hmm :). Most of the time, I'm "against" views... but not strongly. For most developers, the clearest path is to create entities, make queries, etc. In fact, most developers have little to no experience with views - so it's an "extra" thing to learn and manage. However, if you're already familiar with using views, I don't think it's a problem at all - it's just another tool that you should use.

However, for better or worse, Doctrine won't help you with your views - it has almost nothing built into it for working with them. That's not a problem - it just means that you will need to do the work to create the views (unlike with entities, where it writes the SQL for you for tables) and also you will need to write all the mapping code if you want your views to be mapped back to some object (maybe someone has done some work to help with this, but I haven't seen it). Of course, once you've done this, I think the setup would be very nice! But in general, just be careful not to overdo it, especially if you choose to create views but NOT map them back to classes (i.e. you just query directly on the views). This can become a bit magic - since the view logic lives *only* on the database, a new developer will be confused by how we're querying these seemingly "non-existent" tables.

Anyways, I'd be curious to hear what you end up doing with this! There are a lot of ways to handle complex queries - figuring them out, writing raw SQL, writing raw SQL and then mapping back to objects with the ResultSetMapping stuff, creating views - just find one that works for you.

Cheers, and keep in lorrying yourself ;).

I 100% agree that it doesnt seem right to not map a view to classes, seems easier if writing raw sql queries to do that in the entity repo. I was trying the resultSetMapping and it does look pretty complicated. My current solution for joins without a constraint is to, raw sql and PDO::FETCH_CLASS, and have my custom class deal with mapping the result back into object. Its rather unwieldily and can be unclear.

So, in short, the problem I'm trying to solve for is to map 'mostly joined unconstraint foreign key tables' to classes, easily.
I was playing around and using https://github.com/mysql-wo.... There is an option for sortTablesAndViews, but seems unsupported. However if i manually create the 'view' entity class, it works fine. So from my perspective, and correct me if I'm wrong, the raw sql query will still need to written and one can do it on the app side, or a database view. I'm leaning towards making a 'view' but many uncertainties, like if indexes work from the 'copied' table. Or maybe its just easier to do it in the app repo and have a simple foreach to map to an entity obj. I could be over thinking it. What do u think?

I think i'm leaning away from view. Separation of concerns does not seem like it fits with the idea of 'views'