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.
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.
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.
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.
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?
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.
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:
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.
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.
# Enhanced to let you apply database functions to columns. These
columns will be placed in the attributes of the base class
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.
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.
