Order By named parameter

[engilyin]

I can't set the query order using the named parameter like

@Query("""
    SELECT * 
    FROM mytable
    ORDER BY :orderParam
    OFFSET :offset LIMIT :limit
""")
public Flux<MyObject> findObjects(@Param("orderParam") String orderParam, 
            @Param("offset") long offset,
            @Param("limit") long limit);

If I call:

var objects = repo.findObjects("fieldname DESC", 0, 100);

It works but using default order.

That happens because this parameter is wrapped with single quotas as a SQL string literal:

    SELECT * 
    FROM mytable
    ORDER BY 'fieldname DESC'
    OFFSET 0 LIMIT 100

Also you can find similar request at:
https://stackoverflow.com/questions/74111689/r2dbc-querying-from-multiple-tables-with-sorting-and-pagination/74756531#74756531

[mp911de]

Binding parameters only work well within SELECT, WHERE and HAVING. ORDER BY cannot be parametrized.

[tiagodevss]

Hi! Would you have any alternative?

[mp911de]

The alternative is to create a custom repository fragment and running your query via DatabaseClient and mapping rows through R2dbcConverter. Alternatively, you can wrap your query into PreparedOperation and run it via R2dbcEntityTemplate.

[NursultanMuss]

Hi. Can you expose some examples of usage this advice?