On Friday, January 3, 2020 at 11:57:39 AM UTC-8, BeeRich33 wrote:
>
> Hi folks.
>
> I'm migrating to Sequel from using the PG gem, due to the async nature of
> some of their calls. I was getting closure on the connections so I thought
> I'd ride this train.
>
> I have some dashboard charts that require SQL statement like the
> following. Is it recommended that I use straight SQL for these, or can
> Sequel handle them?
>
> res = DBN.fetch( %Q{ SELECT a."TheDate", COALESCE(b."recs", 0) mycount
> FROM
> (
> SELECT CURRENT_DATE - i "TheDate"
> FROM generate_series(0, 29) i
> ) a
> LEFT OUTER JOIN (
> SELECT ts::date, COUNT(*) "recs"
> FROM ptree_admin
> WHERE ts::date > (CURRENT_DATE - INTERVAL '30 DAY')::DATE
> AND kind = 'search_done'
> GROUP BY ts::date
> )b ON a."TheDate"=b.ts::date
> } ).to_a
>
> It's a bit of a doozie even for straight SQL. Just wondering how advanced
> Sequel is. I'm happy all the same as it provides DB.fetch.
>
Sequel can handle this without using raw SQL:
DB.from{generate_series(0, 29).as(:i)}.
select((Sequel::CURRENT_DATE - :i).as(:TheDate)).
from_self(:alias=>:a).
select{[a[:TheDate], coalesce(b[:recs], 0).as(:mycount)]}.
left_join(DB[:ptree_admin].
select_group{ts.cast(Date)}.
select_append{count.function.*.as(:recs)}.
where{ts.cast(Date) > (Sequel::CURRENT_DATE - Sequel.cast('30 day',
:interval)).cast(Date)}.
where(:kind=>'search_done').as(:b),
:TheDate =>Sequel[:b][:ts].cast(Date)
)
SQL generated (whitespace added):
SELECT "a"."TheDate", coalesce("b"."recs", 0) AS "mycount"
FROM (
SELECT (CURRENT_DATE - "i") AS "TheDate"
FROM generate_series(0, 29) AS "i"
) AS "a"
LEFT JOIN (
SELECT CAST("ts" AS date), count(*) AS "recs"
FROM "ptree_admin"
WHERE ((CAST("ts" AS date) > CAST((CURRENT_DATE - CAST('30 day' AS
interval)) AS date))
AND ("kind" = 'search_done'))
GROUP BY CAST("ts" AS date)
) AS "b" ON ("b"."TheDate" = CAST("b"."ts" AS date))
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/8e1c08d7-2207-450d-ab1a-b155ece87aa5%40googlegroups.com.
