ActiveRecord without table for using find_by_sql

rubyonrails-talk
1

Hello,

I have custom queries that joins several tables, so I have written a model class to represent those data. I have extended it from ActiveRecord::Base in order to be able to use 'find_by_sql'. When I call 'find_by_sql' it seems to work fine, in the debugger I can see every record retrieved from DB with their values, but when I try to access those values through some accessors, they don't give me anything.

This is my class:

class ProjectsReport < ActiveRecord::Base

attr_accessor :total_hours, :group_name, :project_type, :request_type

  def initialize(total_hours, group_name, project_type, request_type)     @total_hours = total_hours     @group_name = group_name     @project_type = project_type     @request_type = request_type   end end

If I use another class that has a table associated to call find_by_sql, everything works fine, but that isn't a good design.

# This doesn't work because ProjectsReport doesn't have any table on DB?     @records = ProjectsReport.find_by_sql(create_projects_report_query(start_date, end_date))

# This works but TimeEntry doesn't have anything related with information retrieved by the query     @records = TimeEntry.find_by_sql(create_projects_report_query(start_date, end_date))

What must I add to ProjectsReport to get it working?

2

Hello,

I have custom queries that joins several tables, so I have written a model class to represent those data. I have extended it from ActiveRecord::Base in order to be able to use 'find_by_sql'. When I call 'find_by_sql' it seems to work fine, in the debugger I can see every record retrieved from DB with their values, but when I try to access those values through some accessors, they don't give me anything.

Are you absolutely sure you cannot do it using normal rails relationships and avoid find_by_sql?

Colin

3

Well, I'm not a newbie developer....

The from clause is:

from                users u, users ug, members m, projects p,                issues i, time_entries t,                issue_categories ic, groups_users g, category_attrs att                left join enumerations ptype on ptype.id = att.project_type_id                left join enumerations rtype on rtype.id = att.request_type_id

I prefer to have a model for my results, I think it'll be enhanced with more attibutes when I progress in the development.

4

Well, I'm not a newbie developer....

OK, we were not to know that of course.

The from clause is:

from users u, users ug, members m, projects p, issues i, time_entries t, issue_categories ic, groups_users g, category_attrs att left join enumerations ptype on ptype.id = att.project_type_id left join enumerations rtype on rtype.id = att.request_type_id

I prefer to have a model for my results, I think it'll be enhanced with more attibutes when I progress in the development.

Is there not an issue then that there will be multiple ways of accessing overlapping attributes? I suppose if it is read only that may be less of an issue. You might end up having to replicate business logic though.

Colin

5

Your accessors are trying to read from instance variables, but activerecord attributes aren't stored in individual instance variables. Your initialize method doesn't have the signature expected either. I'd try removing both

Fred

6

I've found the solution:

class ProjectReport < ActiveRecord::Base   def total_hours     @attributes['total_hours']   end

  def group_name     @attributes['group_name']   end

  def project_type     @attributes['project_type']   end

  def request_type     @attributes['request_type']   end end

So I can access results from find_by_sql using something like 'accessors'.