CODEWITHSUNDEEP
ruby-on-railspostgresql
Juanse Gimenez
Juanse Gimenez

Reputation: 513

Why does one Rails query return ordered results without an explicit ORDER BY while a similar query does not?

I am experiencing unexpected behavior in my Rails(PostgreSQL) application when retrieving associated records. Two similar queries are returning results in different orders, and I can't figure out why.

I have four models: User,Subscription and PremiumUser PremiumSubscription.

class User < ApplicationRecord
  has_many :subscriptions
end

class Subscription < ApplicationRecord
  belongs_to :user
end

class PremiumUser < ApplicationRecord
  has_many :subscriptions
end

class PremiumSubscription < ApplicationRecord
  belongs_to :user
end

I am running the following queries:

# Query 1
User.find(12345).subscriptions.pluck(:created_at)

# Query 2
PremiumUser.find(67890).premium_subscriptions.pluck(:created_at)

Results:

Additional Context:

My Questions:

Any help or suggestions on how to approach this issue would be greatly appreciated. Thank you!

Run SQL queries, inspect the models, look index on tables

Upvotes: 0

Views: 58

Answers (1)

d r
d r

Reputation: 7891

Without order by it's like life in:
"...My mama always said life was like a box of chocolates. You never know what you're gonna get...." Forrest Gump

From the documentation (ORDER BY)
"After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen."

The ORDER BY clause specifies the sort order:

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

The sort expression(s) can be any expression that would be valid in the query's select list

Upvotes: 4

Related Questions