QueryDSL and SQL Function from Postgres

Question

I would like to use Postgres FullText search with QueryDsl JPA.

Generated in SQL:

select * 
from film 
where to_tsquery ('tarzan') @@ to_tsvector('french',film.title) = true

to get all film containing tarzan in their title.

In JPA I define a custom function 'ftsMatch' which I could use like that:

String jpql = "select film from Film film where ftsMatch(:queryString, film.titre) = true";

I QueryDSL I would like to have an opportunity to define a predicate on String type :

QFilm.film.titre.ftsMatch('tarzan')

I haven't found any solution

Answer 1

What I would like to do is to extends the com.querydsl.core.types.dsl.StringExpression.class and add a custom function fullTextMatch() which could be used like :

BooleanBuilder booleanBuilder = new BooleanBuilder(QFilm.film.titre.fullTextMatch(_titre, "french"));

it would turn into SQL :

select film0_.id as id1_2_ .. from film film0_ 
where  to_tsquery (?) @@   to_tsvector('pg_catalog.french',film0_.titre)=true

I haven't found how to get the QueryDSL syntax above, but found the following solution:

1/ define Custom Dialect for Postgres and register the Customm function on this dialect :

public class CustomFullTextPostgresDialect extends PostgreSQL94Dialect {

    public CustomFullTextPostgresDialect() {
        registerFunction("ftsMatch", new PostgreSQLFullTextSearchFunction());
    }

}

2/ Code the custom function PostgreSQLFullTextSearchFunction implementing org.hibernate.dialect.function.SQLFunction This function 'ftsMacth' will generate the SQL :

String fragment = " to_tsquery (" + value + ") @@   to_tsvector(" + ftsConfig + "," + field + ")";

This step give me access to Posgres FullText in JPA :

String jpql = "select film from Film film "
        + "where FUNCTION( 'ftsMatch', :titre,'pg_catalog.french', film.titre) = true";
TypedQuery<Film> typedQuery = em.createQuery(jpql, Film.class);
typedQuery.setParameter("titre", _titre);
List<Film> list = typedQuery.getResultList();

3/Use QueryDsl to relay to the custom function defined on the extended postgres Dialect :

BooleanTemplate predicate = Expressions
        .booleanTemplate("FUNCTION('ftsMatch', {0},'pg_catalog.french', film.titre) = true ", _titre);

Page<Film> page = filmRepository.findAll(predicate, _pageable);

But with this QueryDSL solution, I still need the Hibernate customization. And the syntax is no longer DSL oriented