Hi, I couldn't find a way to write SELECT * INTO query, I've basically a
case where I need to use WITH and JOIN both when inserting into table, I'm
not "pro" in SQL but as far as I know the only way to implement it using is
SELECT INTO statement. I've been working tightly with sequel past two years
building complex queries but this is first time I'm really stuck.
Here is full working query I'm trying rewrite into sequel
WITH "visits" AS
(SELECT "filter",
COUNT("id") AS "visit_count",
CAST(CONVERT_TIMEZONE('America/Los_Angeles', "visit_time") AS date
) AS "visit_date"
FROM "visits_1"
GROUP BY "filter",
"visit_date")
SELECT ("value"::float / "visit_count") AS "cpv",
"visit_count",
"visit_date",
"filter",
"value" AS "amount"
INTO "costs_1"
FROM "visits"
INNER JOIN "amounts" ON (("amounts"."filter_id" = "visits"."filter")
AND ("amounts"."start_date" = "visits"."visit_date"
))
WHERE ("project_id" = 1)
;
And here is ruby code I currently have, which generates INSERT statement
which doesn't work
project_id ||= 1
timezone ||= 'America/Los_Angeles'
visits_table = :"visits_#{project_id}"
table_name = :"costs_#{project_id}"
visits = DB[visits_table].
select(
:filter,
Sequel.lit('COUNT(?)', :id).as(:visit_count),
Sequel.lit('CONVERT_TIMEZONE(?, ?)', timezone, :visit_time).cast(:date).
as(:visit_date)
).
group(:filter, :visit_date)
query = DB[:visits].
# with(:visits, visits). # moved to insert query
select(
Sequel.lit('(?::float / ?)', :value, :visit_count).as(:cpv),
:visit_count,
:visit_date,
:filter,
Sequel.as(:value, :amount)
).
join(:amounts, { filter_id: :filter, start_date: :visit_date }).
where(project_id: project_id)
DB[table_name].
with(:visits, visits).
insert_sql(query)
Probably the solution might be somewhere around
here
https://github.com/jeremyevans/sequel/blob/51d96b8700df2c4cae9963bd90428efc92755e43/lib/sequel/adapters/shared/postgres.rb#L1361
I think I've seen something about SELECT INTO in MSSQL adapter code.
I think my query can be rewritten without using SELECT INTO, but some
pieces of the ruby code are shared across my app and I'l like to keep these
business logic pieces "shared". Any advice or solution is welcome.
Thank you Jeremy for best gem ever!
--
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.
