Toplink IN subquery

Question

Hi all,

I hava 2 objects associate by oneToMany relationship("One Model can have many events").

I'm trying to make a subquery in ejbql to find models for one event, like this:

SELECT model 
FROM RegModelValue model 
WHERE :event IN (model.events)

.... but toplink doent recognize model alias and tell me "Internal Exception: line 1:129: unexpected token: model"

Any ideas ?

Thanks a lot by advance !

Answer 1

I think the order is wrong, the :event cannot be before a IN.

Try this :

    SELECT model 
    FROM RegModelValue model 
    JOIN model.events events
    WHERE events.id = :event

Answer 2

The syntax for your JPQL query should be:

SELECT model 
FROM RegModelValue model 
WHERE model.events IN (:event)

Below, the relevant section of the JPA 1.0 specification:

4.6.8 In Expressions

The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

in_expression ::=
    state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)
in_item ::= literal | input_parameter

The state_field_path_expression must have a string, numeric, or enum value. The literal and/or input_parameter values must be like the same abstract schema type of the state_field_path_expression in type. (See Section 4.12).

The results of the subquery must be like the same abstract schema type of the state_field_path_expression in type. Subqueries are discussed in Section 4.6.15, “Subqueries”.

Examples are:

o.country IN (’UK’, ’US’, ’France’) is true for UK and false for Peru, and is equivalent to the expression (o.country = ’UK’) OR (o.country = ’US’) OR (o.country = ’ France’).

o.country NOT IN (’UK’, ’US’, ’France’) is false for UK and true for Peru, and is equivalent to the expression NOT ((o.country = ’UK’) OR (o.country = ’US’) OR (o.country = ’France’)).

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If the value of a state_field_path_expression in an IN or NOT IN expression is NULL or unknown, the value of the expression is unknown.