Combining and querying multiple tables efficiently (STI vs MTI)

In my Rails project, I have several kinds of content types, let's say "Article," "Interview," and "Review."

In planning my project, I decided to go with separate tables for all of this (multiple tables vs single table), namely because while these content types do share some fields in common (title, teaser, etc), they also have fields unique to each content type.

I managed to get all of this working fine in my project, but hit some roadblocks doing so. Namely let's say I want to show all of these content types TOGETHER in order of their publish date (like on a categories page) and I just need to display the fields (title, teaser, etc) that they all have in common...this is problematic.

I went with the has_many_polymorphs plugin for a little while, but it was never officially updated for Rails 3 and I was using a forked branch 'hacked' together for R3. Anyway, it cause problems and spat out weird error messages now and then, and after one of my updates broke the plugin, I decided it was a good idea to abandon it.

Then I looked for a plugin that could perform SQL like UNION joins, but that plugin hasn't been updated since 2009 so I passed that by.

What I ended up doing was making three different queries for each table, then using Ruby to combine the results. (@articles + @reviews +, etc) Not efficient, but I need it to work (and these areas will likely be cached eventually).

However, it 'would' be easier if I could just make queries on a single table, and there is still time to restructure my schema to roll out with the most efficient way to do this. So I'm now wondering if STI is indeed the way to go, and if so, how would I get away with this with different columns on both sides?

UPDATE

I've been thinking heavily about this...namely how I can use STI but have some unique fields for each type. Let's say I have a 'Content' table where I have my shared columns like title, teaser, etc...then I have separate tables like Article, Review where those tables would have my unique columns. Couldn't I technically make those nested attributes of the Content table, or actually the Content table's specific types?

UPDATE 2

So apparently according to what I researched, STI is a bad idea if you need different columns on the different tables, or if you need different c开发者_开发问答ontrollers for the different content types. So maybe sticking with multiple tables and polymorphs is the right way. Just not sure I'm doing it as effectively as I should be.

UPDATE 3

Thinking mu has the right idea and this is one of those times where the query is so complex that it requires find_by_sql, I created a 'Content' model and made the following call...

@recent_content = Content.find_by_sql("SELECT * FROM articles UNION SELECT * FROM reviews ORDER BY published_at DESC")

And it sort of works...it's returning all types with a single query indeed. The remaining trouble is that because I'm calling ActiveRecord on Content, the following don't work in my collection partial returning recent content...

<%= link_to content.title, content %>

Namely the link path, "content_path doesn't exist." It's tricky because each result returns as a "Content" object, rather than what kind of object it really is. That's because I'm starting the above query with 'Content' of course, but I have to put "something" there to make the activerecord query work. Have to figure out how to return the appropriate path/object type for the different content types, /articles/, /reviews/, etc...

Querying them separately is not as bad as you think. If they have different columns and you need to show different columns in your view, I don't think you can combine them using sql union. For performance issue, you can use cache or optimize your table.

I recently forked a promising project to implement multiple table inheritance and class inheritance in Rails. I have spent a few days subjecting it to rapid development, fixes, commenting and documentation and have re-released it as CITIER Class Inheritance and Table Inheritance Embeddings for Rails.

I think it would do exactly what you need.

Consider giving it a look: http://peterhamilton.github.com/citier

I am finding it so useful! I would (by the way) welcome any help for the community in issues and testing, code cleanup etc! I know this is something many people would appreciate.

Please make sure you update regularly however because like I said, it has been improving/evolving by the day.