Rails - Jun 2011 - Rails 3.1 : Complex database query with Arel 2

Hi,

I''ve search for documentations on queries with Arel 2 but there is
pretty nothing and all I''ve found is for Arel 1 so I''ve run
into
NoMethodError: undefined method `[]'' for
#<Arel::SelectManager:0x00000003f1b820> errors with join syntax.

My query is barely simple but involve nesting queries. I have 4 tables.
Assets, Deposits, Orders and OrderLines and I want to
get asset name, the stock, the quantity that is ordered and the
difference between the two previous.

I''ve manage to do it in pure SQL. (tested on postgres)

select assets.name, stock, ordered, (stock - ordered) as available
from
assets
join
    (
    select asset_id, sum(quantity) as stock
    from "deposits"
    where validated = true
    group by asset_id
    ) as stocks
on assets.id = stocks.asset_id
join
    (
    select asset_id, sum(quantity) as ordered
    from order_lines
    join orders on orders.id = order_id
    where state in (''IN_PREPARATION'',
''WAIT_DELIVERY'')
    group by asset_id
    ) as ordereds
on assets.id = ordereds.asset_id

I expect to retrieve a simple hash with results so I''ve tried to do
that
directly with Arel but is there a way to do that with ActiveRecord ?  

Thanks in advance.

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.