Hi Luis, Sorry, the re-written query string : "and (r = :a or r = :u)" that I suggested is incorrect in syntax. The bottom line is that "r" that ranges over an element collection is not a valid state_field_path_expression that appears as an operand in comparison expressions.
Catalina On Wed, Jun 3, 2009 at 5:03 AM, Luis Fernando Planella Gonzalez < [email protected]> wrote: > Well, it seems to be a matter of interpretation here, because > @PersistentCollection is also not in the specification, but without a proper > query support, it seems like it's "half implemented"... > > > Anyway, when trying to run your suggestion, I've noticed something really > strange: simply no filter is applied. > Here is the code: > > > public static void main(String[] args) { > EntityManagerFactory emf = > Persistence.createEntityManagerFactory("query-test"); > EntityManager em = emf.createEntityManager(); > em.getTransaction().begin(); > em.persist(new User("admin", Role.ADMIN)); > em.persist(new User("guest", Role.GUEST)); > em.persist(new User("user", Role.USER)); > em.persist(new User("mix", Role.ADMIN, Role.USER)); > em.persist(new User("all", Role.values())); > > Query query = em.createQuery("select u" + > " from User u " + > " where exists (" + > " select u2.username" + > " from User u2 inner join u2.roles r" + > " where u2 = u" + > " and (r = :a or r = :u)" + > " ) order by u.username"); > query.setParameter("a", Role.ADMIN); > query.setParameter("u", Role.USER); > > for (User user : (List<User>) query.getResultList()) { > System.out.println(user.getUsername()); > } > > em.getTransaction().rollback(); > } > > > The result I have is: > admin > all > guest > mix > user > > > This means that it's simply not filtering by role. I've even tried to > replace the "and (r = :a or r = :u)" part by "and r = :a", and had the same > result. > > > I'm attaching an eclipse project with this test. To run it, you should set > the correct location in project for openjpa-1.2.1.jar and > mysql-connector*.jar, and create a mysql database named test (or change this > in persistence,xml). > > -- > Luis Fernando Planella Gonzalez > > > Em Terça-feira 02 Junho 2009, às 19:18:26, catalina wei escreveu: > > > 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 < > > [email protected]> 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 < > [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 > > > > > > > > > > > > > > > > > > > > > > > > > > >
