CODEWITHSUNDEEP
jpaspring-bootspring-dataspring-data-jpa
user1002065
user1002065

Reputation: 615

spring data error when trying to sort by a field of joined entity inside a crudrepository

I am using springboot and springdata with Mysql.

I have 2 entities, Customer & Order:

@Entity
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id", nullable = false)
    protected long id;

    @Column(name = "name")
    private String name;
}

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id", nullable = false)
    protected long id;

    @Column(name="customer_id")
    private long customerId;
}

I also have a repository:

@Repository
public interface OrdersRepository extends JpaRepository<Order, Long> {

    @Query("select o from Order o, Customer c where o.customerId = c.id")
    Page<Order> searchOrders(final Pageable pageable);
}

The method has some more arguments for searching, but the problem is when I send a PageRequest object with sort that is a property of Customer.

e.g.

Sort sort = new Sort(Sort.Direction.ASC, "c.name");
ordersRepository.search(new PageRequest(x, y, sort));

However, sorting by a field of Order works well:

Sort sort = new Sort(Sort.Direction.ASC, "id");
ordersRepository.search(new PageRequest(x, y, sort));

The error I get is that c is not a property of Order (but since the query is a join of the entities I would expect it to work).

Caused by: org.hibernate.QueryException: could not resolve property c of Order

Do you have any idea how I can sort by a field of the joined entity?

Thank you

Upvotes: 1

Views: 3601

Answers (2)

Tom
Tom

Reputation: 1027

You got the error because the relationship is not modeled properly. In your case it is a ManyToOne relation. I can recomend the wikibooks to read further.

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name="id", nullable = false)
    protected long id;

    @ManyToOne
    @JoinColumn(name="customer_id", referencedColumnName = "id")
    private Customer customer;
}

The query is not needed anymore because the customer will be fetched.

@Repository
public interface OrdersRepository extends PagingAndSortingRepository<Order, Long> {
}

Now you can use nested properties.

Sort sort = new Sort(Sort.Direction.ASC, "customer.name");
ordersRepository.findAll(new PageRequest(x, y, sort));

Upvotes: 0

osama yaccoub
osama yaccoub

Reputation: 1866

In JPA , the thing that you sort with must be something that is returned in the select statement, you can't sort with a property that is not returned

Upvotes: 2

Related Questions