Rails - Feb 2007 - 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?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi~

On Feb 19, 2007, at 1:07 AM, Tom Chappell wrote:
>
> 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
-- ez-NLltGlunAUd/unjJdyJNww@public.gmane.org
-- Engine Yard, Serious Rails Hosting
-- (866) 518-YARD (9273)



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

>   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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

...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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---