Selecting Specific Fields

We're on a roll! Let's select more fields - like an average of the numberPrinted and the name of the category, all in one query. Yea yea, we already have the Category's name over here - we're querying for the entire Category object. But just stick with me - it makes for a good example.

Head back to FortuneCookieRepository. As I hope you're guessing, SELECTing more fields is just like SQL: add a comma and get them. You could also use the addSelect() function if you want to get fancy.

Add, AVG(fc.numberPrinted) and give that an alias - fortunesAverage. I'm just making that up. Let's also grab - the name of the category that we're using here:

... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
->select('SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage,')
... lines 24 - 26

I don't trust myself. So, var_dump($fortunesPrinted) in the controller with our trusty die statement:

... lines 1 - 35
public function showCategoryAction($id)
... lines 38 - 47
$fortunesPrinted = $this->getDoctrine()
... lines 52 - 56
... lines 58 - 59

Refresh! Uh oh, that's an awesome error:

[Semantical Error] line 0, col 88 near ' FROM': Error 'cat'
is not defined.

Debugging Bad DQL Queries

This is what it looks like when you mess up your DQL. Doctrine does a really good job of lexing and parsing the DQL you give it, so when you make a mistake, it'll give you a pretty detailed error. Here, cat is not defined is because our query references cat with, but I haven't made any JOINs to create a cat alias. cat is not defined.

But real quick - go back to the error. If you scroll down the stack trace a little, you'll eventually see the full query:

SELECT SUM(fc.numberPrinted) as fortunesPrinted,
    AVG(fc.numberPrinted) fortunesAverage,
    FROM AppBundle\Entity\FortuneCookie fc
    WHERE fc.category = :category

For me, sometimes the top error is so small, it doesn't make sense. But if I look at it in context of the full query, it's a lot easier to figure out what mistake I made.

Fixing our error is easy: we need to add a JOIN - this time an innerJoin(). So, innerJoin('fc.category', 'cat'):

... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
return $this->createQueryBuilder('fc')
... lines 18 - 19
->innerJoin('fc.category', 'cat')
->select('SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage,')
... lines 22 - 23
... lines 25 - 27

Why fc.category? Because in the FortuneCookie entity, we have a category property. That's how it knows which relationship we're talking about. So cat is now aliased! Let's try again.

Ooook, another error: NonUniqueResultException. We're still finishing the query with getSingleScalarResult(). But now that we're returning multiple columns of data, it doesn't make sense anymore. The NonUniqueResultException means that you either have this situation, or, more commonly, you're using getOneOrNullResult(), but your query is returning mulitple rows. Watch out for that.

Change the query to getOneOrNullResult(): the query still returns only one row, but multiple columns:

... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
return $this->createQueryBuilder('fc')
... lines 18 - 21
... lines 25 - 27

Refresh! Beautiful! The result is an associative array with fortunesPrinted, fortunesAverage and name keys. And notice, we didn't give the category name an alias in the query - we didn't say as something, so it just used name by default:

... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
return $this->createQueryBuilder('fc')
... lines 18 - 20
->select('SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage,')
... lines 22 - 23
... lines 25 - 27

And hey, I was even a bit messy: for the sum I said as fortunesPrinted but for the average, I just said fortunesAverage with the as. The as is optional - I didn't leave it out on purpose, but hey, good learning moment.

The query is beautiful, so let's actually use our data. In the controller, change the result from $fortunesPrinted to $fortunesData - it's really an array. And below, set $fortunesPrinted to $fortunesData['...']. I'll check my query to remember the alias - it's fortunesPrinted, so I'll use that. I'll do the same thing for the other two fields:

... lines 1 - 35
public function showCategoryAction($id)
... lines 38 - 47
$fortunesData = $this->getDoctrine()
$fortunesPrinted = $fortunesData['fortunesPrinted'];
$averagePrinted = $fortunesData['fortunesAverage'];
$categoryName = $fortunesData['name'];
... lines 54 - 60
... lines 62 - 63

The alias for the average is fortunesAverage. And the last one just uses name. Let's pass these into the template:

... lines 1 - 35
public function showCategoryAction($id)
... lines 38 - 54
return $this->render('fortune/showCategory.html.twig',[
'category' => $category,
'fortunesPrinted' => $fortunesPrinted,
'averagePrinted' => $averagePrinted,
'categoryName' => $categoryName
... lines 62 - 63

And again, I know, the categoryName is redundant - we already have the whole category object. But to prove things, use categoryName in the template. And below, add an extra line after the total and print averagePrinted:

Moment of truth! Woot! 244,829 total, 81,610 average, and the category name still prints out. Doctrine normally queries for objects, and that's great! But remember, nothing stops you from using that select() function to say: no no no: I don't want to select objects anymore, I want to select specific fields.

Leave a comment!

  • 2017-07-21 Diego Aguiar

    Ohh, you are totally right, nice detective work! :)

  • 2017-07-21 Greg

    Diego Aguiar yeah, I got that... your script is wrong :) Video is correct.

  • 2017-07-20 Diego Aguiar

    Hey Greg

    You can specify the "As" part or not, is completely up to you


  • 2017-07-20 Greg

    From the script (the video has it correctly)

    "...I just said fortunesAverage with the as. The as
    is optional - I didn't leave it out on purpose, but hey, good learning moment."

    That first 'with' should be 'without'? Plus your query above actually *does* have the 'as' in it.

  • 2017-04-05 Victor Bocharsky

    Hey Ciprian,

    Yes, it should works fine, but also you can use serializer to make your JSON looks prettier and does not contain extra data which you don't need. But probably it will be a bit complex for you at first time if you haven't worked with serializers yet. Here's a few screencasts which could be interesting for you:

    And yeah, good questions! If you're on PHP 5.5 or higher, better use this new PHP syntax: Story::class which returns the FQCN. Most likely the old "AppBundle:Story" will be deprecated in the future. And the problem that you're requesting an array of entities, not a single entity, that's why you have to use "$story[0]" which is not cool! To fix it, you can call "findOneById()" instead of "findById()" to query only one row from the DB. But better just use find() if you're querying by ID - it's shorter and always return a single entry from the database.


  • 2017-04-04 Ciprian Cucu

    Victor, thank you for your reply, I was trying to get an object with only relevant story and photos info. It seems that Doctrine returns some huge objects that are not easy to use (i.e. not well formed JSON or anything).
    I actually started to go in the same direction:

    $em = $this->getDoctrine()->getManager();
    $story = $em->getRepository('AppBundle:Story') ->findById($no);
    $photos = $story[0]->getPhotos();

    After that I just build my own multidimensional array to have everything nice and neat.

    So far it's working fine, I'm curios thugh if your variant is more optimal in any way:
    you have Story:class instead of AppBundle:Story and find instead of findById...

    Also, curious to why I have $story[0]->getPhotos() - this tells me $story is an array of objects, though I was expecting a single object...

  • 2017-04-03 Victor Bocharsky

    Hey Ciprian,

    If I understand you right, you have OneToMany relationship between Story and Photo entities. If you use Doctrine ORM - you can't specify specific fields in select(). Actually, you can... but you probably don't want to do this. So try to remove it at all and your query should work... but you can significantly simplify it, because you don't need the entity repository at all!

    // StoryController::showAction($storyId)

    $story = $this->getDoctrine()

    // and then call getPhotos() on it to fetch all related photos
    $story->getPhotos(); // Will return ArrayCollection of Photo entities.

    So ORM has superpower because it executes some query for you: you fetch Story entity directly, but then, when you call getPhotos(), ORM executes one more query to fetch all the related photos behind the scene.


  • 2017-04-01 Ciprian Cucu

    great tutorial!
    I have a question: I have an entity Story with an array collection field - photos (a story has many photos).
    I'm trying to make a specific field select to get story info and all its photos

    $db = $this->createQueryBuilder('s')
    ->andWhere(' = :id')
    ->setParameter('id', $no)
    ->select(', s.title, s.abstract,');

    gets me this error:

    [Semantical Error] line 0, col 36 near 'photos FROM AppBundle\Entity\Story': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

    Do I need to make a join, or what is the solution here?

  • 2016-11-10 weaverryan

    I think this is a solid plan :). Custom hydrators are a bit more complex. And honestly, I've never (yet) run into a situation where I needed to create one in a real project.

    Keep up the good work!

  • 2016-11-10 mattxtlm

    Hi Ryan,

    thanks for your great support. I've tried to use the custom hydrator, but don't really know what to put in there. It did work in terms of doing a simple PDO fetch, but not further, I'm afraid. Like this:

    $result = $this->_stmt->fetchAll(\PDO::FETCH_OBJ);

    I read through the ObjectHydrator which ships with Doctrine by default, and hey, that's pretty complicated stuff, at least for me... (esp. that hydrateRowData function)

    Anyways, I'll stick with my "quite not satisfied, but still working solution" for the moment. Maybe you'll have a tutorial about the custom hydrator once. that'll be awesome.


  • 2016-11-08 weaverryan

    Yo Matt!

    Thanks for the nice comment! So, your behavior is "expected"... at least as far as this tutorial goes: we talk about how to hydrate objects and how to hydrate custom fields. What we don't do, is go so far as to hydrate an object *plus* some custom fields. And as far as I can tell (your situation makes great sense, but I've honestly never tried this approach) it's not well-supported. You have 2 options:

    1) Use the ResultSetMapping - but then I think you need to make a raw SQL query, which is a bummer
    2) Use a custom hydrator (

    The custom hydrator is probably the way to go :). There's a third option, which I *have* used... but doesn't quite apply here (it would require an extra key, probably per row, which is lame) - a listener on postLoad (I *have* used this in the past to add an extra property to a single object, every time you query for that object).

    If you do go down any of these routes to set these values, just be careful not make sure you don't access these properties in other situations when you *haven't* actually queried for and populated them. I might even throw an exception in their getter methods if their values are null (meaning, they were never initialized).

    Let me know how it goes!

  • 2016-11-08 mattxtlm

    Great tutorials, they help me a lot! I try to do something similar with count and average, but I can't manage to get the results as object. Doctrine returns something mixed as it seems.

    I've got 2 tables (quotes and ratings). In quotes, there're 2 columns which will be calculated on runtime ("numberOfRatings" and "averageRating") and are therefore not in the DB. Those are just properties in the quotes class with no ORM annotation, just getters and setters. Both tables are joined correctly. However my result looks like this:

    MainController.php on line 121:
    array:3 [▼
    0 => Quotes {#642 ▼
    -id: 206
    -bookCharacter: BookCharacter {#643 ▶}
    -chapter: Chapter {#644 ▶}
    -rating: PersistentCollection {#645 ▶}
    -quote: "Asperiores accusamus nihil repellat vero omnis voluptates id amet. Et suscipit qui recusandae totam nulla quam. Voluptatem cupiditate sed natus debitis voluptas. Laudantium sit repudiandae esse perspiciatis dignissimos error et itaque. Tempora velit porro ut velit soluta explicabo eligendi. Debitis et saepe eum sint dolorem delectus."
    -book: null
    -createdAt: DateTime {#640 ▶}
    -updatedAt: DateTime {#641 ▶}
    -numberOfRatings: null
    -averageRating: null
    "numberOfRatings" => "3"
    "averageRating" => "2.5"

    And my Query like this:

    $quotes = $this->createQueryBuilder('q')
    ->select('q, COUNT( as numberOfRatings, AVG(r.rating) as averageRating')

    How can I tell doctrine to set the properties directly and not returning a mixed array. Btw, my current workaround after the query results in the Controller looks like this, but I'm not satisfied with it:



  • 2016-05-07 weaverryan

    Hi Zef!

    I don't know of a way to hydrate that result directly, but if you use array_map, you can transform it really easily :)

    // FortuneCookieRepository.php
    public function findAllIds()
    $rows = $this->createQueryBuilder('fc')

    return array_map(function($row) {
    return $row['id'];
    }, $rows);

    That'll return an array of all the ids.


  • 2016-05-05 Zef

    Great explanation. It would be interesting to know how to get an array of a attribute (id's) of selected objects, like in ids = 1,2,3,6.