Rails 3. Sort by Associated Model

Rails 3. sort by associated model

Try...

ScheduledCourse.joins(:course).order('course.name')

If this doesn't work, you may need to call .all before your joins condition, like so:

ScheduledCourse.all.joins(:course).order('course.name')

Like luacassus said, this answer can help; I think the syntax in that answer is pre-Arel (ActiveRecord 3), but it'll get the job done. Hope that helps!

EDIT:

As mentioned by @FellowStranger, the correct syntax nowadays seems to be

ScheduledCourse.joins(:course).order('courses.name')

How to sort by associated model within given value?

Firstly I'm presuming by belongs_to :items you mean belongs_to :item (singular) given the presence of the item_id foreign key.

Secondly, to solve your specific query you can use:

Stat.where(:skin_id => skin_id).joins(:item).order("items.rating DESC")

However, if skin_id refers to another model - i.e. Stat belongs_to :skin and Skin has_many :stats then it may make more sense to start from there:

skin = Skin.find(1)
stats = skin.stats.order("rating DESC").includes(:item)

To get the items then just loop through them:

stats = skin.stats.order("rating DESC").includes(:item)

stats.each do |stat|
   stat.item
end

F

Order by associated model with pagination

The query lends itself to a joins rather than an includes. You can do it, sort of, with a single query:

@conversations = current_user.conversations
    .select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
    .left_joins(:conversation_messages).group('conversations.id').order('recent_message_date desc')

This will produce the desired result, but it isn't reliable. For example, if you do:

>> @conversations.count

You'll get an error because ActiveRecord will replace the select clause with something you didn't intend. Calling size produces different SQL but also results in an error.

To do it right, you need to use a subquery. You can do this while staying in the Rails environment by using ActiveRecord's little-known #from method. This allows you to generate a subquery and then operate on that subquery, for example, by ordering it or filtering with a where clause.

>> subquery = current_user.conversations
     .select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
     .left_joins(:conversation_messages)
     .group('conversations.id')

The subquery is now an ActiveRecord association that contains the conversation objects you want, each having a tentative recent_message_date attribute. I say tentative because (as shown before) the attribute exists only if ActiveRecord doesn't decide to mess with our select clause.

To set things in stone, we have to give the subquery a name (you'll want to use the name of the table to avoid problems), and then grab everything from the subquery. Then we can count the resulting records, and we can also reliably sort by or filter from that attribute:

>> @conversations = Conversation.from(subquery, :conversations)
     .order('recent_message_date desc')

To make things tidy, we can create a method or two:

class Conversation < ApplicationRecord
  def self.with_recent_message_date
    select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
         .left_joins(:conversation_messages)
         .group('conversations.id')
  end

  def self.most_recent_first
    order('recent_message_date desc nulls last')
  end
end

Or, if you prefer, you can write the methods as scopes on your class; the result is the same. Now you can write clear, expressive queries:

>> subquery = current_user.conversations.with_recent_message_date
>> @conversations = Conversation.from(subquery, :conversations).most_recent_first

In addition, you can add a filter or whatever else using the new attribute:

>> @conversations.where('recent_message_date > ?', Time.current - 3.days)

and you should be able to reliably paginate this result. I've tried a similar query using will_paginate and it works as expected.

Rails order by in associated model

There are multiple ways to do this:

If you want all calls to that association to be ordered that way, you can specify the ordering when you create the association, as follows:

class Log < ActiveRecord::Base
  has_many :items, :order => "some_col DESC"
end

You could also do this with a named_scope, which would allow that ordering to be easily specified any time Item is accessed:

class Item < ActiveRecord::Base
  named_scope :ordered, :order => "some_col DESC"
end

class Log < ActiveRecord::Base
  has_many :items
end

log.items # uses the default ordering
log.items.ordered # uses the "some_col DESC" ordering

If you always want the items to be ordered in the same way by default, you can use the (new in Rails 2.3) default_scope method, as follows:

class Item < ActiveRecord::Base
  default_scope :order => "some_col DESC"
end

Rails 3 sorting through parent association

Project.joins(:customer).order('customers.name')

Rails sort entries based on attributes of related model

You can order on the associated table by doing a nested join, like so:

@posts = Post.joins(:user => :friendships).order("friendships.friended_at")

How to sort ActiveRecord results by associated model count?

Hey in mysql you can directly used like

@cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc')

You can directly select Top 2 using limit

 @cafe_by_posts = Cafe.joins(:posts).group("posts.cafe_id").order('count(posts.cafe_id) desc').limit(2)

Rails sort by association count and associated field on same model

So there are 2 problems here:

1. You are using .joins on a belongs_to association that may not exist.
2. You are joining the same user table twice (for 2 different associations), causing rails to generate a table alias so that the SQL will be valid. But you aren't grouping on this alias.

Solution:

You'll need a LEFT JOIN which unfortunately isn't as pretty as a standard join. You'll also need to modify your .group clause:

Department.
  select("departments.*, COUNT(users.id) AS members").
  joins(:members, "LEFT JOIN users directors ON departments.director_id = directors.id").
  group("departments.id, directors.last_name").
  order("members, directors.last_name")

---

Related Topics