Is it possible to use Criteria API to query with Postgres functions such as to_tsquery and to_tsvector?

[mindy-jump]

I am trying to implement full text search to one of my apis, here's an example postgres sql query:

SELECT *
FROM recipes
WHERE  to_tsvector('english', description || ' ' || name @@ to_tsquery('english', 'uni:*'); 

I've attempted to try and do the below within the code but noticed the where clause is only Critera.CriteriaStep type and needs to be chained with some operator to make it a Criteria:

Criteria criteria = Criteria.where("to_tsvector('english', " + VECTOR_FUNC + "@@ to_tsquery('english', 'sec:*');");
this.template
    .select(RecipeEntity.class)
    .matching(Query.query(criteria)).all();

Or would I have to do something like below? I am relatively new to using this framework and apologies if this has already gone answered.

databaseClient
    .sql("SELECT id, type FROM recipes WHERE  to_tsvector('english', description || ' ' || name @@ to_tsquery('english', :searchText")
    .bind("searchText", searchText)

[mp911de]

Right now, we do not support vendor-specific queries through the R2dbcEntityTemplate. The only way you could use this functionality is by using string-based queries on a repository, see https://docs.spring.io/spring-data/r2dbc/docs/current/reference/html/#r2dbc.repositories.queries

[Rahul-Bukuwarung]

@mp911de Any update on this?

The query i am trying to make is below, I can't simply use a @query because i have a lot more criteria that need to be added depending on user input. Any idea on how I can get this to work

select * 
from atable
where (name_ft @@ to_tsquery('t')) 

[mindy-jump]

@mp911de Any update on this?

The query i am trying to make is below, I can't simply use a @query because i have a lot more criteria that need to be added depending on user input. Any idea on how I can get this to work

select * 
from atable
where (name_ft @@ to_tsquery('t')) 

I had the same use case a year ago. We also had a couple of fields from a join table a user could also choose to filter from. I ended up just ditching the orm completely and just wrote a query builder specific to the search contract we had. Used the db client directly