Micronaut data pagination full-text search query

Question

I'm using Micronaut Data version 1.0.2.

Given the following JPA entity class:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String code;
    private String name;
    private String fullName;
}

I can create full-text search query with the following method of PageableRepository:

@Repository
public interface ProductRepository extends PageableRepository<Product, Long> {
    Slice<Product> findByCodeLikeOrNameLikeOrFullNameLike(
        String code, String name, String fullName, Pageable pageable);
}

However, I have a problem to add one more criterion for the name property. What I want to achieve is equivalent to the following SQL:

select * from product
where code like '%search%' 
or (name like '%search%' and name not like '%-%')
or full_name like '%search%'

I tested the following ways:

Slice<Product> findByCodeLikeOrNameLikeAndNotLikeOrFullNameLike
Slice<Product> findByCodeLikeOrNameLikeAndNotContainsOrFullNameLike
Slice<Product> findByCodeLikeOrNameLikeAndNameNotLikeOrFullNameLike
Slice<Product> findByCodeLikeOrNameLikeAndNameNotContainsOrFullNameLike

Any idea how to make it works?

Thank in advanced.

Thanks for quick response. I found the statement "Note that if the method returns a Page for pagination then you must additionally specify a query that performs the equivalent count using the countQuery member of the `@Query` annotation." in the doc at https://micronaut-projects.github.io/micronaut-data/latest/guide/#explicitQueries. But I never use `@Query` before and couldn't found any sample code in the doc to perform the operation mentioned in the statement with pagination. Next, I will try to find some sample code on how to do so, appreciate your sharing if you have one. Thanks. — limcheekin, Aug 25 '20 at 03:41
for the countQuery have a look here: [count-query-for-micronaut-data-query](https://stackoverflow.com/questions/61340255/example-count-query-for-micronaut-data-query) — Dirk Deyne, Aug 25 '20 at 07:22

Dirk Deyne · Accepted Answer · 2020-08-25T15:29:29.540

If you use a @Query then this should be something like:

@Query(value = "select p from Product p where (p.code like :code or p.fullName like :fullName or p.name like :name) and p.name not like :ignoreName") 
Slice<Product> fullSearch(String code, String name, String ignoreName, String fullName, Pageable pageable);

here you can leave out the countQuery because you use Slice<Product>

If you want to use Page<Product> then the total number of elements needs to be available. So then you need to provide count query.

like:

countQuery = "select count(p.id) from Product p where (p.code like :code or p.fullName like :fullName or p.name like :name) and p.name not like :ignoreName")

Micronaut data pagination full-text search query

1 Answers1