1

I have a one-to-many relationship with Customer and Order entity. This is of a composition relationship. I want to write a query to find out if there is a matching Order with the given orderId and the customerId. This method should be in the CustomerRepository

    @Query("select c from Customer c inner join c.orders o where c.id= ?1 and c.orders.")
    Optional<Customer> findCustomerByOrderId(long orderId, long customerId);

I am interested in knowing, if the orderid belongs to the customer. What should be the query and should the method return a count or a boolean from the performance point of view.

zilcuanu
  • 3,451
  • 8
  • 52
  • 105

1 Answers1

2

If you have an Order entity with an id property and customerId property, you can use spring JPA magic and add a method to your OrderRepository (if you have one):

boolean existsByIdAndCustomerId(long orderId, long customerId)

Performance will be better than getting the customer entity, since it requires less data transfer from the DB to your application.

You can also share more detailed code if this does not answer your question.

Your @Query can be updated as follows to get the customer

@Query("select c from Customer c inner join c.orders o where c.id = :customerId and o.id = :orderId")
Optional<Customer> findCustomerByOrderId(long orderId, long customerId);

Or if you want to return a count

@Query("select count(c) from Customer c inner join c.orders o where c.id = :customerId and o.id = :orderId")
int countCustomerByOrderId(long orderId, long customerId);
leonardseymore
  • 533
  • 5
  • 20
  • I want to write this method inside the `CustomerRepository` and it should be using `@Query` and not DSL – zilcuanu May 31 '22 at 08:56
  • See updated answer. I'm making some assumptions and hope I don't have typos – leonardseymore May 31 '22 at 09:03
  • How is this query ```@Query("select count(c) from Customer c inner join Order o on c.id = o.customer.id and c.id = :customerId and o.id = :orderId") int findCustomerByOrderId(long orderId, long customerId);``` different from your query? – zilcuanu May 31 '22 at 09:27
  • Also, should it be ```Customer c inner join c.orders``` or ```Customer c inner join Orders``` ? Is there any difference? – zilcuanu May 31 '22 at 09:28
  • There are many ways to skin a cat. It is difficult to say exactly what the best approach is based on what I can see. It all depends on how your entities have been annotated. – leonardseymore May 31 '22 at 09:34