[PostgreSQL] distinct() and inRandomOrder() issue

[ddzobov]

• Laravel Version: latest
• PHP Version: PHP7
• Database Driver & Version: PostgreSQL

Description:

In PostgreSQL DISTINCT and ORDER BY RANDOM() can not be in one query

[Illuminate\Database\QueryException] SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

https://www.postgresql.org/message-id/5070.1286027570@sss.pgh.pa.us

[themsaid]

Please share the Laravel code that lead to this query.

[ddzobov]

php artisan tinker
>>> $user = App\User::select('id')->distinct()->inRandomOrder()->get();

Illuminate\Database\QueryException with message 'SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct "id" from "users" order by RANDOM()
^ (SQL: select distinct "id" from "users" order by RANDOM())'

I can try to submit PR for that, but later

[themsaid]

If it's not supported, why use it?

[ddzobov]

It is not supported by PostgreSQL but supported by MySQL like issue found by me #19989

[themsaid]

yes but Laravel doesn't use such query internally, so there's nothing we can change. Is there anywhere where Laravel uses distinct with inRandomOrder internally that you know of?

[ddzobov]

No, Laravel not using this query internally, but it can break applications that depends on no database dependency of Eloquent after migration from MySQL to PostgreSQL.
I think that fixing it internally will be the right way.

[themsaid]

How can we fix it internally?

If you run App\User::select('id')->distinct()->inRandomOrder()->get(); you know what you want :) If the driver doesn't support this then you have to change it.

[ddzobov]

in inRandomOrder() make wrapper for it like that:

select * from (original_query) ORDER BY RANDOM()

[themsaid]

I don't believe we can force change the query like this, can't see anywhere in the source where we force change the structure of a query. Not all drivers support all types of queries, if the driver you picked doesn't support something I don't think there's anything we can do.

Anyways if you have suggested change please feel free to open a PR, but I don't think restructuring the query into two queries like this can be easily done looking at how the query builder is structured.

Closing this for now since it's not really a framework issue. But thanks for taking the time to answer my questions :)