openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fay Wang <fyw...@yahoo.com>
Subject Re: Using jpql IN over element collections
Date Fri, 29 May 2009 21:35:56 GMT

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.dev@gmail.com> wrote:

> From: Luis Fernando Planella Gonzalez <lfpg.dev@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
> 


      

Mime
View raw message