Forum: Ruby on Rails Anyway to autogenerate joins w/o :include

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
83c468c64733d94d0e334bb02e95f8bd?d=identicon&s=25 vincent fazio (Guest)
on 2007-08-02 18:38
(Received via mailing list)
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!
C7669e8b5676f61fdf202230cbcf72d8?d=identicon&s=25 Mikkel Bruun (mikkel)
on 2007-08-02 19:11

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...
83c468c64733d94d0e334bb02e95f8bd?d=identicon&s=25 vincent fazio (Guest)
on 2007-08-02 19:43
(Received via mailing list)
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...@andreas-s.net>
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2007-08-02 21:15
(Received via mailing list)
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.  :)
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2007-08-02 21:40
(Received via mailing list)
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.
83c468c64733d94d0e334bb02e95f8bd?d=identicon&s=25 vincent fazio (Guest)
on 2007-08-02 21:44
(Received via mailing list)
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?
6ef8cb7cd7cd58077f0b57e4fa49a969?d=identicon&s=25 Brian Hogan (Guest)
on 2007-08-02 21:49
(Received via mailing list)
Someone should make that a plugin since it's been rejected.
83c468c64733d94d0e334bb02e95f8bd?d=identicon&s=25 vincent fazio (Guest)
on 2007-08-02 21:51
(Received via mailing list)
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!
0a7131507e84601e4ed9ccf128a9253d?d=identicon&s=25 Bart Duchesne (Guest)
on 2007-08-03 09:09
(Received via mailing list)
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
518db1803036fb7880be61666f50e004?d=identicon&s=25 unimatrixZxero (Guest)
on 2007-08-03 12:17
(Received via mailing list)
Couldn't you just do something like

@author = Author.find_by_id(5);

@documents = Author.Document.find_all();


?
83c468c64733d94d0e334bb02e95f8bd?d=identicon&s=25 vincent fazio (Guest)
on 2007-08-06 18:05
(Received via mailing list)
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/activereco...
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2007-08-14 13:20
(Received via mailing list)
Bart Duchesne wrote:

>
>
> 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.
E795e175b99c767b2b8bee8767ec5090?d=identicon&s=25 Blythe Dunham (blythe)
on 2007-08-16 08:58
(Received via mailing list)
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/selec...

# 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
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2007-08-18 16:48
(Received via mailing list)
blythe@spongecell.com 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_select

Your 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.
This topic is locked and can not be replied to.