usage of SetPath.any() and multiple predicates.

[Thomas Bruyelle]

Consider a JPA entity (QUser.user) which has a collection attribute of another entity (QUser.user.pets), if you want to request this entity on a field of this other entity, you can write your predicate like that with Querydsl :

QUser.user.pets.any().type.eq( 'dog' )

The where clause generated in JPQL :

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1)

Now if I want to base my request on multiple fields of the other entity, how can I do ? If I write something like that :

QUser.user.pets.any().type.eq( 'dog' ).and( QUser.user.pets.any().color.eq( 'red' ) )

The JPQL generated is not correct :

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1) AND exists (select pet from Pet pet where pet member of user.pets and pet.color = ?2)

The JPQL I would like to have :

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1 and pet.color = ?2)

How can I do with querydsl ?

[Thomas Bruyelle]

> The JPQL generated is not correct 

Would be The JPQL generated is not what I expected :)

[Timo Westkämper]

Hi.

I guess for this case you would need to write it as a subquery

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1 and pet.color = ?2)

becomes

subQuery.from(pet).where(pet.in(user.pets), pet.type.eq(...), pet.color.eq(...)).exists()

On Fri, Jun 15, 2012 at 6:47 PM, Querydsl on behalf of Thomas Bruyelle <quer...@googlegroups.com> wrote:

> The JPQL generated is not correct 

Would be The JPQL generated is not what I expected :)

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

[Timo Westkämper]

subQuery would be an instance of JPASubQuery http://www.querydsl.com/static/querydsl/2.6.0/apidocs/com/mysema/query/jpa/impl/JPASubQuery.html

Timo

[Thomas Bruyelle]

Awesome !! 

I tried the subqueries but without the exists() at the end, and I didn't manage to do what I want. Now with the usage of exists() it works like a charm !

Thanks Timo, once again you help me a lot

Have a nice day

Best regards

Le samedi 16 juin 2012 17:08:46 UTC+2, Timo Westkämper a écrit :

subQuery would be an instance of JPASubQuery http://www.querydsl.com/static/querydsl/2.6.0/apidocs/com/mysema/query/jpa/impl/JPASubQuery.html

Timo

On Sat, Jun 16, 2012 at 6:07 PM, Timo Westkämper <> wrote:

Hi.

I guess for this case you would need to write it as a subquery

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1 and pet.color = ?2)

becomes

subQuery.from(pet).where(pet.in(user.pets), pet.type.eq(...), pet.color.eq(...)).exists()

On Fri, Jun 15, 2012 at 6:47 PM, Querydsl on behalf of Thomas Bruyelle <> wrote:

> The JPQL generated is not correct 

Would be The JPQL generated is not what I expected :)

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

[Maciej Wojterski]

Hi Timo,

I'm wondering if after those couple of years, maybe there is a better solution now? The problem I have with suggested approach is that I couldn't find any subquery factory or generic implementation, which would allow me to separate from actual query implementation and since it is supposed to be sub-query, this information should not be necessary. Currently I have a class which provides predicates but should not be aware of executor (possibly many different) which will run them.

Best Regards,

MWt

W dniu sobota, 16 czerwca 2012 17:07:37 UTC+2 użytkownik Timo Westkämper napisał:

Hi.

I guess for this case you would need to write it as a subquery

exists (select pet from Pet pet where pet member of user.pets and pet.type = ?1 and pet.color = ?2)

becomes

subQuery.from(pet).where(pet.in(user.pets), pet.type.eq(...), pet.color.eq(...)).exists()

[timowest]

Maciej, do you use Querydsl 3 or 4?

[Maciej Wojterski]

Timo, I'm using Querydsl v. 4.0.2 from com.querydsl group.

[timowest]

Subqueries in Querydsl 4 are now detached queries, you can create them for JPA via the static methods in JPAExpressions, e.g. JPAExpressions.select/selectFrom/selectOne

[Maciej Wojterski]

I know, I can as well just use new JPAQuery(), but the point is, the code is just creating a predicate and it basically need only depend on querydsl-core. It'd be great if I could not say it's a JPA subquery, since I can use created predicate with many different executors like Collections or MongoDB. So I was just wondering if there is a way to create some generic SubQuery (not tied to any particular executor).