# Building weird commands

**URL:** https://discourse.rom-rb.org/t/building-weird-commands/644
**Category:** Issues
**Created:** [June 5, 2024, 1:35pm UTC](https://discourse.rom-rb.org/t/building-weird-commands/644 "2024-06-05T13:35:42Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![pointlessone](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/pointlessone/32/322_2.png) [@pointlessone](https://discourse.rom-rb.org/u/pointlessone)
#### Post date: [June 5, 2024, 1:35pm UTC](https://discourse.rom-rb.org/t/building-weird-commands/644/1 "2024-06-05T13:35:42Z")

</div>

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:

```SQL
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:

```ruby
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):

```sql
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?