Hi Jeremy
As per your suggestion, the CTE form works - many thanks.
I quickly tried the sub-query form, but seems busted. Details below if
you're interested.
== Works ==
claimed_jobs = @db[:jobs].returning(:id, :status, :created_at).where(id:
1).with_sql(:update_sql, status: 'ACTIVE')
ordered_claimed_jobs_data = @db[:claimed_jobs].with(:claimed_jobs,
claimed_jobs).order(Sequel.asc :created_at).sql
SQL produced:
WITH "claimed_jobs" AS (
UPDATE "jobs"
SET "status" = 'ACTIVE'
WHERE ("id" = 1)
RETURNING "id", "status", "created_at")
SELECT *
FROM "claimed_jobs"
ORDER BY "created_at" ASC
== Doesn't work ==
claimed_jobs = @db[:jobs].returning(:id, :status, :created_at).where(id:
1).with_sql(:update_sql, status: 'ACTIVE')
ordered_claimed_jobs_data = claimed_jobs.from_self.order(Sequel.asc
:created_at).all
SQL produced:
SELECT * FROM (
UPDATE "jobs"
SET "status" = 'ACTIVE'
WHERE ("id" = 1)
RETURNING "id", "status", "created_at"
) AS "t1"
ORDER BY "created_at" ASC
Error:
Sequel::DatabaseError:
PG::SyntaxError: ERROR: syntax error at or near "SET"
LINE 1: SELECT * FROM (UPDATE "jobs" SET "status" = 'ACTIVE' WHERE
(...
On Friday, 25 January 2019 19:16:15 UTC, Jeremy Evans wrote:
>
> On Friday, January 25, 2019 at 9:42:37 AM UTC-8, Greg H wrote:
>>
>> Hi there
>>
>> I'd like to order the results from an UPDATE .. RETURNING statement
>> (sequel's .returning() method).
>> Is this possible?
>> My approach detailed below isn't working, so I'd appreciate some advice.
>>
>> Many thanks
>> Greg
>>
>> --
>>
>> To order the results from an UPDATE .. RETURNING statement, I've followed
>> the suggestion from:
>>
>> https://stackoverflow.com/questions/25649976/update-returning-order-by-in-postgresql
>>
>> In short, assign the UPDATE .. RETURNING dataset to a Common Table
>> Expression, then sort that.
>>
>> So in sequel-land, use:
>>
>> https://www.rubydoc.info/github/jeremyevans/sequel/Sequel%2FDataset:returning
>> as input to..
>> https://www.rubydoc.info/github/jeremyevans/sequel/Sequel%2FDataset:with
>>
>> However, this doesn't seem to work, though using a .select() as input to
>> .with() does.
>> From debugging, the main difference is that .returning() actually runs
>> the update and returns an array of hash-results, whilst .select() is a
>> sequel dataset (hasn't run yet)
>>
>> == Works (.select as input to .with) ==
>>
>> claimed_jobs = @db[:jobs].select(:id, :created_at, :status).where(id:
>> oldest_runnable_job_ids)
>> ordered_claimed_jobs_data = @db[:claimed_jobs].with(:claimed_jobs,
>> claimed_jobs).order(Sequel.asc :created_at).all
>>
>> == Doesn't work (.returning as input to .with) ==
>>
>> claimed_jobs = @db[:jobs].returning(:id, :created_at, :status).where(id:
>> oldest_runnable_job_ids).update(status: 'ACTIVE')
>> ordered_claimed_jobs_data = @db[:claimed_jobs].with(:claimed_jobs,
>> claimed_jobs).order(Sequel.asc :created_at).all
>>
>> The .with call errors with: NoMethodError: undefined method `opts' for
>> #<Array:0x000056172631f1f8>
>>
>
> The doesn't work example is because Dataset#update in the first line runs
> the UPDATE query and returns the number of rows updated. In the first line,
> switch:
>
> update(status: 'ACTIVE')
>
> to:
>
> with_sql(:update_sql, status: 'ACTIVE')
>
> Then the second line will work and produce the following SQL:
>
> WITH "claimed_jobs" AS (
> UPDATE "jobs"
> SET "status" = 'ACTIVE'
> WHERE ("id" IN (1))
> RETURNING "id", "created_at", "status"
> )
> SELECT *
> FROM "claimed_jobs"
> ORDER BY "created_at" ASC
>
> I'm guessing (but haven't tested) you can also use a subquery instead of a
> CTE:
>
> claimed_jobs.from_self.order(:created_at).all
>
> which produces this SQL:
>
> SELECT * FROM (
> UPDATE "jobs"
> SET "status" = 'ACTIVE'
> WHERE ("id" IN (1))
> RETURNING "id", "created_at", "status"
> ) AS "t1"
> ORDER BY "created_at"
>
> 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.
