Hi Luis,
Please take a look at my comment on the jira issue you have opened.
I have not tried this, but your IN expression may be re-written as:
.. where u2 = u
and ( r =:role1 or r = :role2 or r = :roleN )
Could you give it a try?
OpenJPA's implementation conforms to JPA1.0 Spec.
Catalina
On Mon, Jun 1, 2009 at 6:45 AM, Luis Fernando Planella Gonzalez <
lfpg....@gmail.com> wrote:
> 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
> > >
> >
> >
> >
> >
>
