Repository - order by in native query not working

Question

I have a spring data JPA repository (on a postgres db) and from time to time I need to use native queries using the nativeQuery = true option.

However in my current situation I need to pass in an order field and am doing so like this:

the call..

targetStatusHistoryRepository.findSirenAlarmTimeActivation([uuid,uuid2],"activation_name DESC", 0, 10)

.. the repo method

@Query(
        nativeQuery = true,
        value = """select
                     a.name as activation_name,
                     min(transition_from_active_in_millis),
                     max(transition_from_active_in_millis),
                     avg(transition_from_active_in_millis) from target_status_history t, activation_scenario a
                     where t.activation_uuid=a.activation_scenario_id and t.transition_from_active_in_millis > 0 and t.activation_uuid in (:activationUUIDs) group by a.name,t.activation_uuid
                     order by :orderClause offset :offset limit :limit """
)
List<Object[]> findSirenAlarmTimeActivation(@Param("activationUUIDs") List<UUID> activationUUIDs,
                                                              @Param("orderClause") String orderClause, @Param("offset") int offset, @Param("limit") int limit )

I wrote a unit test with a DESC and then a ASC call and vice versa, and it seems what ever the first call is, the second gives the same result.

score 17 · Answer 1 · answered Sep 27 '14 at 04:58

If that's a prepared statement, and that's a bind value being supplied in the ORDER BY clause, that is valid, BUT...

The bind value supplied won't be interpreted as SQL text. That is, the value will be seen as just a value (like a literal string). It won't be seen as a column name, or an ASC or DESC keyword.

In the context of your statement, supplying a value for the :orderClause bind placeholder, that's going to have the same effect as if you had written ORDER BY 'some literal'.

And that's not really doing any ordering of the rows at all.

(This is true at least in every SQL client library I've used with DB2, Teradata, Oracle, SQL Server, MySQL, and MariaDB (JDBC, Perl DBI, ODBC, Pro/C, et al.)

(MyBatis does provide a convenient mechanism for doing variable substitution within the SQL text, dynamically changing the SQL text before it's prepared, but those substitutions are handled BEFORE the statement is prepared, and don't turn into bind placeholders in the statement.)

It is possible to get some modicum of "dynamic" ordering with some carefully crafted expressions in the ORDER BY clause. For example, we can have our static SQL text be something like this:

  ORDER BY CASE WHEN :sort_param = 'name ASC'  THEN activation_name END ASC
         , CASE WHEN :sort_param = 'name DESC' THEN activation_name END DESC

(The SQL text here isn't dynamic, it's actually static, it's as if we had written.

 ORDER BY expr1 ASC
        , expr1 DESC

The "trick" is that the expressions in the ORDER BY clause are conditionally returning either the value of some column from each row, or they are returning a literal (in the example above, the literal NULL), depending on the value of a bind value, evaluated at execution time.

The net effect is that we can "dynamically" get the effect of either:

 ORDER BY activation_name ASC, NULL DESC

or

 ORDER BY NULL ASC, activation_name DESC

or

 ORDER BY NULL ASC, NULL DESC

depending on what value we supply for the :sort_param placeholder.

Ill give it a go, but what about the limit and offset params ? what can I do there — 1977, Sep 28 '14 at 15:44
great, it works, no issues with limit or offset either. Thanks dude — 1977, Sep 28 '14 at 15:59
I have use ` ORDER BY CASE WHEN :sort_param = 'name ASC' THEN activation_name END ASC` but i got `activation_name` column not found. do you have any idea? — Akash Chavda, Sep 18 '19 at 06:25
in this answer `activation_name` was a reference to an expression in the SELECT list, actually, the alias assigned to the first expression `SELECT a.name AS activation_name, ...`. MySQL allows the column alias to be referenced in an ORDER BY clause. In databases that don't allow that, we could use `a.name` instead. The exact expressions in the ORDER BY clause are going to depend on what we are attempting to achive, what column we want to order by. — spencer7593, Sep 18 '19 at 15:21
This literally saved me. I have been trying to figure out the solution for this, and this answer clarified the issue that I was facing. Thank you @spencer7593. — aevin, Mar 07 '22 at 08:03

score 1 · Answer 2 · edited May 23 '17 at 12:18

1

You can use pageable with the SpEL langage. The Sort object in Pageable will be used to append " order by " in the end of the request. Here is an example.

edited May 23 '17 at 12:18

Community

1
1

answered Sep 23 '16 at 11:49

Stephane L

2,879
1
34
44

score 1 · Answer 3 · answered Jan 24 '20 at 12:33

1

Use createNativeQuery and directly append the order by value as string into query rather than using setParameter(). It worked fine for me.

answered Jan 24 '20 at 12:33

Vivek Goel

762
2
10
21

score 1 · Answer 4 · edited Apr 06 '21 at 16:14

I had the same problem using native query in Spring Boot and the way that i found was:

1- Create a pageable:

Pageable pageable = PageRequest.of(numPage, sizePage, Sort.by(direction , nameField));

2- Add "ORDER BY true" into the query, for example:

@Query(value = " SELECT  * " +
            "FROM articulos a   " +
            "WHERE " +            
            "AND a.id = :id" +           
            "ORDER BY TRUE",
        countQuery = " SELECT  * " +
            "FROM articulos a   " +
            "WHERE " +            
            "AND a.id = :id" +           
            "ORDER BY TRUE"
        , nativeQuery = true)
    Page<PrecioArticuloVO> obtenerPreciosPorCategoriaProveedor(@Param("id")Long id,Pageable pagina);

Repository - order by in native query not working

4 Answers4

Linked