Well, quoting my first post: > 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) > )
Both queries seems almost identical, but here, Role is an enum, not an entity. As, I couldn't see anything wrong with the query, I've filed a jira issue: https://issues.apache.org/jira/browse/OPENJPA-1110 Thanks. Luis Fernando Planella Gonzalez Em Sexta-feira 29 Maio 2009, às 18:35:56, Fay Wang escreveu: > > 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 <lfpg....@gmail.com> > wrote: > > > From: Luis Fernando Planella Gonzalez <lfpg....@gmail.com> > > Subject: Using jpql IN over element collections > > To: users@openjpa.apache.org > > 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 > > > > > >
