Cphalcon: Paginator\Adapter\QueryBuilder Column not found: 1054 Unknown column in 'having clause'

[BenWil]

When using Paginator\Adapter\QueryBuilder with multiple groups of fields with the same column name, the following behavior occurs

GROUP BY vc.id, se.id, vc.zip, pc.id

becomes

pc.id AS id, vc.zip AS zip

in the totalBuilder count query witch results in an unkonwn column exception.

I think the solution would be to define an alias for all fields when building the groupColumn

[CameronHall]

Hey @BenWil,

I've had a go at reproducing this but to no avail. Can you please provide an example of your Builder.

Here's a test that I've written (bearing in mind it's for Phalcon 4.0.x).

// tests/integration/Paginator/Adapter/QueryBuilderCest.php
public function testIssue13552(IntegrationTester $I)
{
    $this->setDiMysql();
    $modelsManager = $this->getService('modelsManager');

    $builder = $modelsManager->createBuilder()
        ->columns("COUNT(*) as robos_count")
        ->from(['Robots' => Robots::class])
        ->join(RobotsParts::class, "RobotsParts.robots_id = Robots.id", "RobotsParts", "LEFT")
        ->join(RobotsParts::class, "RobotsParts_2.robots_id = Robots.id", "RobotsParts_2", "LEFT")
        ->groupBy('Robots.id, RobotsParts.id, RobotsParts.parts_id, RobotsParts_2.id, RobotsParts_2.parts_id')
        ->having('MAX(Robots.year) > 1970')
    ;

    // var_dump($builder->getQuery()->getSql());

    $paginate = (new QueryBuilder(
        [
            "builder" => $builder,
            "limit"   => 1,
            "page"    => 2
        ]
    ))->paginate();

    $I->assertEquals(4, $paginate->last);
    $I->assertEquals(4, $paginate->total_items);
}

Thank you :)

[BenWil]

The error occurs only with non-aggregate conditions in having clause

Something like that should generate the error at the totalBuilder count query

$builder = $modelsManager->createBuilder()
        ->columns("Robots.id")
        ->from(['Robots' => Robots::class])
        ->join(RobotsParts::class, "RobotsParts.robots_id = Robots.id", "RobotsParts", "LEFT")
        ->join(RobotsParts::class, "RobotsParts_2.robots_id = Robots.id", "RobotsParts_2", "LEFT")
        ->groupBy('Robots.id, RobotsParts.id, RobotsParts.parts_id, RobotsParts_2.id, RobotsParts_2.parts_id')
        ->having('Robots.id> 2')
    ;

[CameronHall]

Thanks for getting back to me so promptly @BenWil. I can reproduce the issue now, I'll see what I can do :)

[BenWil]

@CameronHall I think the problem is not directly on the Paginator, but the query build itself.

For exampel, when you do somthing like this

$builder = $modelsManager->createBuilder()
                ->columns("Robots.id, RobotsParts.id, RobotsParts.parts_id, RobotsParts_2.id, RobotsParts_2.parts_id")
                ->from(['Robots' => Robots::class])
                ->join(RobotsParts::class, "RobotsParts.robots_id = Robots.id", "RobotsParts", "LEFT")
                ->join(RobotsParts::class, "RobotsParts_2.robots_id = Robots.id", "RobotsParts_2", "LEFT")
                ->groupBy('Robots.id, RobotsParts.id, RobotsParts.parts_id, RobotsParts_2.id, RobotsParts_2.parts_id')
                ->having('Robots.id > 1')
            ;

which is done for the total count calculation, it becomes like this in sql

SELECT `RobotsParts_2`.`id` AS `id`, `RobotsParts_2`.`parts_id` AS `parts_id` FROM `phalcon_test`.`Robots` AS `Robots` ...

that results in Unknown column in 'having clause'

[niden]

This has been addressed in https://github.com/phalcon/cphalcon/pull/13653

Thank you @CameronHall and @BenWil