Anyway to autogenerate joins w/o :include


#1

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!


#2

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…


#3

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 B. removed_email_address@domain.invalid


#4

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


#5

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.


#6

Someone should make that a plugin since it’s been rejected.


#7

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?


#8

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!


#9

On Thu, 2007-08-02 at 14:48 -0500, Brian H. 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


#10

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


#11

Couldn’t you just do something like

@author = Author.find_by_id(5);

@documents = Author.Document.find_all();

?


#12

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


#13

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


#14

removed_email_address@domain.invalid 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.