How to dynamically sort by different joined table column-CodePudding

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]

CodePudding user response：

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.

CodePudding user response：

I would be choose column lastModifiedDate or createdDate and call descending order, because I want to get firstly fresh invoices