ActiveRecord without table for using find_by_sql

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?

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

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.

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

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

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'.