Ha! found solution 5 minutes later after I posted (actually I wrote a post yesterday and forgot to post).
So instead of WITH I simply used nested SELECT "DB.from(visits)" (not sure how I could forgot about it) but the question is still actual, why not support "SELECT INTO" in sequel? It looks to be easy to implement. On Thursday, November 8, 2018 at 6:39:05 PM UTC+2, 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! > -- 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.