Why do these two queries retrieve records in different orders?

Question

I have a Profile that can be published. A profile belongs_to :user and has_many :ratings.

A User has_one :profile, and has_many :ratings.

A Rating belongs_to :profile && belongs_to :user.

These are the schemas for the above models:

Profile.rb:

# == Schema Information
#
# Table name: profiles
#
#  id                      :integer          not null, primary key
#  first_name              :string
#  last_name               :string
#  created_at              :datetime         not null
#  updated_at              :datetime         not null
#  user_id                 :integer

User.rb:

# == Schema Information
#
# Table name: users
#
#  id                     :integer          not null, primary key
#  email                  :string           default(""), not null
#  created_at             :datetime         not null
#  updated_at             :datetime         not null
#  first_name             :string
#  last_name              :string

Rating.rb

# == Schema Information
#
# Table name: ratings
#
#  id         :integer          not null, primary key
#  speed      :integer          default(0)
#  passing    :integer          default(0)
#  tackling   :integer          default(0)
#  dribbling  :integer          default(0)
#  profile_id :integer
#  user_id    :integer
#  created_at :datetime         not null
#  updated_at :datetime         not null
#

Note that coach = User.find(7).

When I do this query:

>p = Profile.published.where(id: coach.ratings.order(passing: :desc).pluck(:profile_id))
  (0.4ms)  SELECT "ratings"."profile_id" FROM "ratings" WHERE "ratings"."user_id" = $1 ORDER BY "ratings"."passing" DESC  [["user_id", 7]]
  Profile Load (1.1ms)  SELECT "profiles".* FROM "profiles" WHERE "profiles"."status" = $1 AND "profiles"."id" IN (52, 14, 24, 29)  [["status", 1]]
> p.ids
=> [24, 14, 52]

Note the order of the profile.ids produced by p.ids.

However, when I just run the inner query by itself, I get a different order:

> coach.ratings.order(passing: :desc).limit(3).pluck(:profile_id)
   (0.8ms)  SELECT  "ratings"."profile_id" FROM "ratings" WHERE "ratings"."user_id" = $1 ORDER BY "ratings"."passing" DESC LIMIT $2  [["user_id", 7], ["LIMIT", 3]]
=> [52, 14, 24]

What is causing the discrepancy and why can't I get the first query to always produce the same results I expect from the latter query?

Edit 1

Note that even if I hardcode the order of the IDs in the first query, it still gives me back the results in the original order:

[19] pry(main)> cids = coach.ratings.order(passing: :desc).limit(3).pluck(:profile_id)
   (0.7ms)  SELECT  "ratings"."profile_id" FROM "ratings" WHERE "ratings"."user_id" = $1 ORDER BY "ratings"."passing" DESC LIMIT $2  [["user_id", 7], ["LIMIT", 3]]
=> [52, 14, 24]
[21] pry(main)> q = Profile.published.where(id: cids)
  Profile Load (0.7ms)  SELECT "profiles".* FROM "profiles" WHERE "profiles"."status" = $1 AND "profiles"."id" IN (52, 14, 24)  [["status", 1]]
[22] pry(main)> q.ids
=> [24, 14, 52]

Edit 2

When I try the following joins query, it returns profiles that violate the published status (aka it returns profiles with status: :unpublished when it shouldn't):

> a = Profile.joins(:ratings).where(status: :published, id: coach.ratings.pluck(:profile_id)).order('ratings.passing DESC')
   (0.4ms)  SELECT "ratings"."profile_id" FROM "ratings" WHERE "ratings"."user_id" = $1  [["user_id", 7]]
  Profile Load (1.8ms)  SELECT "profiles".* FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE "profiles"."status" = $1 AND "profiles"."id" IN (24, 52, 29, 14) ORDER BY ratings.passing DESC  [["status", 1]]

> o = Profile.find(29)
  Profile Load (0.8ms)  SELECT  "profiles".* FROM "profiles" WHERE "profiles"."id" = $1 LIMIT $2  [["id", 29], ["LIMIT", 1]]
[59] pry(main)> o.status
=> "unpublished"
> a.ids
=> [52, 14, 24, 14, 24]

Edit 3

Error from server from above query:

PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ... AND "profiles"."id" IN (24, 52, 29, 14) ORDER BY ratings.pa...
                                                             ^
: SELECT DISTINCT "profiles".* FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE "profiles"."status" = $1 AND "profiles"."id" IN (24, 52, 29, 14) ORDER BY ratings.passing DESC

Edit 3a

When I try to access @profiles from the REPL of the error page, this is what I get:

>> @profiles
!! #<ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ... AND "profiles"."id" IN (24, 52, 29, 14) ORDER BY ratings.pa...
                                                             ^
: SELECT DISTINCT "profiles".* FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE "profiles"."status" = $1 AND "profiles"."id" IN (24, 52, 29, 14) ORDER BY ratings.passing DESC>
>>

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

The reason is that the where query doesn't return records according to the ordering of the input. So the ordering of ids inside where doesn't affect the result. If you want to order the records in p, you should chain order after the where query. Try this:

Profile.published.joins(:ratings).where(id: coach.ratings.pluck(:profile_id)).order('ratings.speed')

Edit by OP

So the reason was correct, but the fix was incorrect.

I finally found the fix from asking this question another way, and I got an answer. But I am adding the answer here for completeness sake:

Profile.published
  .joins(:ratings)
  .where(ratings: { user_id: coach.id } )
  .order('ratings.passing')

edited May 23 '17 at 12:33

Community

1
1

answered Dec 23 '16 at 06:00

EJAg

3,210
2
14
22

How do I do that, given that I am ordering it by an association? i.e. by an `profile.user.rating.speed` or some other value for `rating` which is associated via the `profile.user`? Aka...can you show me an example of what the correctly ordered query may look like, given the code I have shown? – marcamillion Dec 23 '16 at 06:02
If you wish to have an ordering done in SQL, you must relate the Profile table with the other table(s) in which the associated profile_ids can be ordered. In your question above, it seems that `profile.user.rating.speed` might be such a relation? Do you want to order by largest speed, or lowest speed, or is "speed" a keyword/string with specialized ordering? – aks Dec 23 '16 at 06:08
Otherwise, you can do the profile id list ordering in ruby, using a `sort` method on the array of ids, with whatever logic you may wish to put into the sort block. – aks Dec 23 '16 at 06:09
@aks So a `rating`, has many attributes (`speed`, `passing`, `tackling`, `dribbling`) each of which have an `integer` value. I would like to be able to sort profiles from both highest to lowest according to anyone of those attributes. i.e. Show me all the profiles, sorted by speed (highest to lowest, or lowest to highest), or sorted by passing (highest to lowest or lowest to highest), etc. – marcamillion Dec 23 '16 at 06:11
@aks In terms of the ruby logic, I don't mind that....but it seems to me that no matter what order we feed to `Profile.published.where(id: some_ids)`, it will spit out some arbitrary order....or is this what you are suggesting we fix with a ruby `sort` block? – marcamillion Dec 23 '16 at 06:12
The ordering of SQL `SELECT` statements are _by definition_ indeterminate unless an `ORDER BY` clause is applied. Sometimes, it's better to have the database server order the results, sometimes it's better to do the ordering in ruby. It simply depends on your schema, and the ordering criteria. – aks Dec 23 '16 at 06:19
How are `ratings` related to `users` and how are `users` related to `profiles`? This question started out with a selection of `profiles` which you wanted to have ordered. In another comment, I have an example ordering based on a _guess_ of the schema. – aks Dec 23 '16 at 06:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131314/discussion-between-aks-and-marcamillion). – aks Dec 23 '16 at 06:22
I tried this latest suggestion by @EJ2015 and you seem to be on the right track. The only issue now is that I am getting duplicate records. i.e. when I ran this query, this is what I got: `> s.ids => [14, 24, 52, 14, 24] ` Note the double `14` and `24`. How do I strip out the duplicates? – marcamillion Dec 23 '16 at 06:28
Ok even closer. Now it seems that it is violating the scope. i.e. it is returning some profiles with `status: :unpublished`, despite the fact that we are running it on `Profile.published.joins(:ratings)...`. I even tried moving the `.published` to be within the `where(status: :published, id: ...)`, but not even that seems to work. – marcamillion Dec 23 '16 at 06:34
Refresh the question to see it. – marcamillion Dec 23 '16 at 06:44
I see now, `coach.ratings.pluck(:profile_id)` of course will return profile both published and unpublished, because this statement doesn't have any scope. Does the final result still contain unpublished profiles? What's the result of `a.ids`? – EJAg Dec 23 '16 at 06:50
`> a.ids => [52, 14, 24, 14, 24]`. I also put that in the question. – marcamillion Dec 23 '16 at 06:53
So that's correct then. no id 29. Add `distinct` and I think the result should be correct. – EJAg Dec 23 '16 at 06:54
Still getting an error. See updated question for the full error, not my interpretation of it :) – marcamillion Dec 23 '16 at 07:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131319/discussion-between-ej2015-and-marcamillion). – EJAg Dec 23 '16 at 07:14

score 2 · Answer 2 · answered Dec 23 '16 at 06:05

2

The order of the first query does not determine the order of the second query that uses the result of the first as a selector. Selection does not imply ordering.

To paraphrase your example:

coach_rating_profile_ids = coach.ratings.pluck(:profile_id)
profiles = Profile.where(id: coach_rating_profile_ids)

The ids in coach_rating_profile_ids can be in any order and it will not affect the ordering of the selected Profile objects. You can easily experiment with the ordering of coach_rating_profile_ids to see. Example:

profiles = Profile.where(id: coach_rating_profile_ids.reverse)

will give the same result (assuming no other transactions have taken place).

If you want to have a specific order in the set of Profiles selected, you must use an explicit .order() on it (and not on the id selector). Eg:

profiles = Profile.where(id: coach_rating_profile_ids).order(xxxx)

where xxxx is some kind of ordering criteria.

answered Dec 23 '16 at 06:05

aks

2,328
1
16
15

Ok fair enough. So the question is, what is the order criteria I must apply to get what I am trying to get? I want to get the profiles, ranked by speed (an association). So what would the trailer `order` look like? – marcamillion Dec 23 '16 at 06:07
You haven't shared your schema, so this is a guess: `ordered_profiles = Profile.where(id: coach.ratings.pluck(:profile_id)).join('ratings').sort(rating.speed)` – aks Dec 23 '16 at 06:16
Refresh the question. I included the schema info. Not sure if that changes your suggestion, but figured I would update it anyway. – marcamillion Dec 23 '16 at 06:22
I tried your query and it doesn't work. I get the following: `NameError: undefined local variable or method `rating' for main:Object` and `NameError: undefined local variable or method `ratings' for main:Object` when I tried `ratings` rather than `rating`. – marcamillion Dec 23 '16 at 06:23
This related answer may help you with ordering by an associated field: http://stackoverflow.com/questions/1127192/rails-order-by-in-associated-model – aks Dec 23 '16 at 06:27
That's an interesting approach, except I am already using a scope on it....I would rather not use another one if I can help it. Also I want it to be ordered on different columns based on different scenarios. So if the user clicks `speed`, it should order all the profiles by `speed`, but nothing else. – marcamillion Dec 23 '16 at 06:29

Why do these two queries retrieve records in different orders?

2 Answers2