How to dynamically sort by different joined table column

Question

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {
    @Query("SELECT DISTINCT NEW com.invoice.InvoiceResult"
            + "(i.invoiceId, t.vendor, i.totalTrips, i.fromDate, i.toDate, i.totalFare, i.paidAmount, i.status, i.createdDate, i.lastModifiedDate)"
            + " FROM Invoice i"
            + " JOIN i.trips t")
    List<InvoiceResult> retrieveInvoices(Sort sort);

}

String sortField = "";
Sort.by(sortField).ascending();

What should be value of sortField achieve sorting by any of below selected columns?

i.invoiceId, t.vendor, i.totalTrips, i.fromDate, i.toDate, i.totalFare, i.paidAmount, i.status, i.createdDate, i.lastModifiedDate

If I pass i.invoiceId, I get below error

org.hibernate.QueryException: could not resolve property: i of: com.seamless.one.billing.domain.Invoice [SELECT DISTINCT NEW com.seamless.one.billing.pojo.InvoiceResult(i.invoiceId, t.vendor, i.totalTrips, i.fromDate, i.toDate, i.totalFare, i.paidAmount, i.status, i.createdDate, i.lastModifiedDate) FROM com.seamless.one.billing.domain.Invoice i JOIN i.trips t WHERE t.vendor = :vendor order by i.i.invoiceId asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: i of: com.seamless.one.billing.domain.Invoice [SELECT DISTINCT NEW com.seamless.one.billing.pojo.InvoiceResult(i.invoiceId, t.vendor, i.totalTrips, i.fromDate, i.toDate, i.totalFare, i.paidAmount, i.status, i.createdDate, i.lastModifiedDate) FROM com.seamless.one.billing.domain.Invoice i JOIN i.trips t WHERE t.vendor = :vendor order by i.i.invoiceId asc]

Answer 1

You are very close, if you write it without the i. before it should work.

Sort.by(Sort.Direction.ASC, "invoiceId");

In the last line of your error you can even see, that the created query uses i.i.invoiceId to order your query which is obviously one i. too much.