select working with :include

[Mathieu Jobin]

Hi everyone,

this is about  http://dev.rubyonrails.org/ticket/7147
which has been close as wont fix. it seems like I cannot post comment on this bug anymore...
neither my account (somekool) have the right to create new tickets.

anyhow. I was looking at using both :select and :include in a query but could not as they are mutually exclusive. they I found this ticket close as won't fix.

I understand the initial request is rather difficult to implement without getting important performance drawbacks.
but I think this could be solved by adding a new attributes such as :select_append

so a query would look like this

Store.find :all,
   :select_append => 'sum(books.price * books.stock) as total_inventory',
   :include => [:manager[:name], :address, {:books[:title, :stock, :price] => :author}],
   :joins => 'join owners on stores.owner_id = owners.id',
   :conditions => "addresses.city = 'Sydney'",
   :group => 'stores.id'

ActiveRecord would still manage the whole thing as usual. without having to write a full sql parser and the user would be allowed to specify more columns in the select by just appending them. I beleive this would be a great add-on to active record.

in the same trend, I'd like to be to use :include => with find_by_sql
sometimes, I need to write my own SQL joining multiple tables. but if AR could do its magic with the :include that'd be rather awesome

thanks

On Mon, Nov 9, 2009 at 5:02 AM, Hongli Lai <hon...@phusion.nl> wrote:

On Nov 8, 10:38 pm, Ryan Bigg <radarliste...@gmail.com> wrote:
> The site is down:http://downforeveryoneorjustme.com/rubyonrails.org

Looks up to me.

--
Go is to Western chess what philosophy is to double entry accounting.
- From Shibumi, bestseller by Trevanian

[Ken Collins]

FYI, I think about the time rails went to git, they moved the issue/bug tracking system to Lighthouse. That can be found here:

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails

Ah... more helpful. Here is the blog article that announced that:

http://weblog.rubyonrails.org/2008/4/15/rails-and-family-on-lighthouse

 - Ken

[dasil003]

Appending to the eager loading query select phrase just makes it
easier to blow your leg off (eg. because you don't know what the
aliased field names are, etc) without offering much more capability.
There are already thousands of edge cases like this that ActiveRecord
can't reasonably support because of the parity mismatch to raw SQL.

In these types of situations a much more powerful technique is to
manually hydrate your associations. If a Teacher has_many :students
then you can hydrate like this:

@teachers = Teacher.all
Teacher.send(:preload_associations, @teachers, [:students])

I think this should be exposed in some future version of Rails as an
official API since it's so amazingly useful and gives you a hook into
associations that is not possible any other way, but for now it's a
private method and subject to change.

On Nov 9, 1:40 pm, Mathieu Jobin <somek...@gmail.com> wrote:
> Hi everyone,
>
> this is about  http://dev.rubyonrails.org/ticket/7147
> which has been close as wont fix. it seems like I cannot post comment on
> this bug anymore...
> neither my account (somekool) have the right to create new tickets.
>
> anyhow. I was looking at using both :select and :include in a query but
> could not as they are mutually exclusive. they I found this ticket close as
> won't fix.
>
> I understand the initial request is rather difficult to implement without
> getting important performance drawbacks.
> but I think this could be solved by adding a new attributes such as
> :select_append
>
> so a query would look like this
>
> Store.find :all,

>    :select_*append* => 'sum(books.price * books.stock) as total_inventory',

[Michael Koziarski]

> I think this should be exposed in some future version of Rails as an
> official API since it's so amazingly useful and gives you a hook into
> associations that is not possible any other way, but for now it's a
> private method and subject to change.

I agree on this one, I've been using the preload_associations stuff
quite a bit recently and it's great.

I figure there are two approaches, either do it data-mapper style and
automatically preload whenever any association gets accessed, or have
an explicit api. I'm in favour of the second approach here, perhaps
something like:

@customers = Customer.find_by_sql(some_crazy_thing)
@customers.preload(:projects=>:tasks)
# DB not used from now on
puts @customers.map(&:projects).flatten.map(&:tasks).flatten.map(&:name)

That'd require an array subclass to be returned from all find calls,
but there's already stuff like this in Arel.

--
Cheers

Koz