Rails - Sort by Join Table Data

Rails - Sort by join table data

I would suggest to avoid using default_scope, especially on something like price on another table. Every time you'll use that table, join and ordering will take place, possibly giving strange results in complex queries and anyway making your query slower.

There's nothing wrong with a scope of its own, it's simpler and it's even clearer, you can make it as simple as:

scope :ordered, -> { includes(:availabilities).order('availabilities.price') }

PS: Remember to add an index on price; Also see other great answers in here to decide between join/include.

Sorting by joined table value with Rails

Try

FeaturedEvent.includes(:event).order('events.start_datetime').limit(5)

In your order the table name should be the real database table name.

As I guess, the table name must be events

Count and Sort on Join table in Rails 5

The syntax error is telling you that it’s expecting a ) character to close a parenthesis, but reaching an end statement instead.

If you look at this line:

.joins(:favorite_coffeeshops.group(:favorite_coffeeshops.coffeeshop_id)

(which should be line 12 according to your syntax error), then you can see that there are two opening ( characters, but only one closing ).

So to fix the syntax error you just need to add the missing ) for the joins method, before the group method is called, i.e.:

.joins(:favorite_coffeeshops).group(:favorite_coffeeshops.coffeeshop_id)

select from or order by a column from the joins table in Rails using ActiveRecord syntax

There is no any risk in your solution. Even while you are using some string parameters for ActiveRecord - there is nowhere to put SQL injection there.
Just change 'fishes.name' to 'fishes.name AS fish_name' to preserve both names in results.

However if you prefer SQL-string-free solutions (like I do) you can use Arel to white something like this.

Aquarium
  .joins(:fishes)
  .select(:id, :name, :yr_built, Fish.arel_table[:name].as('fish_name'))
  .where(fishes: { name: 'Nemo'})
  .order(Fish.arel_table[:updated_at].desc)

Rails ActiveRecord sort by count of join table associations

Try the following:

@resources = Resouce.select("resources.*, COUNT(votes.id) vote_count")
                    .joins(:votes)
                    .where(language_id: "ruby")
                    .group("resources.id")
                    .order("vote_count DESC")

@resources.each { |r| puts "#{r.whatever}  #{r.vote_count}" }

To include resources with 0 votes, use an outer join. If the example below doesn't work as is you'll have to alter the joins statement to join across the correct relations.

@resources = Resource.select("resources.*, COUNT(votes.id) vote_count")
                     .joins("LEFT OUTER JOIN votes ON votes.votable_id = resources.id AND votes.votable_type = 'Resource'")
                     .where(language_id: "ruby")
                     .group("resources.id")
                     .order("vote_count DESC")

Sorting based on created_at table after joins two table in rails

Your query does look correct, so I'm assuming the issue is as you describe in your edit (the join is happening through the wrong ID). Rails decides how to join two tables based on the relations that you've set up, so if the join isn't as you'd expect then you need to make sure your relations are correct. In your case, you would want something like:

class Micropost < ActiveRecord::Base
  has_many :activities
  has_many :action_activities, class_name: 'Activity', foreign_key: :action_id
end

class Activity < ActiveRecord::Base
  belongs_to :micropost
  belongs_to :action, class_name: 'Micropost'
end

Since Micropost is related to Activity in two different ways, it needs to be told that explicitly. I used the foreign_key option to make sure it knows which is which. With that code, you can see that this generates the expected SQL:

irb> Micropost.joins(:action_activities)
  Micropost Load (2.8ms)  SELECT "microposts".* FROM "microposts" INNER JOIN "activities" ON "activities"."action_id" = "microposts"."id"

The other option is to be explicit in your join, like so:

Micropost.joins("JOIN activities ON activities.action_id = microposts.id")
  .where('activities.user_id=?',id)
  .order('activities.created_at DESC')

Which is useful in situations when you want a more complicated join, mostly. In your case, I think your relations are the real issue.

---

Related Topics