[BUG]: Query builder wrong assumption GROUP BY should contain model columns (Postgres)

[FaimMedia]

Describe the bug Phalcon Query Builder checks if the (full) group by syntax exists in the model, while it should only check the column name or perhaps skip the check entirely. In MySQL you can specify the alias for the group by clause as specify in the column select, for Postgres this isn't allowed.

To Reproduce Steps to reproduce the behavior:

Consider following model:

use Phalcon\Mvc\Model;

final class InvoiceStatistic extends Model
{
    protected string $invoiceDate;
    protected int $count;
    protected int $countryId;

    /**
     * Init
     */
    public function initialize(): void
    {
        parent::setSource('invoice_statistic');
    }

    /**
     * Column map
     */
    public function columnMap(): array
    {
        return [
            'invoice_date' => 'invoiceDate',
            'count'        => 'count',
            'country_id'   => 'countryId',
        ];
    }
}

Create table syntax:

CREATE TABLE "invoice_statistic" (
   "invoice_date" TIMESTAMP NOT NULL,
   "count" int8 NOT NULL,
   "country_id" int2 NOT NULL,
);

Provide minimal script to reproduce the issue

Executing code:

use Phalcon\Mvc\Model\Query\Builder;

$result = (new Builder())
    ->from(InvoiceStatistic::class)
    ->columns([
        "DATE_PART('year', invoiceDate) year",
        'countryId',
        'SUM(count) count',
    ])
    ->groupBy([
        "DATE_PART('year', invoiceDate)",
        'countryId',
    ])
    ->getQuery()
    ->execute();

var_dump($result);

Throws the following exception:

Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Column 'DATE_PART('year', invoiceDate)' doesn't belong to any of the selected models (1), when preparing: SELECT DATE_PART('year', invoiceDate) year, countryId, SUM(count) count FROM [InvoiceStatistic] GROUP BY [DATE_PART('year', invoiceDate)], [countryId] in /www/public/index.php:61

Stack trace:
#0 [internal function]: Phalcon\Mvc\Model\Query->getQualified(Array)
#1 [internal function]: Phalcon\Mvc\Model\Query->getExpression(Array)
#2 [internal function]: Phalcon\Mvc\Model\Query->getGroupClause(Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()
#4 [internal function]: Phalcon\Mvc\Model\Query->parse()
#5 /www/public/index.php(61): Phalcon\Mvc\Model\Query->execute()
#6 {main}
  thrown in /www/public/index.php on line 61

Expected behavior No error, group by should work as expected. Error caused by Exception at: https://github.com/phalcon/cphalcon/blob/v5.7.0/phalcon/Mvc/Model/Query.zep#L3025

Details

• Phalcon version: (php --ri phalcon) 5.6.2 (also applicable in 5.7.0)
• PHP Version: 8.3
• Operating System: Alpine Linux 3.19
• Installation type: Compiling from source
• Zephir version: 0.17.0
• Server: Apache / PHP-FPM
• Other related info (Database, table schema): Postgres 16.1