Random postgresql ?

[Noido]

Hi i have create DQL Function Random in postgresql but not work

<?php

namespace App\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class RandomFunction extends FunctionNode
{
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'RANDOM()';
    }
}

dql:
            numeric_functions:
                Random: App\DQL\RandomFunction

$qb->orderBy('RANDOM()');

But got error
Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

i have test

 public function test(){
        return $this->createQueryBuilder('q')->select('q.id')->orderBy('RANDOM()')->setMaxResults(5)->getQuery()->getResult();
    }

And it works but with paginator no

Thanks for your help

[garak]

What is the resulting query?

[Noido]

resulting is error

Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

But like i say with normal query no problem happen only when i use knp paginator

[garak]

I try again: what is the query that gives error?

[Noido]

public function testPaginator(){
return $this->createQueryBuilder('q')->select('q.id')->orderBy('RANDOM()')->getQuery();
}

[garak]

That's not a query. A query is something you can put inside postgresql

[Noido]

 $pagination = $paginator->paginate(
                        $this->testPaginator(),
                        $request->query->getInt('page',1),
                        $this->getParameter('page_number')
                    );

[garak]

https://www.postgresql.org/docs/current/queries-overview.html

[vphant]

@garak I have the same error.

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42P10]: Invalid column reference: 7 ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...me LIKE $2) AND (k0_.deleted_at IS NULL) ORDER BY CASE WHEN ...

A problem appears if I try to paginate such a query

SELECT k0_.id          AS id_0,
       k0_.name        AS name_1,
       k0_.asset_class AS asset_class_2,
       k0_.isin        AS isin_3,
       k0_.created_at  AS created_at_4,
       k0_.updated_at  AS updated_at_5,
       k0_.deleted_at  AS deleted_at_6,
       k0_.file_id     AS file_id_7
FROM kiids k0_
WHERE (k0_.isin LIKE '%Test search%' OR k0_.name LIKE '%Test search%')
  AND (k0_.deleted_at IS NULL)
ORDER BY CASE WHEN (k0_.isin LIKE '%Test search%') THEN 1 WHEN (k0_.name LIKE '%Test search%') THEN 2 ELSE 3 END ASC

But it works OK, if I try same query, but without ORDER BY
Thanks in advance

[garak]

Is the query working if you run it directly in postgres?

[vphant]

Is the query working if you run it directly in postgres?

Yes, it does.

[garak]

Can you provide the difference between the raw query and the query resulting from applying the paginator?

[vphant]

@garak Sure.
If I try to

dump($paginator->getQuery()->getSQL());

In Knp\Component\Pager\Event\Subscriber\Paginate\Doctrine\ORM\QuerySubscriber::items()

I get

SELECT k0_.id          AS id_0,
       k0_.name        AS name_1,
       k0_.asset_class AS asset_class_2,
       k0_.isin        AS isin_3,
       k0_.created_at  AS created_at_4,
       k0_.updated_at  AS updated_at_5,
       k0_.deleted_at  AS deleted_at_6,
       k0_.file_id     AS file_id_7
FROM kiids k0_
WHERE (k0_.isin LIKE '%Test search%' OR k0_.name LIKE '%Test search%')
  AND (k0_.deleted_at IS NULL)
ORDER BY CASE WHEN (k0_.isin LIKE '%Test search%') THEN 1 WHEN (k0_.name LIKE '%Test search%') THEN 2 ELSE 3 END ASC
LIMIT 10

The only difference is that it adds LIMIT 10
But then a script fails on this line

 $event->items = iterator_to_array($paginator);

https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php#L48

But the raw SQL works if I run directly in postgres.

[garak]

That's not the right query, since (based on your error), it must contain a DISTINCT.
Can't you just get the query from Symfony profiler?

[vphant]

@garak Yes, here it is

SELECT DISTINCT k0_.id AS id_0
FROM kiids k0_
WHERE (k0_.isin LIKE '%IE00B%' OR k0_.name LIKE '%IE00B%')
  AND (k0_.deleted_at IS NULL)
ORDER BY CASE WHEN (k0_.isin LIKE '%IE00B%') THEN 1 WHEN (k0_.name LIKE '%IE00B%') THEN 2 ELSE 3 END ASC
LIMIT 10;

[vphant]

So a problem seems to be, that it produces this additional DISTINCT request, but there are additional fields in the ORDER query part.

[garak]

Can you try using manual counting?

[vphant]

@garak Thanks, I think I can use it as a workaround for now.

[garak]

@Noido is it OK for you?