# Find\_by\_sql across multiple tables

**URL:** <https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319>\
**Category:** Rails\
**Created:** [February 21, 2007, 1:37pm UTC](https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319 "2007-02-21T13:37:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![elliterate](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/9de053/32.png) [@elliterate](https://www.ruby-forum.com/u/elliterate)\
**Post date:** [February 21, 2007, 1:37pm UTC](https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319/1 "2007-02-21T13:37:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![elliterate](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/9de053/32.png) [@elliterate](https://www.ruby-forum.com/u/elliterate)\
**Post date:** [February 21, 2007, 1:57pm UTC](https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319/2 "2007-02-21T13:57:34Z")

</div>

How complex is the query?  
select images.\* from images…join…join…join will get only images  
column  
information.

---

<div class="post-metadata">

**Author:** ![elliterate](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/9de053/32.png) [@elliterate](https://www.ruby-forum.com/u/elliterate)\
**Post date:** [February 21, 2007, 2:22pm UTC](https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319/3 "2007-02-21T14:22:37Z")

</div>

The algorithm calculates the popularity of an asset based on the  
frequency  
of votes within a certain time frame, much like [digg.com](http://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

---

<div class="post-metadata">

**Author:** ![elliterate](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/9de053/32.png) [@elliterate](https://www.ruby-forum.com/u/elliterate)\
**Post date:** [February 22, 2007, 3:23pm UTC](https://www.ruby-forum.com/t/find-by-sql-across-multiple-tables/86319/4 "2007-02-22T15:23:41Z")

</div>

On Wed, 21 Feb 2007 12:36:53 +0000  
“Ian L.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/SingleTableInheritance](http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance)

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 I. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)