On Tuesday, November 14, 2017 at 1:13:43 PM UTC-8, Jason Landry wrote:
>
> I'm trying to do a CTE insert (using with). I can come up with a real
> example if you want, but I thought just a contrived example would
> illustrate what I mean.
>
> Suppose I have a dataset that looks like this:
>
> *incoming_data = DB[:incoming].with(:incoming, User.where(new_record:
> true).select(:field1, :field2))*
>
> That creates exactly what I'd expect if I output the sql:
>
> # puts incoming_data.sql
> WITH "incoming" AS (SELECT "field1", "field2" FROM "users" WHERE
> ("new_record" IS TRUE)) SELECT * FROM "incoming"
>
> The problem comes when I tried to use this with insert:
>
> *Review.insert([:field1, :field2], incoming_data.select(:field1, :field2))*
>
> INSERT INTO "reviews" ("field1", "field2")
> WITH "incoming" AS (SELECT "field1", "field2" FROM "users" WHERE
> ("new_record" IS TRUE)) SELECT "field1", "field2" FROM "incoming"
> RETURNING "id"
>
> It should look more like this:
> WITH "incoming" AS (SELECT "field1", "field2" FROM "users" WHERE
> ("new_record" IS TRUE))
> INSERT INTO reviews ("field1", "field2")
> select "field1", "field2" from incoming
>
> At this point, I can't figure out how to use a WITH dataset with an insert
> or import.
>
> Ideas?
>
You have the CTE on the subquery and not the main query. Try:
Review.
with(:incoming, User.where(new_record: true).select(:field1, :field2)).
insert([:field1, :field2], DB[:incoming].select(:field1, :field2))
Some databases don't support WITH in subqueries, some do. Sequel often
tries to hoist CTEs from subqueries to the main query on databases that do
not, but it apparently does not handle this case. That's probably fixable,
I'll see if I can make the changes before the next release.
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 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.
