Postgres textsearch using tsquery

[Harshith]

Does Jooq support postgres text/document search functionality using tsquery? If so, is there an example that I can look up? I couldn't find anything in the manual or web search.

If not, what would be a suggested alternative to a query that uses textsearch.  For example what would be the easiest way to translate a conditional like:

field @@ 'match1|match2|match3:*'::tsquery

[Lukas Eder]

Hello,

PostgreSQL has quite a few of these interesting functions / data types / operators, that are not natively supported by jOOQ. As always, if something is not natively supported, your best choice is to resort to plain SQL:

http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/

E.g. your conditional expression would probably best be written as such.

DSL.condition("{0} @@ {1}::tsquery", field, DSL.val("match1|match2|match3:*"));

If you're planning on doing a lot of these text queries, you might make the above broadly available to your application through a reusable API.

In the long run, it might make sense to implement such support in jOOQ, though:

https://github.com/jOOQ/jOOQ/issues/2938

Cheers

Lukas

2014/1/8 Harshith <harshi...@gmail.com>

Does Jooq support postgres text/document search functionality using tsquery? If so, is there an example that I can look up? I couldn't find anything in the manual or web search.

If not, what would be a suggested alternative to a query that uses textsearch.  For example what would be the easiest way to translate a conditional like:

field @@ 'match1|match2|match3:*'::tsquery

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[harshi...@gmail.com]

Thanks for your reply Lucas.

I actually ended up creating this as a CustomField<> because i use it in several selects and conditional checks.

public class TsQueryField<R extends Record> extends CustomField<Boolean> {
  private Collection<String> queries;
  private TableField<R, Object> queryField;

  public TsQueryField(TableField<R, Object> field, Collection<String> queries) {
    super(field.getName(), new DefaultDataType(SQLDialect.POSTGRES, Boolean.FALSE.getClass(), "Boolean"));
    this.queryField = field;
    this.queries = queries;
  }

  public void toSQL(RenderContext context) {
    CastMode castMode = context.castMode();

    context.castMode(CastMode.NEVER).formatSeparator()
      .visit(queryField)
      .sql(" @@ ")
      .visit(inline(MiscUtils.join(queries, "|")))
      .sql("::tsquery")
      .castMode(castMode);
  }
}

However I am running into a problem when i try to check if this column is true. for example i am using:

.and(fnameTsQuery.isTrue().or(lnameTsQuery.isTrue()))

where both fnameTsQuery and lnameTsQuery variables are of type TsQueryField as defined above. This is generating SQL equivalent to the following:

"public"."test"."s_fnames" @@ 'hoover|greg'::tsquery = ?

I am unable to get rid of that ?. Is this a bug or am i doing something wrong here?

[Lukas Eder]

Hello,

2014/1/10 <harshi...@gmail.com>

Thanks for your reply Lucas.

I actually ended up creating this as a CustomField<> because i use it in several selects and conditional checks.

That's not a bad idea. This way, you stay in full control of your SQL

 

public class TsQueryField<R extends Record> extends CustomField<Boolean> {
  private Collection<String> queries;
  private TableField<R, Object> queryField;

  public TsQueryField(TableField<R, Object> field, Collection<String> queries) {
    super(field.getName(), new DefaultDataType(SQLDialect.POSTGRES, Boolean.FALSE.getClass(), "Boolean"));
    this.queryField = field;
    this.queries = queries;
  }

  public void toSQL(RenderContext context) {
    CastMode castMode = context.castMode();

    context.castMode(CastMode.NEVER).formatSeparator()
      .visit(queryField)
      .sql(" @@ ")
      .visit(inline(MiscUtils.join(queries, "|")))
      .sql("::tsquery")
      .castMode(castMode);
  }
}

However I am running into a problem when i try to check if this column is true. for example i am using:

.and(fnameTsQuery.isTrue().or(lnameTsQuery.isTrue()))

where both fnameTsQuery and lnameTsQuery variables are of type TsQueryField as defined above. This is generating SQL equivalent to the following:

"public"."test"."s_fnames" @@ 'hoover|greg'::tsquery = ?

I am unable to get rid of that ?. Is this a bug or am i doing something wrong here?

No, this is intended. As you can see in the implementation of isTrue(), this will generate a {0} = true predicate, where "true" is in fact a bind variable:

https://github.com/jOOQ/jOOQ/blob/861d43980a2577a212978f903881fe8d48312bb2/jOOQ/src/main/java/org/jooq/impl/AbstractField.java#L578

If you want to globally avoid bind variables, you could choose the relevant setting as documented here:

http://www.jooq.org/doc/3.2/manual/sql-building/bind-values/inlined-parameters/

Cheers

Lukas