Activerecord Query, Order by Association, Last of Has_Many

ActiveRecord query, order by association, last of has_many

Problem is you are trying to order the parent by child's attributes, so your solution will work only when their orders have the same direction.

The way to work with it is using aggregate function for the children's attribute like this:

# ascending
User
  .joins('LEFT JOIN communications ON communications.user_id = users.id')
  .group('users.id')
  .order('MAX(communications.created_at) ASC')

# descending
User
  .joins('LEFT JOIN communications ON communications.user_id = users.id')
  .group('users.id')
  .order('MAX(communications.created_at) DESC')

Rails: Order a model by the last element of a has_many association

I would probably be preferential to the solution mentioned by phoet of adding an attribute to User such as last_message_posted_at and using touch: true to update that on message creation. That simplifies the query that has to be performed when you need to pull your list of users. This also allows a much more readable (IMO) chain for your application:

@users = User.all.order(:last_message_posted_at)
=> "SELECT \"users\".* FROM \"users\" ORDER BY \"users\".\"last_message_posted_at\" ASC"

This also allows you to add a nice and simple scope to your User model

scope: :by_recent_message, ->{ order(:last_message_posted_at) }
User.by_recent_message.limit(5)

It also depends when and how often this @users scope is being used. Adding a few ms to the message post time is preferable, to me, than a complicated SQL query each time the list of users is pulled.

-- Edit for those who aren't familiar with the touch syntax --

Documentation for touch: http://apidock.com/rails/v4.2.1/ActiveRecord/Persistence/touch

class User < ActiveRecord::Base
  has_many :messages
end
class Message < ActiveRecord::Base
  belongs_to :user, touch: true
end

And then make my query (to the user with most recent last message):

@user =  User.includes(:messages).order(updated_at: :desc )

Rails order by results count of has_many association

If you expect to use this query frequently, I suggest you to use built-in counter_cache

# Job Model
class Job < ActiveRecord::Base
  belongs_to :company, counter_cache: true
  # ...
end

# add a migration
add_column :company, :jobs_count, :integer, default: 0

# Company model
class Company < ActiveRecord::Base
  scope :featured, order('jobs_count DESC')
  # ...
end

and then use it like

@featured_company = Company.featured

Sort based on last has_many record in Rails

assuming the gpas timestamp is updated_at

Student.joins(:gpas).order('gpas.updated_at DESC').uniq

To include students without gpas

#references is rails 4; works in rails 3 without it
Student.includes(:gpas).order('gpas.updated_at DESC').references(:gpas).uniq

---

if you dont like the distinct that uniq creates, you can use some raw sql

Student.find_by_sql("SELECT students.* FROM students
INNER JOIN gpas ON gpas.student_id = students.id
LEFT OUTER JOIN gpas AS future ON future.student_id = gpas.student_id
  AND future.updated_at > gpas.updated_at
WHERE future.id IS NULL ORDER BY gpas.updated_at DESC")
# or some pretty raw arel
gpa_table = Gpa.arel_table
on = Arel::Nodes::On.new(
  Arel::Nodes::Equality.new(gpa_table[:student_id], Student.arel_table[:id])
)
inner_join = Arel::Nodes::InnerJoin.new(gpa_table, on)
future_gpa_table = Gpa.arel_table.alias("future")
on = Arel::Nodes::On.new(
  Arel::Nodes::Equality.new(future_gpa_table[:student_id], gpa_table[:student_id]).\
  and(future_gpa_table[:updated_at].gt(gpa_table[:updated_at])
  )
)
outer_join = Arel::Nodes::OuterJoin.new(future_gpa_table, on)
# get results
Student.joins(inner_join).joins(outer_join).where("future.id IS NULL").\
order('gpas.updated_at DESC')

rails 5 has_many through order on through table

I was able to get it working using

class DoctorProfile
    has_many :specialties, -> { order 'doctor_specialties.ordinal' }, through: :doctor_specialties

end

class DoctorSpecialty < ApplicationRecord
  belongs_to :doctor_profile
  belongs_to :specialty

  default_scope { order('ordinal ASC') }

end

Rails order based on association doesn't work properly

I found an alternative to fix this issue :

# Movie.rb
scope :old,       -> {includes(:movie_countries).order('movie_countries.release_date ASC') }
scope :recent,    -> { old.reverse }

SOLUTION

Thanks to@PavelMikhailyuk 's answer I found the solution

# Movie.rb
scope :recent,    -> (iso_code = 'FR') { includes(:movie_countries).where(movie_countries: {iso_code: iso_code}).order('movie_countries.release_date DESC') }

The problem was that my movie had 2 movie_countries.

[#<MovieCountry id: 8, country_id: 2, movie_id: 5, release_date: "2018-12-10", iso_code: "GB", created_at: "2018-10-23 15:23:09", updated_at: "2018-10-23 15:23:09">, 
 #<MovieCountry id: 7, country_id: 1, movie_id: 5, release_date: "2018-10-24", iso_code: "FR", created_at: "2018-10-23 15:23:09", updated_at: "2018-10-23 15:23:09">]

I must search the movie_countries for a specific country AND THEN sort them.

How do I automatically sort a has_many relationship in Rails?

You can specify the sort order for the bare collection with an option on has_many itself:

class Article < ActiveRecord::Base 
  has_many :comments, :order => 'created_at DESC'
end 
class Comment < ActiveRecord::Base 
  belongs_to :article 
end

Or, if you want a simple, non-database method of sorting, use sort_by:

article.comments.sort_by &:created_at

Collecting this with the ActiveRecord-added methods of ordering:

article.comments.find(:all, :order => 'created_at DESC')
article.comments.all(:order => 'created_at DESC')

Your mileage may vary: the performance characteristics of the above solutions will change wildly depending on how you're fetching data in the first place and which Ruby you're using to run your app.

---

Related Topics