Cannot server side sort by child entity field using Spring Data Rest [DATAREST-1024]

Lash Sanghera opened DATAREST-1024 and commented

I am implementing server side sorting with Spring Data REST and Spring Data JPA included in Spring Boot 1.3.1.RELEASE version. The JPA entity I would like to sort the results by has many to one entiry budgetPool.name

@Entity @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn(name = "TYPE", discriminatorType =DiscriminatorType.STRING) 
@Table(name = "PS_TRANSACTION") 
@ActiveBudgetPoolValidation 
public abstract class Transaction extends Auditable {
    @Id
    @Column(name = "ID", nullable = false, insertable = false, updatable = false)
    @SequenceGenerator(name = "TransactionSeq", sequenceName = "SEQ_TRANSACTION", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TransactionSeq")
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    protected Long id;

    @NotNull
    @JsonProperty(access = READ_WRITE)
    @ManyToOne(optional = false)
    @JoinColumn(name = "BUDGET_POOL_ID", referencedColumnName = "ID", updatable = false)
    protected BudgetPool budgetPool;

I display budgetPool.name on UI with the option to sort the results by budget pool name. The following URL to sort budget pool by id work:

http://host:port/finance-service/transactions/search/approvals?page=0&size=10&sort=budgetPool,desc

But the following url to sort by budget pool name throws java.sql.SQLSyntaxErrorException: invalid ORDER BY expression:

http://host:port/finance-service/transactions/search/approvals?page=0&size=10&sort=budgetPool.name,desc

My Repository has a custom query to return paged transactions based on the search parameters passed.

@RepositoryRestResource(excerptProjection = TransactionWithInlineProductControlAction.class, collectionResourceRel = "transactions")
    public interface TransactionRepository<T extends Transaction> extends JpaRepository<T, Long>, JpaSpecificationExecutor<T> {
    @RestResource(path = "approvals", rel = "approvals", description = @Description("Show approval transactions for the given status, orderId and budgetPoolId (paged)"))
        @Query("SELECT DISTINCT t " +
                "FROM Transaction t " +
                "JOIN t.budgetPool bp " +
                "LEFT JOIN bp.budgetApprovers ba " +
                "LEFT JOIN bp.technicalApprovers ta " +
                "WHERE (t.status = :status OR :status IS NULL) " +
                "AND (t.autoApproved = false OR t.autoApproved IS NULL) " +
                "AND (t.orderId LIKE %:orderId% OR :orderId IS NULL) " +
                "AND (bp.id=:budgetPoolId OR :budgetPoolId IS NULL) " +
                "AND (" +
                "ta.email LIKE ?#{hasRole('ROLE_ADMIN') || hasRole('ROLE_SERVICE_ACCOUNT') ? '%' : principal.username} " +
                "OR ba.email LIKE ?#{hasRole('ROLE_ADMIN') || hasRole('ROLE_SERVICE_ACCOUNT') ? '%' : principal.username} " +
                "OR 1=?#{hasRole('ROLE_ADMIN') || hasRole('ROLE_COMMERCIAL_MANAGEMENT') || hasRole('ROLE_PRODUCT_MANAGEMENT') ? 1 : 0}" +
                ")"
        )   
        Page<T> findByStatusAndBudgetPool(
                @Param("status") TransactionStatus status,
                @Param("orderId") String orderId,
                @Param("budgetPoolId") Long budgetPoolId,
                Pageable p
        );
    }

If I remove DISTINCT the sorting by bugetPool.name work fine but I get duplicate results in my query.

I asked this question on stackoverflow few weeks back but no helpful reply.

Thank you for your help in advance.

Reference URL: http://stackoverflow.com/questions/42112047/cannot-server-side-sort-by-child-entity-field-using-spring-data-rest

6 votes, 7 watchers