The conclusions on this subject: 1. The query I've posted runs the same way with or without the filter r = :a, without a warning. It at least should raise an exception. 2. It's not possible to use related collection elements (not entities) in subqueries. 3. For now (in OpenJPA 1.X), the only way to filter for more than one value is where ((:a member of u.roles) or (:m member of u.roles) or ...). However, as each of those generates a subquery, this is bad when there are lots of values. As JPA 2.0 has support for collections of elements, I hope this kind of query will be supported as well.
Luis Fernando Planella Gonzalez Em Sexta-feira 05 Junho 2009, às 13:31:23, catalina wei escreveu: > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
