Just realized that I didn't really answer the question - how to do this.
Here is one solution - do in memory filtering:
Expression matchQual =
ExpressionFactory.matchExp("teamuserArray.userteamToTeams.teamname",
"xyz");
List users = ctxt.performQuery(new SelectQuery(Users.class, matchQual));
List allUsers = ctxt.performQuery(new SelectQuery(Userteam.class));
allUsers.removeAll(users);
This may not be as inefficient as it looks like if "users" list is small
in size.
Andrus
> Hi David,
>
>
>> Which I expected to return all users EXCEPT the users shown in the
>> last result set. Instead, I get back ALL users, except those who do
>> not belong to ANY team.
>
> just tried this out on MySQL. It works, but... Due to the relational
> logic specifics, "noMatchExp" returns all users who are at least in one
> of teams that do not match the criteria. If you turn on the SQL logging
> per
>
> http://objectstyle.org/cayenne/userguide/deploy/logging.html
>
> You will the SQL generated for the query:
>
> SELECT DISTINCT t0.pass, t0.username, t0.userid FROM USERS t0, USERTEAM
> t1, TEAMS t2 WHERE t0.userid = t1.users_userid AND t1.teams_teamid =
> t2.teamid AND (t2.teamname <> ?) [bind: 'a']
>
> There is indeed a mismatch between relational and common logic here :-).
>
> On a side note, you will need to check the "To Dep PK" checkbox in
> USERS.teamuserArray and TEAMS.teamuserArray relationships. This does not
> affect selectes, but will definitely affect inserts.
>
> Andrus
>
>
>
>> I’m a Cayenne newbie, building a small test app and am having trouble
>> with noMatchExp. I have an n:m relationship between Users and Teams,
>> stored in association table Userteam, which has two columns, a
>> composite primary key of teamid and userid.
>>
>> Using the following code, I can successfully find all the members of a
>> team:
>>
>> Expression includequal
>> ExpressionFactory.matchExp("teamuserArray.userteamToTeams.teamname",
>> teamname);
>> SelectQuery usersquery = new SelectQuery(Users.class, includequal);
>> List users = ctxt.performQuery(usersquery);
>>
>> So far so good. The query traverses the two relationships correctly.
>> Now, I’d like to also find all the members who do NOT belong to that
>> team, so I wrote this:
>>
>> Expression excludequal
>> ExpressionFactory.noMatchExp("teamuserArray.userteamToTeams.teamname",
>> teamname);
>> SelectQuery usersquery = new SelectQuery(Users.class, excludequal);
>> List exusers = ctxt.performQuery(usersquery);
>>
>> Which I expected to return all users EXCEPT the users shown in the
>> last result set. Instead, I get back ALL users, except those who do
>> not belong to ANY team. After much head-scratching, I cannot figure
>> out what the problem might be. Problem with my datamap? An anomaly
>> of MySQL? Is there some other way to get at the data I want?
>>
>> Any tips would be most appreciated.
>>
>> Environment is Tomcat 4.1 running against MySQL 4.0.13, with the
>> schema generated by the modeler.
>>
>>
>> And my datamap:
>>
>> <?xml version="1.0" encoding="UTF-8"?>
>> <data-map project-version="1.0">
>> <db-entity name="TEAMS">
>> <db-attribute name="teamdesc" type="CHAR"
>> length="255"/>
>> <db-attribute name="teamid" type="INTEGER"
>> isPrimaryKey="true" isMandatory="true" length="10"/>
>> <db-attribute name="teamname" type="CHAR"
>> isMandatory="true" length="50"/>
>> </db-entity>
>> <db-entity name="USERS">
>> <db-attribute name="pass" type="CHAR"
>> isMandatory="true" length="20"/>
>> <db-attribute name="userid" type="INTEGER"
>> isPrimaryKey="true" isMandatory="true" length="10"/>
>> <db-attribute name="username" type="CHAR"
>> isMandatory="true" length="50"/>
>> </db-entity>
>> <db-entity name="USERTEAM">
>> <db-attribute name="teams_teamid"
>> type="INTEGER"
>> isPrimaryKey="true" isMandatory="true" length="10"/>
>> <db-attribute name="users_userid"
>> type="INTEGER"
>> isPrimaryKey="true" isMandatory="true" length="10"/>
>> </db-entity>
>> <obj-entity name="Teams" className="com.db.Teams"
>> dbEntityName="TEAMS">
>> <obj-attribute name="teamdesc"
>> type="java.lang.String" db-attribute-name="teamdesc"/>
>> <obj-attribute name="teamname"
>> type="java.lang.String" db-attribute-name="teamname"/>
>> </obj-entity>
>> <obj-entity name="Users" className="com.db.Users"
>> dbEntityName="USERS">
>> <obj-attribute name="pass"
>> type="java.lang.String" db-attribute-name="pass"/>
>> <obj-attribute name="username"
>> type="java.lang.String" db-attribute-name="username"/>
>> </obj-entity>
>> <obj-entity name="Userteam" className="com.db.Userteam"
>> dbEntityName="USERTEAM">
>> </obj-entity>
>> <db-relationship name="teamuserArray" source="TEAMS"
>> target="USERTEAM" toDependentPK="false" toMany="true">
>> <db-attribute-pair source="teamid"
>> target="teams_teamid"/>
>> </db-relationship>
>> <db-relationship name="teamuserArray" source="USERS"
>> target="USERTEAM" toDependentPK="false" toMany="true">
>> <db-attribute-pair source="userid"
>> target="users_userid"/>
>> </db-relationship>
>> <db-relationship name="userteamToTeams" source="USERTEAM"
>> target="TEAMS" toDependentPK="false" toMany="false">
>> <db-attribute-pair source="teams_teamid"
>> target="teamid"/>
>> </db-relationship>
>> <db-relationship name="userteamToUsers" source="USERTEAM"
>> target="USERS" toDependentPK="false" toMany="false">
>> <db-attribute-pair source="users_userid"
>> target="userid"/>
>> </db-relationship>
>> <obj-relationship name="teamuserArray" source="Teams"
>> target="Userteam" toMany="true" deleteRule="Nullify">
>> <db-relationship-ref source="TEAMS"
>> target="USERTEAM" name="teamuserArray"/>
>> </obj-relationship>
>> <obj-relationship name="teamuserArray" source="Users"
>> target="Userteam" toMany="true" deleteRule="Nullify">
>> <db-relationship-ref source="USERS"
>> target="USERTEAM" name="teamuserArray"/>
>> </obj-relationship>
>> <obj-relationship name="userteamToTeams" source="Userteam"
>> target="Teams" toMany="false" deleteRule="Nullify">
>> <db-relationship-ref source="USERTEAM"
>> target="TEAMS" name="userteamToTeams"/>
>> </obj-relationship>
>> <obj-relationship name="userteamToUsers" source="Userteam"
>> target="Users" toMany="false" deleteRule="Nullify">
>> <db-relationship-ref source="USERTEAM"
>> target="USERS" name="userteamToUsers"/>
>> </obj-relationship>
>> </data-map>
>>
>>
>> ---
>> Outgoing mail is certified Virus Free.
>> Checked by AVG anti-virus system (http://www.grisoft.com).
>> Version: 6.0.488 / Virus Database: 287 - Release Date: 6/5/2003
This archive was generated by hypermail 2.0.0 : Mon Jun 16 2003 - 12:05:37 EDT