[NOT] EXISTS is an optimized way to build certain subquery based
conditions (e.g. those emulating outer joins). NOT IN will not work
as a substitute mainly because Cayenne Expressions do not support
correlated subqueries.
There are a few ways you can solve it. You can do it with two queries
if the user table is small and there is no performance hit:
// this should fetch all users that have at least one role:
Expression qualifier = Expression.fromString
("db:userRoleArray.username like '%'");
List users = ctxt.performQuery(new SelectQuery(User.class, qualifier));
// this should fetch all users
List allUsers = ctxt.performQuery(new SelectQuery(User.class));
// this should remove users that have any roles...
allUsers.removeAll(users);
... Or use SQLTemplate ...
Andrus
On Jul 1, 2005, at 11:06 AM, Mike Kienenberger wrote:
> Zvonimir Spajic <z.spaji..scensys.de> wrote:
>
>> I am trying out to build up an Expression for this SQL:
>>
>>
>> SELECT * FROM user t0
>> WHERE NOT EXISTS
>> (SELECT * FROM map_user_role t1
>> WHERE t0.username = t1.username)
>>
>>
>> What is the best way and which typ of Expression I have to use.
>> If there are more alternatives, please show me.
>>
>
> I'm not sure what "NOT EXISTS" means, but it sounds like it's the
> same as
> "NOT IN":
>
> public static Expression notInExp(String pathSpec, Object value)
This archive was generated by hypermail 2.0.0 : Fri Jul 01 2005 - 11:31:54 EDT