CODEWITHSUNDEEP
phpdoctrine-ormsymfony
philipp
philipp

Reputation: 16515

Doctrine—select all fields and group by one of them

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.

Upvotes: 7

Views: 22175

Answers (5)

JSchirrmacher
JSchirrmacher

Reputation: 3362

Regarding the answers to this question which seems not to help and re-read the title of your question, i suppose you think of sorting the data instead of grouping it.

Then use ORDER BY p.type instead. In Doctrine this can be done like that:

 ->orderBy('p.type', 'ASC')

Upvotes: 0

Poiz
Poiz

Reputation: 7617

Here is what worked for me in a similar scenario:

        <?php           

            $conn           = $this->getDoctrine()->getEntityManager()->getConnection();
            $dql            = $conn->createQueryBuilder();

            $dql->select("p")
                ->from('AppBundle:Robots', 'p')
                ->groupBy('p.type');

            $qr             = $em->createQuery($dql);
            $result         = $qr->getResult(AbstractQuery::HYDRATE_ARRAY);

Upvotes: 0

Alvin Bunk
Alvin Bunk

Reputation: 7764

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.

Upvotes: 2

Felippe Duarte
Felippe Duarte

Reputation: 15131

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

Upvotes: 3

JSchirrmacher
JSchirrmacher

Reputation: 3362

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);
}

Upvotes: 1

Related Questions