Re: Problem with noMatchExp

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Mon Jun 16 2003 - 12:05:36 EDT

  • Next message: Andrus Adamchik: "[ANN] Cayenne Beta 4 is out"

    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