Is there any way to combine several select statements into single query?

50 Views Asked by At

I have query:

@Query(
"SELECT p " +
"FROM PostDAO p " +
"WHERE LOWER (p.text) LIKE LOWER (%:search%) " +
"AND (" +
    "(LOWER (p.user.institution.name) LIKE LOWER (%:orgname%) " +
    "AND LOWER(p.user.institution.address) LIKE LOWER(%:address%))" +
    "OR " +
    "(LOWER(p.user.company.companyName) LIKE LOWER(%:orgname%) " +
    "AND LOWER(p.user.company.address) LIKE LOWER(%:address%))" +
")")
Page<PostDAO> search(
    @Param("search") String search,
    @Param("orgname") String orgName,
    @Param("address") String address, 
    Pageable pageable);

But it doesn't work as it should(Details here Spring JPA query null check condition is removing all rows with null values).

So one of the possible solutions is to separate query to 2 select statements, so i think it would be like this:

@Query(
"SELECT p FROM PostDAO p " +
"WHERE LOWER (p.text) LIKE LOWER (%:search%) " +
"AND LOWER (p.user.institution.name) LIKE LOWER (%:orgname%) " +
"AND LOWER(p.user.institution.address) LIKE LOWER(%:address%) " +

"SOME WORD THAT COMBINE IT " +

"SELECT p FROM PostDAO p " +
"WHERE LOWER (p.text) LIKE LOWER (%:search%) " +
"AND LOWER(p.user.company.companyName) LIKE LOWER(%:orgname%) " +
"AND LOWER(p.user.company.address) LIKE LOWER(%:address%)")
Page<PostDAO> search(
    @Param("search") String search,
    @Param("orgname") String orgName,
    @Param("address") String address, 
    Pageable pageable);
0

There are 0 best solutions below