PostgreSQL Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

Question

I'm encountering an error while working with a PostgreSQL database and Objection.js ORM. The specific error message I'm getting is:

Error Message:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in the select list

I have a query that joins multiple tables and includes a DISTINCT clause as well as an ORDER BY clause. Here's the problematic part of the code:

const query = models.User.query()
    .join('team_members', 'users.id', 'team_members.user_id')
    .join('teams', 'team_members.team_id', 'teams.id')
    .leftJoin('channel_members', 'users.id', 'channel_members.user_id')
    .distinct('users.*')
    .orderByRaw('LOWER(users.username) ASC');

The issue seems to be related to the combination of DISTINCT and ORDER BY in the query. The error message indicates that the columns used in the ORDER BY clause must also be present in the SELECT list when using DISTINCT.

I'm not sure how to resolve this error while keeping the desired functionality of my query intact. I need the query to return distinct users based on certain criteria and order the results by the lowercase usernames.

Answer 1

Include the column used in the ORDER BY clause in the SELECT list to fix this error. Create a subquery with the desired transformation to accomplish this. Try this code, in this query the column used in the ORDER BY clause is included in the SELECT list and utilize it to sort the results using lowercase usernames while still keeping the distinct user records.

const query = models.User.query()
  .join('team_members', 'users.id', 'team_members.user_id')
  .join('teams', 'team_members.team_id', 'teams.id')
  .leftJoin('channel_members', 'users.id', 'channel_members.user_id')
  .select('users.*', models.raw('LOWER(users.username) as lower_username'))
  .distinct('users.id')
  .orderBy('lower_username', 'ASC');

Hope it works :)