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


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?

  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 Zygmuntowicz-- Lead Rails Evangelist -- -- Engine Yard, Serious Rails Hosting -- (866) 518-YARD (9273)