Rails is breaking SQL query when modifying order

Question

Context

We have a Rails app that is retrieving conversations with the following raw SQL query:

SELECT sub.*,
       profiles.status AS interlocutor_status
FROM (
  SELECT DISTINCT ON (conversations.id) conversations.id,
         conversation_preferences.unread_counter,
         left(messages.content, 50) AS last_message,
         posts.id AS post_id,
         messages.created_at AS last_activity_on,
         categories.root_name AS site_name,
         conversation_preferences.state,
         COALESCE(NULLIF(post_owner, 1234567), NULLIF(post_applicant, 1234567)) AS interlocutor_id
  FROM "conversations"
  LEFT OUTER JOIN "conversation_preferences" ON "conversation_preferences"."conversation_id" = "conversations"."id"
  LEFT OUTER JOIN "posts" ON "posts"."id" = "conversations"."post_id"
  LEFT OUTER JOIN "categories" ON "categories"."id" = "posts"."category_id"
  LEFT OUTER JOIN "messages" ON "messages"."conversation_id" = "conversations"."id"
  WHERE (post_applicant = 1234567 OR post_owner = 1234567)
    AND "conversation_preferences"."user_id" = 1234567
  ORDER BY "conversations"."id" ASC, messages.created_at DESC
) sub
LEFT OUTER JOIN users ON interlocutor_id = users.id
LEFT OUTER JOIN profiles ON interlocutor_id = profiles.user_id
WHERE ("profiles"."status" != 'pending')
  AND (last_activity_on >= '2021-01-19 04:40:22.881985')
  AND (state = 'active')
ORDER BY profiles.status, sub.unread_counter DESC, sub.last_activity_on DESC
LIMIT 25

We generate this query using the following ActiveRecord code:

def fetch
  distinct = Conversation.left_outer_joins(:preferences)
                         .left_outer_joins(post: :category)
                         .left_outer_joins(:messages)
                         .where('post_applicant = :id OR post_owner = :id', id: current_user.id)
                         .where(conversation_preferences: { user_id: current_user.id })
                         .select(
                           <<-SQL.squish
                             DISTINCT ON (conversations.id) conversations.id,
                             conversation_preferences.unread_counter,
                             left(messages.content, 50) AS last_message,
                             posts.id AS post_id,
                             messages.created_at AS last_activity_on,
                             categories.root_name AS site_name,
                             conversation_preferences.state,
                             COALESCE(NULLIF(post_owner, #{current_user.id}), NULLIF(post_applicant, #{current_user.id})) AS interlocutor_id
                           SQL
                         )
                         .order(:id, 'messages.created_at DESC')

  Conversation.includes(post: :category)
              .from(distinct, :sub)
              .select('sub.*, profiles.status AS interlocutor_status')
              .joins('LEFT OUTER JOIN users ON interlocutor_id = users.id')
              .joins('LEFT OUTER JOIN profiles ON interlocutor_id = profiles.user_id')
              .where.not('profiles.status' => :pending)
              .order('profiles.status, sub.unread_counter DESC, sub.last_activity_on DESC')
end

Problem

We want to stop ordering by profiles.status. To do this, we naturally removed it from the last order statement:

order('sub.unread_counter DESC, sub.last_activity_on DESC')

That's the problem. Doing that is entirely breaking the generated query, that generate an error which is irrelevant here because we don't want the modified query (note how it is different from the 1st one):

SELECT sub.*,
       profiles.status AS interlocutor_status,
       "conversations"."id" AS t0_r0,
       "conversations"."post_id" AS t0_r1,
       "conversations"."post_owner" AS t0_r2,
       "conversations"."post_applicant" AS t0_r3,
       "conversations"."created_at" AS t0_r4,
       "conversations"."updated_at" AS t0_r5,
       "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1,
       "posts"."description" AS t1_r2,
       "categories"."id" AS t2_r0,
       "categories"."name" AS t2_r1
FROM (
  SELECT DISTINCT ON (conversations.id) conversations.id, 
         conversation_preferences.unread_counter,
         left(messages.content, 50) AS last_message,
         posts.id AS post_id,
         messages.created_at AS last_activity_on,
         categories.root_name AS site_name,
         conversation_preferences.state,
         COALESCE(NULLIF(post_owner, 1234567), NULLIF(post_applicant, 1234567)) AS interlocutor_id
  FROM "conversations"
  LEFT OUTER JOIN "conversation_preferences" ON "conversation_preferences"."conversation_id" = "conversations"."id"
  LEFT OUTER JOIN "posts" ON "posts"."id" = "conversations"."post_id"
  LEFT OUTER JOIN "categories" ON "categories"."id" = "posts"."category_id"
  LEFT OUTER JOIN "messages" ON "messages"."conversation_id" = "conversations"."id" 
  WHERE (post_applicant = 1234567 OR post_owner = 1234567)
    AND "conversation_preferences"."user_id" = 1234567
  ORDER BY "conversations"."id" ASC, messages.created_at DESC
) sub
LEFT OUTER JOIN "posts" ON "posts"."id" = "conversations"."post_id"
LEFT OUTER JOIN "categories" ON "categories"."id" = "posts"."category_id"
LEFT OUTER JOIN users ON interlocutor_id = users.id
LEFT OUTER JOIN profiles ON interlocutor_id = profiles.user_id
WHERE ("profiles"."status" != 'pending')
  AND (last_activity_on >= '2021-01-19 05:04:06.084499')
  AND (state = 'active')
ORDER BY sub.unread_counter DESC, sub.last_activity_on DESC
LIMIT 25

I know without a bit of context it'll be hard to help us but if someone knows why ActiveRecord is changing the query after trying to just remove profiles.status from the order statement, that would be awesome. Thanks in advance

NOTE: modifying the 1st raw SQL directly (from our postgres client) does works. The issue is not the first query, but maybe how ActiveRecord is handling it

Answer 1

Finally found a way to make it work using preload instead of includes. We wanted to avoid having seperate queries to load posts and categories but since performance is not affected by it, we don't mind it.

Here is how it look like:

Conversation.preload(post: :category)
            .from(distinct, :sub)
            .select('sub.*, profiles.status AS interlocutor_status')
            .joins('LEFT OUTER JOIN users ON interlocutor_id = users.id')
            .joins('LEFT OUTER JOIN profiles ON interlocutor_id = profiles.user_id')
            .where.not('profiles.status' => :pending)
            .order('sub.unread_counter DESC, sub.last_activity_on DESC')

Which generates 3 queries:

-- Query 1
SELECT sub.*, profiles.status AS interlocutor_status
FROM (
  SELECT DISTINCT
  ....

-- Query 2
SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (............)

-- Query 3
SELECT "categories".* FROM "categories" WHERE "categories"."id" IN (..........)

Thanks to everyone for the help in comments (max and Sebastian Palma)