My query is this:
DB[:expense_projects___p].where(:project_company_id=>user_company_id).
left_join(:expense_items___i, :expense_project_id=>:project_id).
select_group(:p__project_name, :p__project_id).
select_more{count(:i__item_id)}.
select_more{sum(:i__amount)}.to_a.to_json
which works.
However, payment methods include cash, card and invoice. So I would like to sum each of those for summary purposes to achieve a discrete total for payments by cash, card, and invoice repsectively. I included the following line into the query
select_more{sum(:i__amount).where(:i__mop => 'card')}.
and the error message was
NoMethodError - undefined method `where' for #<Sequel::SQL::Function:0x007fddd88b5ed0>:
so I created the dataset separately with
ds1 = expense_items.where(:mop=>'card', :expense_company_id=>user_company_id).sum(:amount)
and appended it, at the end of the original query, with
select_append{ds1}
which achieved partial success as the returned json is now:
{"project_name":"project 2","project_id":2,"count":4,"sum":"0.40501E3","?column?":"0.2381E2"}
as can be seen there is no name for this element which I need in order to reference it in my getJSON call. I tried to add an identifier by adding ___a
to the ds1 query as below
ds1 = expense_items.where(:mop=>'card', :expense_company_id=>user_company_id).sum(:amount___a)
but that failed.
In summary, is this the right approach and, in any case, how can I provide an identifier when doing a sequel sum query? In other words sum(:a_column).as(a_name)
Many thanks.
Dataset#sum returns the sum, not a modified dataset. You probably want something like:
ds1 = expense_items.where(:mop=>'card', :expense_company_id=>user_company_id).select{sum(:amount)}
select_append{ds1.as(:sum)}