Thanks for the quick reply. I had a couple issues though. First I was
getting undefined method [] for Sequel::Module So I changed all of the
Sequel occurrences with `DB`, but that just moved the undefined method
[] issue further down to #<Sequel::SQL::Identifier @value=>:ti>. After
I submitted that query I realized I needed to make some changes to the
query, but now I get:
Sequel::DatabaseError: PG::UndefinedTable: ERROR: relation "pc2" does not exist
LINE 1: SELECT * FROM "pc2" WHERE "category_id" LIMIT 1
This is the code I attempted to write:
DB.from(DB[:period_categories].as(:pc),
DB[DB[:period_categories].as(:pc2)].
left_outer_join(DB[:transaction_items].as(:ti),
category_id: DB[:pc2][:category_id]).
left_outer_join(DB[:transactions].as(:t), id:
DB[:ti][:transaction_id]).
where{{pc2[:period_id] => period.id, pc2[:category_id] =>
category_ids}}.
select_group{pc2[:category_id]}.
select_append{coalesece(sum(ti[:amount],
0)).as(:activity)}.as(:pcs)).
where{{pc[:period_id] => period.id, pc[:category_id] =>
pc2[:category_id]}}.
update{pc[:balance] = pc[:initial] + pcs[:activity] }.sql
I attempted to break it down, and this is what I got, first using an
explicit join, and next using an implicit one:
DB[DB[:period_categories].as(:pc2)].left_outer_join(DB[:transaction_items].as(:ti),
:category_id => DB[:pc2][:category_id])
Sequel::DatabaseError: PG::UndefinedTable: ERROR: relation "pc2" does not exist
LINE 1: SELECT * FROM "pc2" WHERE "category_id" LIMIT 1
^
DB[DB[:period_categories].as(:pc2)].left_outer_join(DB[:transaction_items].as(:ti),
:category_id => :category_id)
=> #<Sequel::Postgres::Dataset: "SELECT * FROM (SELECT * FROM
\"period_categories\") AS \"pc2\" LEFT OUTER JOIN (SELECT * FROM
\"transaction_items\") AS \"ti\" ON (\"ti\".\"category_id\" =
\"pc2\".\"category_id\")">
The SQL on that looks really weird with a bunch of (SELECT * FROM)
statements instead of just the table names. Not sure what's going on
there. If it's just a product of the currently malformed query, or
what.
This is what the SQL statement should look like for reference:
update period_categories pc
set balance = pc.funded + pcs.activity
from (
select pc2.category_id, coalesce(sum(ti.amount), 0) activity
from period_categories pc2
left join transaction_items ti on pc2.category_id = ti.category_id
left join transactions t on t.id = ti.transaction_id
and t.date >= :start_at and t.date <= :end_at
where pc2.period_id = :period_id and pc2.category_id in :category_ids
group by pc2.category_id) pcs
where pc.period_id = :period_id and pc.category_id = pcs.category_id;
Thanks
On Fri, Dec 30, 2016 at 9:40 PM, Jeremy Evans <[email protected]> wrote:
> 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 a topic in the
> Google Groups "sequel-talk" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sequel-talk/E-_CQh28FDY/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
--
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.
