Good morning, is there any example of `groupBy`-queries? I tried the automatically generated functio

U016ZPC5480

Good morning, is there any example of groupBy-queries? I tried the automatically generated functions, but they have no effect on the result of the query. E.g:

public function findUniqueImportReports(): ObjectCollection
{
    $query = $this->getFactory()->createImportReportRepository()
        ->groupByType()
        ->groupByErrorMessage();

    return $query->find();
}

Adding count() or max() - selects unfortunately does not change the result either.

sprymiker

If you use PostgreSQL, Propel adds all columns in GROUP BY statement. So obviously it won’t work as expected.

sprymiker

By SQL standard, all non-aggregating columns must be added in GROUP BY.
MySQL does not follow it and does not require it. PGSQL does.

sprymiker

And it is no good way to do it with Propel.

However, you can make the query like this:

$query = $this->getFactory()->createImportReportRepository()
        ->addSelfSelectColumns()
        ->clearSelectColumns()
        ->withColumn(sprintf('COUNT(*)'))
        ->select(['type', 'error_message'])
        ->groupByType()
        ->groupByErrorMessage();

U016ZPC5480

Perfect, thanks for the explanation.