ActiveRecord without table for using find_by_sql

Rails
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

2010/11/26 Eduardo Yez P. [email protected]:

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

2010/11/26 Eduardo Yez P. [email protected]:

Well, I’m not a newbie developer…

OK, we were not to know that of course.

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

4

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.

5

On Nov 26, 11:41am, Eduardo Yez P. [email protected]
wrote:

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.

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