JpaRepository method name with DISTINCT ON

Question

I'm creating Spring application and I'm using JpaRepository to simplify database queries.

I have problem with creating method name using JpaRepository conventions with DISTINCT ON. Here's my SQL query:

SELECT DISTINCT ON (s.device_id)
  s.status_id,
  s.error,
  s.receive_time,
  s.device_id
FROM statuses s
ORDER BY s.device_id, s.receive_time DESC

I tried such name but it's not working:

List<Status> findDistinctByDeviceId_OrderByReceiveTimeDesc();

Here's my classes (simplified):

public class Status {
    private long status_id;
    private String error;
    private Date receive_time;
    private Device device_id;
}

public class Device {
    private long device_id;
    private String name;
}

Is is even possible to use DISTINCT ON in method name? I wouldn't like to use SQL queries because my classes are much more complex than above and I would like to add every field to query.

Answer 1

I used something like this and it works:

@Query(value = "select distinct on (s.device_id) s.* " +
        "from statuses s " +
        "order by s.device_id, s.receive_time desc",
        nativeQuery = true)
public List<Status> getStatuses();

Answer 2

As far as I know, DISTINCT ON is database specific, than a global SQL command. And it looks like distinct just for one column.

But if you want to DISTINCT the whole SQL line, you could do something like:

// Spring Data allows you to specify the entity you want to distinct after the word "Distinct"
List<Status> findDistinctStatusByOrderByReceiveTimeDesc();

// Or before the word "Distinct"
List<Status> findStatusDistinctByOrderByReceiveTimeDesc();

This would already ensure that you'll not receive any duplicated lines, creating a SQL output like:

SELECT DISTINCT 
  s.device_id,
  s.status_id,
  s.error,
  s.receive_time,
  s.device_id
FROM statuses s
ORDER BY s.device_id, s.receive_time DESC

More information on this, you can check into Spring Data Documentation