CODEWITHSUNDEEP
mysqljpaspring-data
user978758
user978758

Reputation: 599

@Query with or, and

In my db I have two types of users: test and production. I have the method to search them by id or order number. In db is column with boolean param isTest (true if user is from test enviroment and false for other). I have to make query in repository(spring data, jpa) with free params (id, oorderId and isTest) and I want to display only productions users. I made something lkie this (but doesn't work, I mean the search by orderId or id is ok but boolean property is not):

public interface UserRepository extends JpaRepository<User, Long>{

@Query(select u from User where u.orderId = ?1 or u.id = ?1 and isTest = false")
Page<User> searchUsers(Pageable pageable, String orderId, String id, boolean isTest);

} Thanks for help

Upvotes: 0

Views: 111

Answers (1)

eggyal
eggyal

Reputation: 125835

As documented under Operator Precedence, AND has higher precedence than OR. Therefore your current filter is evaluated as:

WHERE u.orderId = ?1 OR (u.id = ?1 AND isTest = false)

You should add explicit parentheses to force the precedence that suits your requirements:

WHERE (u.orderId = ?1 OR u.id = ?1) AND isTest = false

Upvotes: 1

Related Questions