Arbitrary Sorting on a Large Cross-Product Multi-Table Join

Rails
1

I have a RoR app, and I want to display the join of several tables with
tens of thousands of rows (easy in Rails), and then be able to sort on
arbitrary columns when the user clicks on those columns, e.g.:

TableA.col1 TableA.col2 TableB.col1 TableB.col2
TableB.col3 TableC.col1

If it matters, TableA has a many-to-many relationship with TableB (and
so is mediated by a table named tablea_tableb), and TableB and TableC
have a many-to-one relationship.

So: big cross product join, lots of columns from lots of different
tables, and I want to be able to sort on arbitrary columns.

Now, rails has got totally painless facilities for getting an object
from an arbitrary table and then doing invisible joins to march along
these relationships from TableA to TableB, etc., but I don’t see what
I’m supposed to do if I want to impose a big arbitrary sort order on the
big table, i.e., if I want the user to be able to click on any arbitrary
column to sort on that column on the big huge table of everything.

I mean, obviously I could load the whole thing in memory and sort it
that way, but what if I don’t want to do that, because there’s a lot of
data? In the pre-RoR days, I’d just make an arbitrarily-evil SQL call,
creating the big join and sorting it as desired, all in one SQL call.
But what’s the RoR way? How do I display all the data I want, in the
order I want, without abandoning my Model objects, and without loading
the whole mess into memory?

2

Hi~

On Feb 19, 2007, at 1:07 AM, Tom Chappell wrote:

so is mediated by a table named tablea_tableb), and TableB and TableC
big table, i.e., if I want the user to be able to click on any
order I want, without abandoning my Model objects, and without loading
the whole mess into memory?

When it comes time to do something like what you mention then even
in Rails it’s time to drop down and do it in raw sql. With the amount
of joins and records you are talking about pulling them all out and
sorting in ruby is a complete non starter don’t even go down that
route. You need an optimized sql query that returns the exact sorted
result set you want.

Cheers-
– Ezra Z.
– Lead Rails Evangelist
– [email protected]
– Engine Y., Serious Rails Hosting
– (866) 518-YARD (9273)

3

When it comes time to do something like what you mention then even
in Rails it’s time to drop down and do it in raw sql. With the amount
of joins and records you are talking about pulling them all out and
sorting in ruby is a complete non starter don’t even go down that
route. You need an optimized sql query that returns the exact sorted
result set you want.

Ok, thanks very much for the reply – so, just do the SQL, set the
values in the controller directly, cache the ID’s, and if someone wants
to modify a record, then do a find_by_id at that time, I guess.

And other, less gruelling controller-action/views can just do things the
normal rails way.

Yeah, sounds pretty straightforward, after all. Thanks very much for
setting me in the right direction.

-Tom

4

…and just to fill in the last missing piece, for the record (since I
spent some sad minutes puzzling over this), the solution above rasises
the question: what active method object would I use to do the
find_by_sql? Because my results don’t really correspond to any
ActiveRecord subclass. And the ANSWER is…just use a random one, any
one you like, and descend down a level to the ActiveRecord’s connection,
which has a lovely low-level method that will return a hash of
attributes.

As a concrete example, to see a cross of
concepts X concepts_phrases X phrases X languages:

res = Phrase.connection.select_all(“select concept_id, phrase_id, phrases.content as phrase_content, language_id, languages.name as language_name from phrases inner join concepts_phrases on phrases.id=concepts_phrases.phrase_id inner join concepts on concepts.id=concepts_phrases.concept_id inner join languages on phrases.language_id=languages.id order by concept_id, phrases.content, languages.name”)

…yields…(for concept #1 (doggy) and concept #2 (man who sleeps
around)…

=> [
{“concept_id”=>“1”, “phrase_id”=>“3”, “language_id”=>“3”,
“language_name”=>“Español”, “phrase_content”=>“el perro”},

{“concept_id”=>“1”, “phrase_id”=>“2”, “language_id”=>“2”,
“language_name”=>“Français”, “phrase_content”=>“le chien”},

{“concept_id”=>“1”, “phrase_id”=>“1”, “language_id”=>“1”,
“language_name”=>“English”, “phrase_content”=>“the dog”},

{“concept_id”=>“2”, “phrase_id”=>“1”, “language_id”=>“1”,
“language_name”=>“English”, “phrase_content”=>“the dog”},

{“concept_id”=>“2”, “phrase_id”=>“4”, “language_id”=>“1”,
“language_name”=>“English”, “phrase_content”=>“the sexually-profligate
man”}

]

…then I just work from the hash for that controller-action/view, and
then, of course, it’s trivial to change the sort as desired in the above
select_all. Sad, but very workable.