Doctrine—select all fields and group by one of them

Question

I am trying to create a query with doctrine, which basically should look like that in SQL:

SELECT p.* FROM some_tabel AS p GROUP BY p.type;

So I ended up with this, since this is closest this I found documented:

private function getAll()
{
    //$this->em => Entity Manager
    return $this->em->createQueryBuilder()
        ->select('p')
        ->from('AppBundle:Robots', 'p')
        ->groupBy('p.type')
        ->getQuery()
        ->getResult(Query::HYDRATE_ARRAY);
}

The error tells me that 'p.type' is not selected.

How can I get that working?

UPDATE

As mentioned in the accepted answer of @Felippe Duarte, it seems that the grouping of Mysql is a kind of wired. Even if I run:

SELECT p.*, max(p.id) FROM some_tabel AS p GROUP BY p.type;

as suggested by many answers, the result is not what I expect, because only the first item of each type is included and no real grouping happens.

The option to order instead of grouping is good, but would still leave some processing to me, so it is redundant in this case.

Finally this code does what I need:

private function groupByType(array $robots)
{
    return array_reduce($robots, 
        function($list, $robot)
        {
            $list[$robot->getType()][] = $robot;
            return $list;
        }, []);
}

Since this is not too much code and there wont be too many »Robots« I decided - following the KISS Paradigm - this to be the solution of choice.

Solution 1

You can't group by a single column, and select other. MySQL has this "bug", that allow users to group by one column while select many columns.

In other words, you have to group by all columns that are not using aggregate functions, like SUM, AVG, MAX, COUNT.

The side-effect of this "feature" of MySQL is that will return the first result for other columns, even if it doesn't represent the truth.

Try this script:

create table test(id int, name char(1), value1 int);

insert into test values(1, 'a', 1);
insert into test values(2, 'b', 1);

select name, value1, max(id) from test group by value1;

"Expected" output:

name: 'b', value1: 1, max(id): 2

Real output:

name: 'a', value1: 1, max(id): 2

Solution 2

Have you tried a "createQuery" like this:

return $this->em->createQuery(
    'SELECT p.* FROM some_tabel AS p GROUP BY p.type'
)->getQuery()->getResult(Query::HYDRATE_ARRAY);

See if that works? Also try removing "Query::HYDRATE_ARRAY" in case that doesn't run.

Solution 3

You need some aggregation, for example counting the entries for each type:

private function getAll()
{
    //$this->em => Entity Manager
    return $this->em->createQueryBuilder()
        ->select('p.type, count(*)')
        ->from('AppBundle:Robots', 'p')
        ->groupBy('p.type')
        ->getQuery()
        ->getResult(Query::HYDRATE_ARRAY);
}