Forum: Ruby on Rails find_by_sql across multiple tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ian Leitch (Guest)
on 2007-02-21 13:37
(Received via mailing list)
Hi,

I have a rather complex algorithm that needs to return an ordered result
set, the only problem is that the results contain rows for two different
tables, thus meaning if I do Images.find_by_sql, the rows that aren't
Images
will be encapsulated by an Image object. The solution I have in mind is
to
create an ImageIntermediate model, which will return the correct object
for
a row based on the value inside a column. The problem is that
ActiveRecord
will think the object returned by ImageIntermediate will be a new
record. I
can override this with instance_variable_set("@new_record", false), but
that's pretty hacky.

Does anyone have a better solution to this problem?

Cheers
Ian
bandito (Guest)
on 2007-02-21 13:57
(Received via mailing list)
How complex is the query?
select images.* from images...join...join...join will get only images
column
information.
Ian Leitch (Guest)
on 2007-02-21 14:22
(Received via mailing list)
The algorithm calculates the popularity of an asset based on the
frequency
of votes within a certain time frame, much like digg.com. I have a
section
for Images which shows images uploaded by users. I'm adding the ability
for
users to post a link to an image rather than upload one. I want the
result
set to contain both images and image links. I'll need to UNION my
queries
and ORDER them, that's not a problem.. JOINs won't help as I actually
want
the correct corresponding model instance out of the other end, hence the
need for an ImageIntermediate model that will return either an Image or
ImageLink object when I call something like get_obj() on it. I'd just
rather
avoid the hack that get_obj() will have to perform:

def get_obj

    self.is_link? ? obj = ImageLink.new self.attributes : Image.new
self.attributes
    obj.instance_variable_set("@new_record", false) # hack
    obj

end
Luke Ivers (Guest)
on 2007-02-22 15:23
(Received via mailing list)
On Wed, 21 Feb 2007 12:36:53 +0000
"Ian Leitch" <port001@gmail.com> wrote:

> that's pretty hacky.
>
> Does anyone have a better solution to this problem?
>
> Cheers
> Ian
>
You should be able to use STI here... take a look at
http://wiki.rubyonrails.org/rails/pages/SingleTabl...

What this would do is let you do

class ImageReference < ActiveRecord::Base
end

class Image < ImageReference
end

class ImageLink < ImageReference
end

Then, in your database, you have one table for the both of them, with a
field called 'type'.
AR will automatically fill that field with the kind of object it is,
Image or ImageLink.

In this case, you'd have a null column in either case (the blob column
[or file name column, or whatever you're using for the image] on one
side, and the href column for ImageLink), but it still seems as though
it would solve your problem.


--
Luke Ivers <technodolt@gmail.com>
This topic is locked and can not be replied to.