CODEWITHSUNDEEP
postgresqlsymfonydoctrineaggregation
alexandre-mace
alexandre-mace

Reputation: 61

Doctrine : PostgreSQL group by different than select

I have two tables :

user and activityHistory (that has a key to the user)

I am trying to query activityHistory while grouping by user and postgreSQL does not allow me to do that WHILE Sqlite does allow me

        return $qb
        ->select('a.id')
        ->leftJoin('a.user', 'user')
        ->leftJoin(
            ActivityHistory::class,
            'b',
            'WITH',
            'a.id = b.id AND a.createdAt > b.createdAt'
        )
        ->groupBy('user.id')
        ->orderBy( 'a.createdAt','ASC' )
        ->getQuery()->getArrayResult();

I am getting this error only with postgreSQL : Grouping error: 7 ERROR: column "a0_.id" must appear in the GROUP BY clause or be used in an aggregate function

The point is I don't want to groupBy activityHistory id, I only want it to be selected, how can I do ? (I heard about aggregate but I think this works only with functions like SUM etc)

Upvotes: 1

Views: 480

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76787

First of all, let's clarify how aggregation works. Aggregation is the act of grouping by certain field(s) and selecting either those fields or calling aggregation functions and passing ungrouped fields.

You misunderstand how this works - hence the question -, but let me provide you a few very simple examples:

Example 1

Let's consider that there is a town and there are individuals living in that town. Each individual has an eye color, but, if you are wondering what the eye color of the people of the town is, then your question does not make sense, because the group itself does not have an eye color, unless specified otherwise.

Example 2

Let's modify the example above by grouping the people of the town by eye color. Such an aggregation will have a row for all existent eye colors and you can select the eye color, along with the average age, number of individuals, etc. as you like, because you are grouping by eye color

Your example

You have users and they are performing actions. So, an activity is performed by a single user, but a user may perform many activities. So, if you want to group by your user id, then the "eye color" that you are not grouping by here is the history id.

You will have a single record for any user, so you are grouping multiple history items into the same row and after the grouping, asking about the history item's id does not exist.

But, you can use string_agg(some_column, ',') which will take all the values you have and put them all into a string of values separated by comma.

You can explode(',', '$yourvalues) in PHP to convert such a value into an array.

Upvotes: 2

Related Questions