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 - 11:40:15 EDT