Why unable to use .order and .limit for update source?

[jjangga0214]

Hi!

This works.

    let now = chrono::offset::Utc::now();

    diesel::update(foo
        .filter(expired_at.ge(now))  // <=== THIS WORKS
    )
        .set(bar.eq(bar + 1))
        .execute(conn);

While this doesn't.

    let now = chrono::offset::Utc::now();
    
    diesel::update(foo
        .filter(expired_at.ge(now))
        .order(expired_at.asc())  // <=== THIS CAUSES COMPILE ERROR
        .limit(1) // <=== THIS CAUSES COMPILE ERROR, TOO
    )
        .set(bar.eq(bar + 1))
        .execute(conn);

What I want conceptually is this.
(Of course this is possible in SQL)

UPDATE foo
SET bar = bar + 1
WHERE id = (
  SELECT id FROM foo 
  WHERE expired_at >= :now 
  ORDER BY expired_at 
  LIMIT 1
);

I wonder why this should not be permitted.
If there's no reason to disable this usage, I hope diesel supports it.

[weiznich]

This is not supported because the DSL wouldn't translate to your query.

diesel::update(foo
        .filter(expired_at.ge(now))
        .order(expired_at.asc())  // <=== THIS CAUSES COMPILE ERROR
        .limit(1) // <=== THIS CAUSES COMPILE ERROR, TOO
    )
        .set(bar.eq(bar + 1))

Would we translated to:

UPDATE foo
SET bar = bar + 1
WHERE expired_at > :now
ORDER BY expired_at
LIMIT 1

which is just not valid SQL.

For your SQL example you need to use a subquery as you do in your query. That's already supported by diesel.