# Single Table Inheritance

**URL:** <https://www.ruby-forum.com/t/single-table-inheritance/80415>\
**Category:** Rails\
**Created:** [December 29, 2005, 3:36pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415 "2005-12-29T15:36:24Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 3:36pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/1 "2005-12-29T15:36:24Z")

</div>

Hi all,

Quick question for STI. With the following setup:

class Company \< AR::Base; end  
class Firm \< Company; end

Why does Firm.find(:all) return all Companies, not just those that have  
type==‘Firm’?

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 5:53pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/2 "2005-12-29T17:53:24Z")

</div>

On 12/29/05, Alex Y. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Hi all,
> 
> Quick question for STI. With the following setup:
> 
> class Company \< AR::Base; end  
> class Firm \< Company; end
> 
> Why does Firm.find(:all) return all Companies, not just those that have  
> type==‘Firm’?

That sounds like a good feature. My naive question would be this: what  
happens if you define:

class BigFirm \< Firm; end

Does Firm:find :all return instances of BigFirm as well, or just  
instances of Firm?

–  
Reginald Braithwaite  
[http://www.braithwaite-lee.com/weblog/](http://www.braithwaite-lee.com/weblog/)

Like all text messages, email exchanged with a gmail account may be  
stored indefinitely and/or read by third parties without the sender or  
receiver’s knowledge or permission. Please do not send any privileged  
or confidential transmission to this account: enquire about secure  
alternatives.

Experience and Treachery will beat Youth and Talent every time.

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 6:02pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/3 "2005-12-29T18:02:27Z")

</div>

Reginald Braithwaite wrote:

> That sounds like a good feature. My naive question would be this: what  
> happens if you define:
> 
> class BigFirm \< Firm; end
> 
> Does Firm:find :all return instances of BigFirm as well, or just  
> instances of Firm?  
> Logically, it should… All BigFirms are Firms, after all.

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 6:11pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/4 "2005-12-29T18:11:39Z")

</div>

On 12/29/05, Alex Y. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Hi all,
> 
> Quick question for STI. With the following setup:
> 
> class Company \< AR::Base; end  
> class Firm \< Company; end
> 
> Why does Firm.find(:all) return all Companies, not just those that have  
> type==‘Firm’?

Having Firm.find(:all) return only entries with type==‘Firm’ would  
mean that there would be some ‘implicit’ conditions attached.  
My wild guess as to why this isn’t done is that it could get very  
tricky to add that into an arbitrary set of conditions.  
Firm.find(:all, :conditions =\> "blah = 1 and thingy = ‘y’ ")  
…AR would need to add “and type = ‘Firm’” to the end of the conditions.  
Now what happens when you’re doing a more complex condition, possibly  
involving group by, having, ‘partition over’, etc… knowing where to  
inject a ‘where’ could be sticky.

That being said, it would be a cool feature. At the moment,  
Company.find\_all\_by\_type(‘Firm’) is probably the shortest answer.

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 6:42pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/5 "2005-12-29T18:42:02Z")

</div>

Wilson B. wrote:

> Having Firm.find(:all) return only entries with type==‘Firm’ would  
> mean that there would be some ‘implicit’ conditions attached.  
> My wild guess as to why this isn’t done is that it could get very  
> tricky to add that into an arbitrary set of conditions.  
> Firm.find(:all, :conditions =\> "blah = 1 and thingy = ‘y’ ")  
> …AR would need to add “and type = ‘Firm’” to the end of the conditions.  
> Now what happens when you’re doing a more complex condition, possibly  
> involving group by, having, ‘partition over’, etc… knowing where to  
> inject a ‘where’ could be sticky.  
> True, but the with\_scope method already does something similar. In  
> fact, looking through the find() method, it looks like it should be  
> doing it anyway, with the construct\_finder\_sql() method… Maybe I  
> messed something else up…

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 7:12pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/6 "2005-12-29T19:12:09Z")

</div>

On 12/29/05, Alex Y. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Reginald Braithwaite wrote:
> 
> > That sounds like a good feature. My naive question would be this: what  
> > happens if you define:
> > 
> > class BigFirm \< Firm; end
> > 
> > Does Firm:find :all return instances of BigFirm as well, or just  
> > instances of Firm?  
> > Logically, it should… All BigFirms are Firms, after all.

That makes things interesting. find\_all\_by\_type(‘Firm’) doesn’t work  
for this case (nor does implicitly adding WHERE type=‘Firm’).

One approach would be to walk the class tree at run time, then  
construct WHERE (type = ‘Firm’ OR type = ‘BigFirm’ or…). In Ruby,  
that’s a challenge because there might be a class that hasn’t been  
seen by the interpreter yet, but there is a row of that type.

The obverse approach would be to scan the table (ugh). You could  
SELECT DISTINCT Company.type and test each type against firm using  
#kind\_of? to get a lits of sub-types.

This kind of thing could certainly be done. But if you don’t feel like  
hacking ActiveRecord, you can probably roll your own domain-specific  
solution using a mixin.

–  
Reginald Braithwaite  
[http://www.braithwaite-lee.com/weblog/](http://www.braithwaite-lee.com/weblog/)

Did you know… The longest word in the English language, according to  
the Oxford English Dictionary, is  
“pneumonoultramicroscopicsilicovolcanoconiosis”.

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 7:30pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/7 "2005-12-29T19:30:15Z")

</div>

Scott W. wrote:

> You’re right, it should use SQL like: SELECT \* FROM firms WHERE  
> (firms.`type` = ‘Firm’ ). I have seen class loading order cause  
> inconsistent behavior with STI queries, though the effect is the  
> opposite of your problem.
> 
> For instance, if I also have MultiNationalFirm \< Firm, and I run  
> Firm.find\_all, I will get Companies, Firms, but not MultiNationalFirms.  
> If I then reference MultiNationalFirm and do the run Firm.find\_all  
> again, I will get Companies, Firms, _and_ MultiNationalFirms.

That’s just… odd. Presumably in an SCGI environment that would then  
be non-deterministic?

> BTW, you have “Company \< AR::Base” in your example. I assume you just  
> abbreviated “ActiveRecord::Base?”  
> That’s right. I nicked those examples from the ActiveRecord docs  
> because they’re a little more intuitive than what I’m actually using,  
> which are CSVInsert, LabelledInsert and TestimonialInsert. Don’t ask  
> 🙂

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 7:21pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/8 "2005-12-29T19:21:14Z")

</div>

You’re right, it should use SQL like: SELECT \* FROM firms WHERE  
(firms.`type` = ‘Firm’ ). I have seen class loading order cause  
inconsistent behavior with STI queries, though the effect is the  
opposite of your problem.

For instance, if I also have MultiNationalFirm \< Firm, and I run  
Firm.find\_all, I will get Companies, Firms, but not  
MultiNationalFirms. If I then reference MultiNationalFirm and do the  
run Firm.find\_all again, I will get Companies, Firms, _and_  
MultiNationalFirms.

BTW, you have “Company \< AR::Base” in your example. I assume you just  
abbreviated “ActiveRecord::Base?”

Scott

---

<div class="post-metadata">

**Author:** ![regularfry](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/45deac/32.png) [@regularfry](https://www.ruby-forum.com/u/regularfry)\
**Post date:** [December 29, 2005, 8:00pm UTC](https://www.ruby-forum.com/t/single-table-inheritance/80415/9 "2005-12-29T20:00:20Z")

</div>

On Dec 29, 2005, at 10:11 AM, Reginald Braithwaite wrote:

> In Ruby,  
> that’s a challenge because there might be a class that hasn’t been  
> seen by the interpreter yet, but there is a row of that type.

Right, that’s the root cause. In my apps, I’ve been able to  
effectively work around this by explicitly referencing all my STI  
classes at start-up (it’s a FXRuby app, not web-based). So, in my  
init script, I say something like:  
MultiNationalFirm  
Firm  
Company

On Dec 29, 2005, at 10:19 AM, Alex Y. wrote:

> That’s just… odd. Presumably in an SCGI environment that would  
> then be non-deterministic?

Not sure. I wouldn’t be surprised if you see the same behavior there,  
though.

> Wilson B. wrote:
> 
> > My wild guess as to why this isn’t done is that it could get very  
> > tricky to add that into an arbitrary set of conditions.  
> > Firm.find(:all, :conditions =\> "blah = 1 and thingy = ‘y’ ")  
> > …AR would need to add “and type = ‘Firm’” to the end of the  
> > conditions.  
> > Now what happens when you’re doing a more complex condition, possibly  
> > involving group by, having, ‘partition over’, etc… knowing where to  
> > inject a ‘where’ could be sticky.

I think you are right, too. When you get to this point, you’re at the  
boundaries of what the ActiveRecord class/pattern can do for you, and  
it’s time to use explicit SQL.

Scitt