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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs