Preserve order of results with WHERE IN with jOOQ

The issue has already been described in detail in ORDER BY the IN value list,

but to repeat: I want to select items with ids 1,3,2,4 in that specific order.

My jOOQ snippet:

var results = create.select().from(ITEM).where(ITEM.ID.in(1,3,2,4)).fetchInto(Item.class);

The resulting results list will have items in order of 1,2,3,4. How can I preserve the order of the items with jOOQ?

Answer

You can use Field.sortAsc() for this:

create.select()
      .from(ITEM)
      .where(ITEM.ID.in(1, 3, 2, 4))
      .orderBy(ITEM.ID.sortAsc(1, 3, 2, 4))
      .fetchInto(Item.class);

Or, of course, to avoid repeating the list, use a local variable:

Integer[] ids = { 1, 3, 2, 4 };
create.select()
      .from(ITEM)
      .where(ITEM.ID.in(ids))
      .orderBy(ITEM.ID.sortAsc(ids))
      .fetchInto(Item.class);

See also this article about sort indirection here.