Hi,
According to the JPQL BNL, in_expr is defined as:
in_expression ::= state_field_path_expression [ NOT ] IN ( in_item {, in_item}*
| subquery)
state_field_path_expression ::= {identification_variable |
single_valued_association_path_expression}.state_field
You might want to modify your query as follows:
select u
from User u
where exists (
select u2
from User u2 inner join u2.roles r
where u2 = u
and r.id in (:role1, :role2, :roleN)
Hope this helps.
Fay
[1]
http://openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/manual/jpa_langref.html#jpa_langref_bnf
--- On Fri, 5/29/09, Luis Fernando Planella Gonzalez <[email protected]> wrote:
> From: Luis Fernando Planella Gonzalez <[email protected]>
> Subject: Using jpql IN over element collections
> To: [email protected]
> Date: Friday, May 29, 2009, 5:17 AM
> Hi.
> I'm using OpenJPA 1.2.1, and have the following entity:
> @Entity
> public class User {
> ...
> @PersistentCollection(elementCascade =
> CascadeType.ALL)
> @ContainerTable(name = "users_roles",
> joinColumns = @XJoinColumn(name = "user_id"))
> @ElementColumn(name = "role")
> private Set<Role>
>
> roles
> = new
> HashSet<Role>();
> ...
> }
>
> I can use:
> select u
> from User u
> where :role member of u.roles
>
> However, I need to test several roles, and I'd like to do
> this:
> select u
> from User u
> where exists (
> select r
> from u.roles r
> where r in (:role1, :role2,
> :roleN)
> )
>
> But I have an error:
> org.apache.openjpa.persistence.ArgumentException:
> Encountered "exists ( select r from User u2 inner join u2 .
> roles r where u2 = u and r in" at character 7, but expected:
> ["(", ")", "+", ",", "-", ".", ":", "<>", "=", "?",
> "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN",
> "BOTH", "BY", "CONCAT", "COUNT", "CURRENT_DATE",
> "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
> "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM",
> "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING",
> "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX",
> "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT",
> "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE",
> "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM",
> "UPDATE", "UPPER", "WHERE", , , , , ].
>
> This seems to be a bug, because the following query runs
> (but is useless, since there's no role filter)
> select u
> from User u
> where exists (
> select u2
> from User u2 inner join
> u2.roles r
> where u2 = u
> )
>
> And the following query returns exactly the same error
> above (note the only change to the previous query was the IN
> operator:
> select u
> from User u
> where exists (
> select u2
> from User u2 inner join
> u2.roles r
> where u2 = u
> and r in (:role1, :role2,
> :roleN)
> )
>
> Does someone know some way I could do this without several
> MEMBER OF tests (each generating a subquery)?
> For this specific case, an user shouldn't have more than 3
> roles, but other cases I have with @PersistentCollection,
> elements may be filtered using several values...
>
> Any luck?
>
> Luis Fernando Planella Gonzalez
>
