I have a bunch of models defined and have created all the relationships between them. For simplicity lets just say they''re... class Document < ActiveRecord::Base has_and_belongs_to_many :authors end class Author < ActiveRecord::Base has_and_belongs_to_many :documents end I know I can do something like: Document.find :all, :include => authors, conditions => ''authors.id 5'' and Rails will generate SQL similar to SELECT * FROM documents LEFT OUTER JOIN authors_documents ON authors_documents.document_id = documents.id LEFT OUTER JOIN authors ON authors.id = authors_documents.author_id WHERE authors.id = 5 Cool, right? But what if I don''t want all the author information to come back in the query? Do I need to hand code a '':joins'' option or is there another method I''m not aware of which can automatically create the SQL joins for me? BTW I tried using the '':select'' option, but it seems to ignore it. I''ve also tried leaving off the '':include" which obviously throws an exception. Any help would be appreciated, thanks! --~--~---------~--~----~------------~-------~--~----~ 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 specifying the :include you ask rails to fetch the author info. If you just need the document you just need to do a regular find... -- 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 -~----------~----~----~----~------~----~------~--~---
Right, but I only want the documents with specific author criteria... so I need to join with the authors table (via authors_documents). As I understand it, if I use the '':join'' option, I''d need to encode all the join information myself (including the join table). That''s a pain! Especially since I have already encoded how the tables are related in the model. '':include" almost gets me there. It leverages the knowledge I''ve encoded into the model but also gets me a bunch of information I don''t need. I''m real questions is, what ActiveRecord method can I use to leverage all the join information I''ve included in my models, but only get the information I really need. I''m only guessing that this method must exist because it is close to the functionality :include provides. I just don''t know what it''s called. I thought this might work... Document.find :all, :join => :authors, conditions => ''authors.id =5'' but produces... SELECT * FROM documents authors WHERE (author.id = 5) which is stupid. On Aug 2, 1:11 pm, Mikkel Bruun <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> when specifying the :include you ask rails to fetch the author info. If > you just need the document you just need to do a regular find... > -- > Posted viahttp://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 -~----------~----~----~----~------~----~------~--~---
I''m not following you... if you don''t need the information, can''t you just ignore it? I understand wanting to minimize the amount of data that comes back,but is that really necessary? (only you know the answer for that, I''m just playing devil''s advocate.) This is really the 80/20 rule. Rails will do 80% of the cases out there, but if you want to get all customized, then you''re stuck with find_by_sql or something similar. Sounds like a pain? Think of how much other SQL you''re not writing. :) On 8/2/07, vincent fazio <vincent.fazio-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > Right, but I only want the documents with specific author criteria... > so I need to join with the authors table (via authors_documents). > > As I understand it, if I use the '':join'' option, I''d need to encode > all the join information myself (including the join table). That''s a > pain! Especially since I have already encoded how the tables are > related in the model. > > '':include" almost gets me there. It leverages the knowledge I''ve > encoded into the model but also gets me a bunch of information I don''t > need. > > I''m real questions is, what ActiveRecord method can I use to leverage > all the join information I''ve included in my models, but only get the > information I really need. > > I''m only guessing that this method must exist because it is close to > the functionality :include provides. I just don''t know what it''s > called. > > I thought this might work... > Document.find :all, :join => :authors, conditions => ''authors.id > =5'' > but produces... > SELECT * FROM documents authors WHERE (author.id = 5) > which is stupid. > > > On Aug 2, 1:11 pm, Mikkel Bruun <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: > > when specifying the :include you ask rails to fetch the author info. If > > you just need the document you just need to do a regular find... > > -- > > Posted viahttp://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 -~----------~----~----~----~------~----~------~--~---
vincent fazio wrote:> I thought this might work... > Document.find :all, :joins => :authors, conditions => ''authors.id = 5''Yes, I''ve written before about how this would be a nice extension of the joins syntax. It would do all the joins of an include, but only select the base table. If you install the :select with :include patch http://dev.rubyonrails.org/ticket/7147 you can get what you want by Document.find :all, :select => ''documents.*'', :include => :authors, :conditions => ''authors.id = 5'' -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I don''t think that ignoring the information will work. I''ve trivialized the example to try and make it easier to understand, but imagine authors had linked to a gender table and a state table and documents link to a publication table. Let''s say my application has a need to display only document titles written by male authors living in the state of NY and published in "Time Magazine". To pass back all the author (possible address, email, favorite color, etc.) and publication information (address, email, etc) for every author and publication is a real waste (especially if the document appeared in multiple publications or had multiple authors). I just thought that since :include uses reflection to produce the SQL JOIN string, there might be a general purpose method I could leverage to do the same. BTW, I''m not knocking Rails. I have a huge appreciation for all the code I''m not writing. I just thought that the :include functionality had a really nice feature (creating the JOIN string automatically) that I''d like to leverage for something other than brining back all associated information. Does anyone know where/if/how I can get a look at the code that uses the model association information to create the SQL join? I''d like to use this as a starting point to roll my own solution. I mean, I could roll my own from scratch, but no sense in reinventing the wheel, right? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Someone should make that a plugin since it''s been rejected. On 8/2/07, Mark Reginald James <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote:> > > vincent fazio wrote: > > > I thought this might work... > > Document.find :all, :joins => :authors, conditions => ''authors.id = 5'' > > Yes, I''ve written before about how this would be a nice extension > of the joins syntax. It would do all the joins of an include, but > only select the base table. > > If you install the :select with :include patch > http://dev.rubyonrails.org/ticket/7147 > you can get what you want by > > Document.find :all, :select => ''documents.*'', :include => :authors, > :conditions => ''authors.id = 5'' > > -- > We develop, watch us RoR, in numbers too big to ignore. > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks Mark! This should work nicely for me. I''m really new to Rails, can you explain how I go about installing this patch? Will this migrate to production with my code? Thanks again! On Aug 2, 3:38 pm, Mark Reginald James <m...-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote:> vincent fazio wrote: > > I thought this might work... > > Document.find :all, :joins => :authors, conditions => ''authors.id = 5'' > > Yes, I''ve written before about how this would be a nice extension > of the joins syntax. It would do all the joins of an include, but > only select the base table. > > If you install the :select with :include patch > http://dev.rubyonrails.org/ticket/7147 > you can get what you want by > > Document.find :all, :select => ''documents.*'', :include => :authors, > :conditions => ''authors.id = 5'' > > -- > We develop, watch us RoR, in numbers too big to ignore.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Thu, 2007-08-02 at 14:48 -0500, Brian Hogan wrote:> Someone should make that a plugin since it''s been rejected. > >I happen to have written this plugin in the last couple of days. I''m currently writing up docs and tests; before publishing it. It''s called virtual_aliases and extends ActiveRecord : - :select is NOT ignored when using :include you can restrict the columns pulled from the database; ofcourse the attributes you didn''t pull in are not in the instantiated records; this feature can be used mainly to populate table views or report views and ignore fields you don''t need. - it can figure out the :include options by it''s own if a :select is provided The select option can contain a list of virtual aliases and field specs; only these fields are included in the query and the :include is extracted from that ( :select => "invoice.*,invoice.items.*,invoice.customer.name,invoice.customer.creator.name" ) - when specifying :condition and :order or :joins these statement can contain what I call ''virtual aliases'' that are resolved by the plugin to the real aliases used when joining the tables. The problem (in my opinion) with joining (or eager loading) in AR for the moment is that when specifying :conditions or :order you have to use plain SQL , thus you have to know how the joins are aliases to put in the right conditions; it''s horrible when :including > 5 tables with a few duplicates. When changing the order of the :includes the aliases changed -> rewrite conditions. By using virtual aliases this is all done for you. Virtual aliases are based on the associations you make between your classes. In short, the syntax you use to access associated records from your ActiveRecord objects ( eg user.group.name ) can be used to build the SQL ( User.find(:all, :include => :conditions => ["{invoice.customer.id} = ?", 1], :order => "{invoice.items.date''} DESC" ) Virtual aliases are specified between {} and are replaced with the real alias at the moment of SQL generation. regards Bart> On 8/2/07, Mark Reginald James <mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote: > > vincent fazio wrote: > > > I thought this might work... > > Document.find :all, :joins => :authors, conditions => > ''authors.id = 5'' > > Yes, I''ve written before about how this would be a nice > extension > of the joins syntax. It would do all the joins of an include, > but > only select the base table. > > If you install the :select with :include patch > http://dev.rubyonrails.org/ticket/7147 > you can get what you want by > > Document.find :all, :select => ''documents.*'', :include > => :authors, > :conditions => ''authors.id = 5'' > > -- > We develop, watch us RoR, in numbers too big to ignore. > > > > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Couldn''t you just do something like @author = Author.find_by_id(5); @documents = Author.Document.find_all(); ? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I just found a gem called "select_with_include" which allows users to override :include with a custom :select parameter. More info is here: http://assertbuggy.blogspot.com/2007/05/activerecord-select-with-include.html On Aug 2, 3:48 pm, "Brian Hogan" <bpho...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Someone should make that a plugin since it''s been rejected. > > On 8/2/07, Mark Reginald James <m...-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote: > > > > > vincent fazio wrote: > > > > I thought this might work... > > > Document.find :all, :joins => :authors, conditions => ''authors.id = 5'' > > > Yes, I''ve written before about how this would be a nice extension > > of the joins syntax. It would do all the joins of an include, but > > only select the base table. > > > If you install the :select with :include patch > > http://dev.rubyonrails.org/ticket/7147 > > you can get what you want by > > > Document.find :all, :select => ''documents.*'', :include => :authors, > > :conditions => ''authors.id = 5'' > > > -- > > We develop, watch us RoR, in numbers too big to ignore.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Bart Duchesne wrote:> I happen to have written this plugin in the last couple of days. > I''m currently writing up docs and tests; before publishing it. > > It''s called virtual_aliases and extends ActiveRecord : > > - :select is NOT ignored when using :include > you can restrict the columns pulled from the database; ofcourse the > attributes you didn''t pull in are not in the instantiated records; this > feature can be used mainly to populate table views or report views and > ignore fields you don''t need. > > - it can figure out the :include options by it''s own if a :select is > provided > The select option can contain a list of virtual aliases and field specs; > only these fields are included in the query and the :include is > extracted from that ( :select => > "invoice.*,invoice.items.*,invoice.customer.name,invoice.customer.creator.name" ) > > - when specifying :condition and :order or :joins these statement can > contain what I call ''virtual aliases'' that are resolved by the plugin to > the real aliases used when joining the tables. > > The problem (in my opinion) with joining (or eager loading) in AR for > the moment is that when specifying :conditions or :order you have to use > plain SQL , thus you have to know how the joins are aliases to put in > the right conditions; it''s horrible when :including > 5 tables with a > few duplicates. > When changing the order of the :includes the aliases changed -> rewrite > conditions. > > By using virtual aliases this is all done for you. > > Virtual aliases are based on the associations you make between your > classes. > In short, the syntax you use to access associated records from your > ActiveRecord objects ( eg user.group.name ) can be used to build the SQL > ( User.find(:all, :include => :conditions => ["{invoice.customer.id} > = ?", 1], :order => "{invoice.items.date''} DESC" ) > > Virtual aliases are specified between {} and are replaced with the real > alias at the moment of SQL generation.Bart, these virtual aliases in :select and :conditions look really nice. Please announce your plugin to this list. It would be good if your plugin also supported selection of arbitrary SQL expressions as base model attributes. Some of the patches at http://dev.rubyonrails.org/ticket/7147 do this. I can think of a more intuitive syntax for selecting fields on eager-loaded models, but it''d require definition of the [] method for the Symbol class, which is a bit dangerous: :include => [:model1[:attr1, :attr2], {:model2[:attr3] => :model3}] -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
blythe-LRm2bepprt6iMFqZbmIluw@public.gmane.org
2007-Aug-16 06:57 UTC
Re: Anyway to autogenerate joins w/o :include
Hehe. Interesting that so many are writing plugins for this! I threw my suggested patch 7147 up on rubyforge as eload_select (http://rubyforge.org/projects/arperftoolkit/) as a plugin and a gem (though I havent tried it out as a gem). Works pretty well and accepts aliases as well as the * method. I''ve been running off of it for about 6 months now with mysql. ruby script/plugin install http://arperftoolkit.rubyforge.org/svn/trunk/eload_select Has anyone tried out the rparsec gem/plugin? I havent had a chance to check it out, put it looks pretty neat if you want to run really complex select statements. http://cfis.savagexi.com/articles/2007/02/13/select-meets-include-or-a-pitch-for-rparsec # Enhanced to let you apply database functions to columns. These columns will be placed in the attributes of the base class # # ex. Contact.find :first, :include => :account, :select => ''now(), account.name, 123, "drink tequila" as awesome'' # returns a record where the now(), 123, and awesome is placed in # contact[''now()''] => "12007-07-09 20:50:12 '', # contact[''123''] => ''123'', # contact[''awesome''] =>"drink tequila" # contact.account.name will return the account name On 14 ago, 08:19, Mark Reginald James <m...-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> wrote:> Bart Duchesne wrote: > > I happen to have written this plugin in the last couple of days. > > I''m currently writing up docs and tests; before publishing it. > > > It''s called virtual_aliases and extends ActiveRecord : > > > - :select is NOT ignored when using :include > > you can restrict the columns pulled from the database; ofcourse the > > attributes you didn''t pull in are not in the instantiated records; this > > feature can be used mainly to populate table views or report views and > > ignore fields you don''t need. > > > - it can figure out the :include options by it''s own if a :select is > > provided > > The select option can contain a list of virtual aliases and field specs; > > only these fields are included in the query and the :include is > > extracted from that ( :select => > > "invoice.*,invoice.items.*,invoice.customer.name,invoice.customer.creator.name" ) > > > - when specifying :condition and :order or :joins these statement can > > contain what I call ''virtual aliases'' that are resolved by the plugin to > > the real aliases used when joining the tables. > > > The problem (in my opinion) with joining (or eager loading) in AR for > > the moment is that when specifying :conditions or :order you have to use > > plain SQL , thus you have to know how the joins are aliases to put in > > the right conditions; it''s horrible when :including > 5 tables with a > > few duplicates. > > When changing the order of the :includes the aliases changed -> rewrite > > conditions. > > > By using virtual aliases this is all done for you. > > > Virtual aliases are based on the associations you make between your > > classes. > > In short, the syntax you use to access associated records from your > > ActiveRecord objects ( eg user.group.name ) can be used to build the SQL > > ( User.find(:all, :include => :conditions => ["{invoice.customer.id} > > = ?", 1], :order => "{invoice.items.date''} DESC" ) > > > Virtual aliases are specified between {} and are replaced with the real > > alias at the moment of SQL generation. > > Bart, these virtual aliases in :select and :conditions look really nice. > Please announce your plugin to this list. > > It would be good if your plugin also supported selection of arbitrary > SQL expressions as base model attributes. Some of the patches athttp://dev.rubyonrails.org/ticket/7147do this. > > I can think of a more intuitive syntax for selecting fields on > eager-loaded models, but it''d require definition of the [] method > for the Symbol class, which is a bit dangerous: > > :include => [:model1[:attr1, :attr2], {:model2[:attr3] => :model3}] > > -- > We develop, watch us RoR, in numbers too big to ignore.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
blythe-LRm2bepprt6iMFqZbmIluw@public.gmane.org wrote:> Hehe. Interesting that so many are writing plugins for this!Yes, restricting the attributes selected when eager-loading seems to me to be essential in many cases, but the core team is not enthusiastic.> I threw my suggested patch 7147 up on rubyforge as eload_select > (http://rubyforge.org/projects/arperftoolkit/) as a plugin and a gem > (though I havent tried it out as a gem). Works pretty well and accepts > aliases as well as the * method. I''ve been running off of it for about > 6 months now with mysql. > > ruby script/plugin install http://arperftoolkit.rubyforge.org/svn/trunk/eload_selectYour patch on 7147 was the most comprehensive. Good to see that it''s available as a plugin.> Has anyone tried out the rparsec gem/plugin? I havent had a chance to > check it out, put it looks pretty neat if you want to run really > complex select statements.I''ve done another monkey-patch/plugin-init on 7147 that does away with the need to parse the SQL, and provides an easy-to-write-and-read way of selecting attributes on eager-loaded models. Let me know what you think. http://dev.rubyonrails.org/ticket/7147#comment:12 It even autogenerates joins, which makes this post on-topic. -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---