Very native queries in Querydsl

[Luis Fernando Planella Gonzalez]

Hi.
I'm using Postgresql with full text queries, and I'd like to know what
would be the most appropriate way (if even possible) to perform this
query with querydsl:

select u.*
from users u, to_tsquery('dictionary', 'query-string') query
where (u.name_tsvector || u.username_tsvector || u.email_tsvector) @@ query
order by ts_rank_cd(u.name_tsvector || u.username_tsvector ||
u.email_tsvector, query)desc

I'm using JPA, but I'll have to implement that as a native query, and
hopefully, querydsl will make things easier...

PS: Just in case you're not familiar, tsvectors may be concatenated
using || and matched against a query with the @@ operator. It is
important that the query is part of the from, so the matching and
ranking are done against the same query instance, and results are
optimized. My first attempt was to map all things to db functions, and
map those functions in querydsl as custom operators, effectively hiding
the @@ operator and ts_rank_cd functions inside custom db functions, and
some performance tests showed timings of 4.2 seconds for the custom
functions vs < 0,1 seconds for the native approach.

Best regards,
Luis.

[Luis Fernando Planella Gonzalez]

Sorry, I posted too early, and found a solution already.
Just to share some bits of it (can't post the source code, sorry):
As I'm using JPA, generating the database schema from entities (plus some custom sql script to handle native columns / functions / triggers).
Among those, I have some tsvector fields in Postgresql. Those are updated with triggers, so they are always in sync with their respective columns.
I was not using Querydsl for native sql queries so far. As generating the Q (or, in this case, S) types from schema is a no-go, because the schema is generated from entities, I decided to write by hand the S queries just for the tables involved on the native queries. Plus, I created a TsVector type (mostly empty, just to be type safe), TsVectorExpression (with custom methods / operators), TsVectorPath, TsVectorOperation and a STsQuery (extending RelationalPath, so it can be used in from clause). I also extended the Postgresql dialect to handle the operators, and to not quote the table name if it contains "to_tsquery". Additionally, I needed a custom type to be registered for tsvector columns.

Finally, I was able to do things like:
SUser u = new SUser("u");
STsQuery q = new STsQuery("q", "english", "term1 | term2");
SQLQuery query = new SQLQuery(configuration)
    .from(u, q)
    .where(u.nameTsVector.matches(q)
    .orderBy(u.nameTsVector.rank(q).desc());

Even though it would not be useful for my case, because I can't generate Q-types directly from the schema, maybe it would be nice to be able to generate database-specific types in Q-types, like tsvector in this case.
Another thing which could be nice (though the tsvector types wouldn't visible in my case, anyway) is to generate the S-types directly from JPA entities, without relying on the database to be present at build time.

Again, Querydsl rocks!

[Timo Westkämper]

Hi Luis.

On Tue, Oct 1, 2013 at 4:26 AM, Luis Fernando Planella Gonzalez via Querydsl <querydsl+noreply-APn2wQd1BDYGPhL...@googlegroups.com> wrote:

Sorry, I posted too early, and found a solution already.
Just to share some bits of it (can't post the source code, sorry):
As I'm using JPA, generating the database schema from entities (plus some custom sql script to handle native columns / functions / triggers).
Among those, I have some tsvector fields in Postgresql. Those are updated with triggers, so they are always in sync with their respective columns.
I was not using Querydsl for native sql queries so far. As generating the Q (or, in this case, S) types from schema is a no-go, because the schema is generated from entities, I decided to write by hand the S queries just for the tables involved on the native queries. Plus, I created a TsVector type (mostly empty, just to be type safe), TsVectorExpression (with custom methods / operators), TsVectorPath, TsVectorOperation and a STsQuery (extending RelationalPath, so it can be used in from clause). I also extended the Postgresql dialect to handle the operators, and to not quote the table name if it contains "to_tsquery". Additionally, I needed a custom type to be registered for tsvector columns.

Finally, I was able to do things like:
SUser u = new SUser("u");
STsQuery q = new STsQuery("q", "english", "term1 | term2");
SQLQuery query = new SQLQuery(configuration)
    .from(u, q)
    .where(u.nameTsVector.matches(q)
    .orderBy(u.nameTsVector.rank(q).desc());

Even though it would not be useful for my case, because I can't generate Q-types directly from the schema, maybe it would be nice to be able to generate database-specific types in Q-types, like tsvector in this case.

Good that you found a solution. You might be able to utilize this pattern also with Querydsl JPA native queries to get entity projections, if that's what you need.
 

Another thing which could be nice (though the tsvector types wouldn't visible in my case, anyway) is to generate the S-types directly from JPA entities, without relying on the database to be present at build time.

I am not sure if the JPA metamodel could be used for that.

Br,
Timo

 

Again, Querydsl rocks!

Em segunda-feira, 30 de setembro de 2013 15h11min25s UTC-3, Luis Fernando Planella Gonzalez escreveu:

Hi.
I'm using Postgresql with full text queries, and I'd like to know what
would be the most appropriate way (if even possible) to perform this
query with querydsl:

select u.*
from users u, to_tsquery('dictionary', 'query-string') query
where (u.name_tsvector || u.username_tsvector || u.email_tsvector) @@ query
order by ts_rank_cd(u.name_tsvector || u.username_tsvector ||
u.email_tsvector, query)desc

I'm using JPA, but I'll have to implement that as a native query, and
hopefully, querydsl will make things easier...

PS: Just in case you're not familiar, tsvectors may be concatenated
using || and matched against a query with the @@ operator. It is
important that the query is part of the from, so the matching and
ranking are done against the same query instance, and results are
optimized. My first attempt was to map all things to db functions, and
map those functions in querydsl as custom operators, effectively hiding
the @@ operator and ts_rank_cd functions inside custom db functions, and
some performance tests showed timings of 4.2 seconds for the custom
functions vs < 0,1 seconds for the native approach.

Best regards,
Luis.

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

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Luis Fernando Planella Gonzalez]

Hi Timo

Em 01-10-2013 06:48, Querydsl on behalf of Timo Westkämper escreveu:

Hi Luis.

On Tue, Oct 1, 2013 at 4:26 AM, Luis Fernando Planella Gonzalez via Querydsl <querydsl+noreply-APn2wQd1BDYGPhL...@googlegroups.com> wrote:

Sorry, I posted too early, and found a solution already.
Just to share some bits of it (can't post the source code, sorry):
As I'm using JPA, generating the database schema from entities (plus some custom sql script to handle native columns / functions / triggers).
Among those, I have some tsvector fields in Postgresql. Those are updated with triggers, so they are always in sync with their respective columns.
I was not using Querydsl for native sql queries so far. As generating the Q (or, in this case, S) types from schema is a no-go, because the schema is generated from entities, I decided to write by hand the S queries just for the tables involved on the native queries. Plus, I created a TsVector type (mostly empty, just to be type safe), TsVectorExpression (with custom methods / operators), TsVectorPath, TsVectorOperation and a STsQuery (extending RelationalPath, so it can be used in from clause). I also extended the Postgresql dialect to handle the operators, and to not quote the table name if it contains "to_tsquery". Additionally, I needed a custom type to be registered for tsvector columns.

Finally, I was able to do things like:
SUser u = new SUser("u");
STsQuery q = new STsQuery("q", "english", "term1 | term2");
SQLQuery query = new SQLQuery(configuration)
    .from(u, q)
    .where(u.nameTsVector.matches(q)
    .orderBy(u.nameTsVector.rank(q).desc());

Even though it would not be useful for my case, because I can't generate Q-types directly from the schema, maybe it would be nice to be able to generate database-specific types in Q-types, like tsvector in this case.

Good that you found a solution. You might be able to utilize this pattern also with Querydsl JPA native queries to get entity projections, if that's what you need.

Yes, exactly. I'm using query.getSql(StringTemplate.create("u.*")), and passing that to EntityManager.createNativeQuery(), also setting the parameters from bindings. As I'm not reflecting all columns, only those I need, the solution is to use the .* projection, as u.all() in this case would just list the columns mapped in my manual S-type.

 

Another thing which could be nice (though the tsvector types wouldn't visible in my case, anyway) is to generate the S-types directly from JPA entities, without relying on the database to be present at build time.

I am not sure if the JPA metamodel could be used for that.

In theory it could... The JPA providers are able to generate the schema from it - all tables / columns can be obtained from JPA classes. Of course, those custom tsvector fields wouldn't be reflected in this case. But people using the Entities-first approach, where the DB is generated from entities could benefit from this, as one would either need to have the schema in order to build the application (generating S-types on the fly) or the S-types would need to be committed to the source repository (SVN, GIT, ...), which is not ideal for generated classes.

Another thing: imagine I had a SUser (actually SUsers, because the table name is users) in this case, generated from the User JPA entity. If I had a manual custom SUsersExtended, which is constructed using u.getMetadata(), would both variables reflect the same alias in the query? In that way, in this example, I could just extend SUsers and add those custom fields. Would that work?

Br, Luis.

You received this message because you are subscribed to a topic in the Google Groups "Querydsl" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/querydsl/3RBi9Zf04wI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to querydsl+u...@googlegroups.com.