Building weird commands

Issues
1

I’m very new to both ROM and Sequel but it seem like what I’m trying to do is very non-standard so I might as well ask.

What I’m trying to do

I’m using sqlite. It has this non-standard syntax for updates:

update doodads
set status = 'in_progress'
from (
  select * from doodads
  where status = 'pending' and category = ?
  order by random()
  limit 1
)
returning *

It finds a random unprocessed doodad, marks is as being processed and returns it. All in one query.

Bits and pieces

It is my understanding so in order to do that I need a custom ROM command. Let’s call it Reserve.

I guess, I can make it inherit from Update. Maybe?

And in it I need to implement execute. I somehow need to get the category in. Is it an argument to the method?

In execute I need to build the query. I found that relation.dataset is a Sequel dataset. However, it doesn’t look like there’s native support for update ... from (...).

My current best is the following, it s not quite right:

relation.dataset
  .from(
    relation.dataset
    .where(category: category, status: 'pending')
    .order{ Sequel.function(:random) }
    .limit(1)
  )
  .returning
  .update(status: 'in_progress')

This seem to produce the following SQL (paraphrased):

UPDATE (
  SELECT *
  FROM `doodads`
  WHERE ((`category` = 'category') AND (`status` = 'pending'))
  ORDER BY random()
  LIMIT 1
) AS 't1'
SET `status` = 'in_progress'
RETURNING *

As you can see, it has the right parts but overall query is not even syntactically correct.

So… Please help me bend it into shape suing Sequel APIs. Alternatively, how can I give it some raw SQL?

Do I even need a command here or should I use something else instead?