Nested select in rails (SQL to Rails conversion)

Question

I have this rails logic that uses partial SQL query code. I was wondering if there was a way a better way or a cleaner way to do the same thing (i.e. use rails's methods to replace the SQL code)?

@servers = Server
    .select("*", "(SELECT AVG('reviews'.'average') FROM 'reviews' WHERE 'reviews'.'server_id' = 'servers'.'id') AS s_avg")
    .order("s_avg DESC")
    .paginate(:page => params[:page], :per_page => 25)

Pavel Tkackenko · Accepted Answer · 2014-08-08 08:59:38Z

2

First good thing is to move that code from view or controller to model and wrap it in scope. Moreover, scopes can be chained.

class Server < ActiveRecord::Base
  scope :averaged, -> { where(SQL CODE HERE) }
  scope :expensive, -> { where('price > ?', price) }
  scope :latest, -> { where('created_at > ?', Date.today - 3.days.ago) }
  scope :active, -> { where(active: true) }
end

Only then you can pass and chain it in controller:

@servers = Server.latest.averaged

So, simply try to brake your SQL on several parts, move these parts to model and wrap them with scopes. You can find a lot of useful examples of query methods without pure SQL here: http://guides.rubyonrails.org/active_record_querying.html

edited Aug 8, 2014 at 8:59

answered Aug 8, 2014 at 6:33

Pavel Tkackenko

9537 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user419017 Over a year ago

Note: new scope will conflict with Object.new. I usually go for latest. Apart from that, good suggestion!

Richard Peck Over a year ago

Chained scopes will undoubtedly be the way to go with this one

Collectives™ on Stack Overflow

Nested select in rails (SQL to Rails conversion)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related