On Thursday, November 8, 2018 at 8:39:05 AM UTC-8, Troex Nevelin wrote:
>
> 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!
>
As the PostgreSQL documentation states
(https://www.postgresql.org/docs/current/sql-selectinto.html), SELECT INTO
is basically the same as CREATE TABLE AS, and CREATE TABLE AS is the
recommended syntax. Sequel supports CREATE TABLE AS via:
DB.create_table(:costs_1, :as=>DB[:visits].
join(:amounts, :filter_id=>:filter, :start_date=>:visit_date).
with(:visits, DB[:visits_1].
select_group{[:filter, CONVERT_TIMEZONE('America/Los Angeles',
:visit_time).cast(Date).as(:visit_date)]}.
select_append{count(:id).as(:visit_count)}
).
where(:project_id=>1).
select do [
(value.cast(Float) / visit_count).as(:cpv),
:visit_count,
:visit_date,
:filter,
value.as(:amount)
] end
)
Which uses the following SQL:
CREATE TABLE "costs_1" AS
WITH "visits" AS (
SELECT "filter",
CAST(CONVERT_TIMEZONE('America/Los Angeles', "visit_time") AS
date) AS "visit_date",
count("id") AS "visit_count"
FROM "visits_1"
GROUP BY "filter",
CAST(CONVERT_TIMEZONE('America/Los Angeles', "visit_time") AS
date)
)
SELECT (CAST("value" AS double precision) / "visit_count") AS "cpv",
"visit_count",
"visit_date",
"filter",
"value" AS "amount"
FROM "visits"
INNER JOIN "amounts" ON (("amounts"."filter_id" = "visits"."filter")
AND ("amounts"."start_date" =
"visits"."visit_date"))
WHERE ("project_id" = 1)
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
