70

I have the following models

class User
  attr_accesible :first_name, :phone_number
  has_one :user_extension
end

class UserExtension
  attr_accessible :company, :user_id
  belongs_to :user
end

I have table which contains all users. And I need to sort this table by first_name, phone_number, company. With first_name, phone_number I don't have any problems, order works fine, in example

@users = User.order("first_name desc")

, but I also need sort by company and don't know how to do it.

And I can get company name by this way

@user.user_extension.company

So i have troubles with sql, which will gave me all users ordered by company. DB: PostgreSQL. Thanks.

Edit:

I should provide more information about this models.

create_table "user_extensions", :force => true do |t|
  t.integer  "user_id"
  t.string   "company"
end

create_table "users", :force => true do |t|
  t.string   "first_name"
  t.string   "phone_number" 
end

Also, I tried use join

User.joins(:user_extension).order("user_extension.company desc")

and what i get

 User Load (1.6ms)  SELECT "users".* FROM "users" INNER JOIN "user_extensions" ON "user_extensions"."user_id" = "users"."id" ORDER BY user_extension.company desc
PG::Error: ERROR:  relation "user_extensions" does not exist

on

User.includes(:user_extension).order("user_extension.company desc")

i also get

PG::Error: ERROR:  relation "user_extensions" does not exist

Resolved Have problems with my bd, all joins works fine.

wendigooor
  • 813
  • 1
  • 6
  • 8
  • how can i make it work here? http://stackoverflow.com/questions/28451274/how-to-add-sorted-elements-in-the-filter-dropdown-in-activeadmin-rails – inquisitive Feb 11 '15 at 12:31

5 Answers5

133

Try this:

@users = User.includes(:user_extension).order("user_extensions.company desc")

I think you need at order: user_extensions, not user_extension

user2503775
  • 4,267
  • 1
  • 23
  • 41
  • 5
    Indeed. :user_extension is the association, "user_extensions" is the table name and you need to reference the table name in order clauses. – Dan Galipo Mar 14 '14 at 03:15
  • 2
    Nice! I had this exact problem! – B Seven Jun 03 '15 at 22:21
  • 1
    This even works with external tables. I'm pulling data from an external SQL source and this worked!! Thanks – Chris Mendla Sep 08 '16 at 12:47
  • 1
    Why does it need to be `includes` – Jwan622 Nov 01 '16 at 16:27
  • 2
    Great! Now where on earth is this documented in the official docs? – MSC Aug 09 '18 at 11:39
  • 1
    Note: while this works with Postgres, it does not work with Sqlite. – Steve Carey Jan 27 '19 at 01:25
  • @Jwan622 the `includes` here will eliminate the N+1 query. Each iteration through the loop will call `user.user_extensions`, and without `includes`, each of these calls will make an additional trip to the database. Including `includes` here will kind of preload the associations, eliminating the need for all the extra database calls. – eho May 16 '23 at 04:13
24

Merge can make the query smaller/saner-looking, and it benchmarked faster for me in Rails 4.x:

@users = User.joins(:user_extension).merge(UserExtension.order(company: :desc))
Gary S. Weaver
  • 7,966
  • 4
  • 37
  • 61
  • 1
    Great answer, and uses symbols. Thank you very much. – Dan May 23 '17 at 21:26
  • 1
    This is the only solution I could get working for Rails 5.1 Thanks, Gary. – Bill Doughty Mar 23 '18 at 13:21
  • 4
    FYI: We aware that as Rails 5.1.1 this returns duplicated users as is common with joins. However, applying `.distinct` at the end throws an error like the following: `ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list`. – Giovanni Benussi Mar 08 '19 at 17:17
12

@users = User.order("user_extension.company desc") should work fine.

roganartu
  • 593
  • 5
  • 18
  • 1
    I tried your code, but unsuccessful
    User.order("user_extension.company desc") User Load (1.3ms) SELECT "users".* FROM "users" ORDER BY user_extension.company desc PG::Error: ERROR: missing FROM-clause entry for table "user_extension"     – wendigooor Oct 27 '13 at 10:28
  • 2
    How about `User.includes(:user_extension).order("user_extension.company desc")`? – roganartu Oct 27 '13 at 10:29
  • That sounds like the table does not exist. Are you sure the migration has been run? Have you tried resetting and remigrating the DB? – roganartu Oct 27 '13 at 10:37
  • 4
    @Roganartu, just curious: is there an equivalent hash syntax for associations (i.e., an equivalent to `order(company: :desc)`? – honktronic Mar 05 '15 at 20:31
10

Old topic, and perhaps off topic, but I needed this. FWIW:

Order by association field

User.includes(:user_extension).order('user_extensions.company ASC')

Lets make it more interesting, create more associations.

Order by the associations two levels deep.

User.includes(user_extension: :company).order('companies.name ASC')

Order by the associations three levels deep.

User.includes(user_extension: { company: :guilds }).order('guilds.secret_knock ASC')
oma
  • 38,642
  • 11
  • 71
  • 99
  • What if you have multiple companies associated for the same user extension and you want to sort according to the last/first company associated? – doer123456789 Aug 26 '21 at 02:35
  • Post separate Q on SO. Specify the problem clearly, with code example and desired outcome. Feel free to link to the Q in your next comment. – oma Sep 04 '21 at 10:06
  • Thanks a lot. This three level associations helped me from a blocker – Syamlal Dec 21 '22 at 13:17
1

Maybe too late but I ran into a similar issue and this is how I implement it:

scope :sort_by_company, ->{
    joins(:user_extension).order(UserExtension.arel_table[:company].lower.desc)
  }

The code above should be placed within the user model.

Hope it can help!

r4cc00n
  • 1,927
  • 1
  • 8
  • 24