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.
