CODEWITHSUNDEEP
sqlruby-on-rails
Xantara
Xantara

Reputation: 25

Nested select in rails (SQL to Rails conversion)

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)

Upvotes: 0

Views: 84

Answers (1)

Pavel Tkackenko
Pavel Tkackenko

Reputation: 953

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

Upvotes: 2

Related Questions