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

[spring-projects-issues]

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

[spring-projects-issues]

Predrag commented

Hello,

 

This issue is still present in SDR 3.1.4 as well as SDR 3.2.0.M1 .. From the DATAREST-976 I'm concluding that this still isn't supposed to be fixed even in latest releases. I wonder however why using associations is working perfectly for filtering, but cannot be fixed/implemented for sorting?

 

 

[spring-projects-issues]

HZ commented

any update on this one? upgrading from spring-data-rest-webmvc:2.3.0.RELEASE to the latest causes this issue

[tmbell]

No update in 11 months? This seems like a pretty big regression, workarounds we implemented are spectacularly Janky and I don’t want to have to maintain them. Please fix this

[electronickai]

I also just stumbled over this error and am also wondering why filtering is possible between two tables but sorting is not. I don't even get any exceptions during the sort, it just passes back the results but without applying the sorting - like described in DATAREST-976.

[fortamt]

Try use "." in your request. Example http://localhost:8080/api/user/all-experts?sort=doctor.mainInstitution.city.name. You need only Paging and sorting interface implemented.

[electronickai]

Thanks. I had another look on the issue. We have a path like this:

/entityA?page=0&size=50&sort=entityB.id%2Cdesc&sort=id%2Cdesc

Where entityB is configured as follows in entityA:

@Valid
@ManyToOne
private MyJavaType entityB

When executing the request, the execution gets into JacksonMappingAwareSortTranslator.translateSort where the Sort object is created based on the URL parameters. From there it follows the execution path to getMappedPropertyPath and mapPropertyPath. However, because the check associations.isLinkableAssociation(persistentProperties) returns true, the sorting criteria is skipped by returning an empty list to the caller.

If I would force the method isLinkableAssociation to return false instead (e.g. by using the debugger to set property.isAssosiation to false), the sorting would work as I would expect it - just because it would be considered then in the resulting jpql query afterwards. So the sorting is just not done because the property (entityB) is recognized as an association.

Especially because it would work, I don't really understand why this explicit check for the association is done. Would it be possible to release a fix just checking the cases that really need to be checked?

[chlustanec]

Hi.
I found one workaround, that works for me. When I annotate the nested entity with @RestResource(exported=false), it works. Thanks to @electronickai , whose comment set me on the right track :)

So using his example, my entity now looks like this

@RestResource(exported = false)
@ManyToOne
@JoinCollumn(...not important...)
@NotFound(...not important...)
private MyJavaType entityB