On Friday, December 30, 2016 at 4:38:38 PM UTC-8, Steve V wrote:
>
> I know Sequel is pretty powerful, and you can express a lot of different
> queries, but I was wondering if it would be better to just run this query
> as straight SQL, or if it could legibly be converted into Sequel statements.
>
> update period_categories pc
> set activity = cs.activity
> from (
> select ti.category_id, sum(ti.amount) as activity
> from transaction_items ti
> join transactions t
> on t.id = ti.transaction_id and t.date >= '2016/12/01' and t.date
> <= '2016/12/31'
> where ti.category_id in (1,2)
> group by ti.category_id) cs
> where cs.category_id = pc.category_id;
>
Here you go:
DB.from(Sequel[:period_categories].as(:pc),
DB[Sequel[:transaction_items].as(:ti)].
join(Sequel[:transactions].as(:t), :id=>:transaction_id,
:date=>'2016/12/01'..'2016/12/31').
where{{ti[:category_id]=>[1,2]}}.
select_group{ti[:category_id]}.
select_append{sum(ti[:amount]).as(:activity)}.as(:cs)).
where{{cs[:category_id]=>pc[:category_id]}}.
update(:activity=>Sequel[:cs][:activity])
This gets significantly more readable if you use the symbol_aref extension,
or have symbol splitting turned on (still the default in Sequel 4) and use
double/triple underscores in symbols.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
