Selecting Specific Fields

i think this work because you use mysql SGDB with ONLY_FULL_GROUP_BY mode deactivated, otherwise for others SGBG or when mysql ONLY_FULL_GROUP_BY is activated it will not work and you have to add a group by.
Generally all columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY.

`
public function countNumberPrintedForCategory(Category $category)

{
    $conn = $this->getEntityManager()
        ->getConnection();

    $sql = '
        SELECT SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name
        FROM fortune_cookie fc
        INNER JOIN category cat ON cat.id = fc.category_id
        WHERE fc.category_id = :category
   GROUP BY cat.name
        ';
    $stmt = $conn->prepare($sql);
    $stmt->execute(array('category' => $category->getId()));

    return $stmt->fetch();

    return $this->createQueryBuilder('fc')
        ->andWhere('fc.category = :category')
        ->setParameter('category', $category)
        ->innerJoin('fc.category', 'cat')
        ->select('SUM(fc.numberPrinted) as fortunesPrinted, AVG(fc.numberPrinted) as fortunesAverage, cat.name')
		->groupBy('cat.name')
        ->getQuery()
        ->getOneOrNullResult();
}`

Hey Helmi

Thanks for sharing your findings. You are right, that kind of query requires a group by clause when ONLY_FULL_GROUP_BY is activated. I believe that's a default since MySql 5.6

Cheers!

Hi,
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('s.id = :id')
            ->setParameter('id', $no)
            ->select('s.id, s.title, s.abstract, s.photos');

gets me this error:
<blockquote>[Semantical Error] line 0, col 36 near 'photos FROM AppBundle\Entity\Story': Error: Invalid PathExpression. Must be a StateFieldPathExpression.</blockquote>
Do I need to make a join, or what is the solution here?
Thanks!

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()
    ->getRepository(Story::class)
    ->find($storyId);

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

Cheers!

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

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:
https://knpuniversity.com/s...
https://knpuniversity.com/s...

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.

Cheers!

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.

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')
        ->select('fc.id')
        ->getQuery()
        ->execute();

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

That'll return an array of all the ids.

Cheers!

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 (https://techpunch.co.uk/dev...

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!

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!

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.

Hey Greg B.

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

Cheers!

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

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