Ruby on Rails: Instantiate associated models with find_by_sql?

find, find-by-sql, include, ruby-on-rails, select

Solution

Have you considered creating model for database view? For example:

Create database view, with your complicated SQL query:

CREATE VIEW production_plan_items AS
    SELECT * FROM [...]
    INNER JOIN [...];

Create model for this view:

# app/view_model.rb
class ViewModel < ActiveRecord::Base
  self.abstract_class = true

  def readonly?
    true
  end   

  def before_destroy
    raise ActiveRecord::ReadOnlyRecord
  end 
end

# app/models/logical/production_plan_item.rb
module Logical
  class ProductionPlanItem < ::ViewModel
  end
end

Use as always, but remember that these records are READ ONLY!

Logical::ProductionPlanItem.where( ... )

If performance still be an issue in the future, you can quite easily convert DB views to materialized views using triggers and stored procedures. This will give your application enormous speed boost, and you don't have to change even one line of Rails code.

Enterprise Rails is highly recommended reading: http://www.amazon.com/Enterprise-Rails-Dan-Chak/dp/0596515200/ref=sr_1_1?ie=UTF8&qid=1293140116&sr=8-1

Problem

Apparently, include and select can't be used simultaneously on a Rails find query, and this has been repeatedly marked as wontfix: http://dev.rubyonrails.org/ticket/7147 http://dev.rubyonrails.org/ticket/5371 This strikes me as very inconvenient, because the times I'd want to use include are exactly the same times I'd want to use select - when every bit of performance counts. Is there any way to work around this and manually generate a combined include-with-select using find_by_sql, or any other method? The trouble is, I'm not aware of any way to emulate the functionality of include, where it instantiates models in memory to hold the included associated models, such that I can enter model1.associated_models and have it not hit the database again.

Original source