# Arbitrary Sorting on a Large Cross-Product Multi-Table Join **URL:** \ **Category:** Rails\ **Created:** [February 19, 2007, 10:07am UTC](https://www.ruby-forum.com/t/arbitrary-sorting-on-a-large-cross-product-multi-table-join/86037 "2007-02-19T10:07:57Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![tbcox23](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b9bd4f/32.png) [@tbcox23](https://www.ruby-forum.com/u/tbcox23)\ **Post date:** [February 19, 2007, 10:07am UTC](https://www.ruby-forum.com/t/arbitrary-sorting-on-a-large-cross-product-multi-table-join/86037/1 "2007-02-19T10:07:57Z")
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? ---
**Author:** ![tbcox23](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b9bd4f/32.png) [@tbcox23](https://www.ruby-forum.com/u/tbcox23)\ **Post date:** [February 19, 2007, 8:29pm UTC](https://www.ruby-forum.com/t/arbitrary-sorting-on-a-large-cross-product-multi-table-join/86037/2 "2007-02-19T20:29:41Z")
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 – removed\_email\_address@domain.invalid – Engine Y., Serious Rails Hosting – (866) 518-YARD (9273) ---
**Author:** ![tbcox23](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b9bd4f/32.png) [@tbcox23](https://www.ruby-forum.com/u/tbcox23)\ **Post date:** [February 20, 2007, 5:38am UTC](https://www.ruby-forum.com/t/arbitrary-sorting-on-a-large-cross-product-multi-table-join/86037/3 "2007-02-20T05:38:20Z")
> 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 ---
**Author:** ![tbcox23](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/b9bd4f/32.png) [@tbcox23](https://www.ruby-forum.com/u/tbcox23)\ **Post date:** [February 20, 2007, 6:46am UTC](https://www.ruby-forum.com/t/arbitrary-sorting-on-a-large-cross-product-multi-table-join/86037/4 "2007-02-20T06:46:20Z")
…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.