Hello Kevin,
I’ll take a swing at a new qualifier for you.
Verbally:
A profile sometimes points directly to the account I want. Other times, the
primary account for a profile is NOT the one I want but it has teams which
vicariously point back to the account I want. Give me all those profiles.
Style notes (entirely optional):
Try to avoid starting with “ERXQ.anything” such as “ERXQ.or()” and
“ERXQ.keypath()” Technically it is ok but it’s harder to rest and disrupts the
visual flow of the code.
Don’t use “_KEY” - It’s easier to just call “.key()” when you need it as you’ll
soon see.
Code sniff:
Anytime you have a to-many relationship involved, consider an “exists”
qualifier.
Utility method:
You need a way to “qualify yourself.” Your primary keys are probably hidden
(rightly so). You don’t want to qualify on “name”, “title”, “description" or
anything else since you already have the Account object you want in hand. So
put this utility method either in your “Account” EO or preferably in your
abstract parent class if you have one (between “Account” and
“ERXGenericRecord”):
public EOQualifier identityQualifier() {
EOEntity entity =
ERXEOAccessUtilities.entityNamed(editingContext(), entityName());
EOQualifier qualifier =
entity.qualifierForPrimaryKey(primaryKeyDictionary(false /*inTransaction*/));
return qualifier;
}
The new code:
// Generates a qualifier for the Profile for this account and the Profiles of
any account on the same team (or that is the intention)
public static EOQualifier visibleToAccountQualifier(Account account)
{
EOQualifier accountIsDirectlyTheOneIWantQualifier =
account.identityQualifier();
EOQualifier accountHasTeamForTheOneIWantQualifier = new
ERXExistsQualifier (
account.identityQualifier() /*subQualifier*/,
Account.TEAMS.dot(Team.ACCOUNTS).key() /*baseKeyPath*/,
);
EOQualifier finalAccountQualifier = new ERXOrQualifier(
accountIsDirectlyTheOneIWantQualifier,
accountHasTeamForTheOneIWantQualifier);
EOQualifier profileQualifier = new ERXExistsQualifier (
finalAccountQualifier /*subQualifier*/,
Profile.ACCOUNT.key() /*baseKeyPath*/, /*Note: this is a to-one
relationship, but that’s ok*/
);
return profileQualifier;
}
Wrap-up:
Note: be sure to either update Wonder or at least “cherry pick" the latest
version of the ExistsQualifier.
Kevin give this a try and report back. It’s funny how the ExistsQualifier can
solve 90% of the complex data-mining queries we could encounter. This is
especially true for “to-many” relationships.
You can “flip” the “exists” sql into an “in” with an additional boolean
parameter. Play with the true-false value of “useInClauseInstead” to tune
performance. If you want to learn more then watch the slides here:
http://www.chatnbike.com/presentation_existsQualifier/
You’ll be able to lick this, don’t worry. Dig in :-)
Cheers,
AARON ROSENZWEIG / Chat 'n Bike
e: [email protected] t: (301) 956-2319
On Apr 14, 2014, at 4:49 PM, Kevin Hinkson <[email protected]> wrote:
> Hello,
>
> I was hoping someone would be able to point me in the direction of how to
> handle generating a to many qualifier that involves a key path (not just a
> single key).
>
> A code example:
>
> // Generates a qualifier for the Profile for this account and the Profiles of
> any account on the same team (or that is the intention)
> public static EOQualifier visibleToAccountQualifier(Account account)
> {
> EOQualifier profilesDirectlyTiedToAccount =
> Profile.ACCOUNT.eq(account);
> String teamProfilesKeyPath = ERXQ.keyPath(Profile.ACCOUNT_KEY,
> Account.TEAMS_KEY, Team.ACCOUNTS_KEY);
> EOQualifier teamProfilesQualifier = ERXQ.has(teamProfilesKeyPath, new
> NSArray<Account>(account));
> return ERXQ.or(profilesDirectlyTiedToAccount, teamProfilesQualifier);
> }
>
> Performing a fetch with this qualifier generates the following error.
>
> EvaluateExpression failed: <com.webobjects.jdbcadaptor.PostgresqlExpression:
> "SELECT DISTINCT t0.id FROM Profile t0 INNER JOIN Account T1 ON t0.accountID
> = T1.id INNER JOIN AccountTeam T2 ON T1.id = T2.accountId INNER JOIN Team T3
> ON T2.teamId = T3.id INNER JOIN AccountTeam T4 ON T3.id = T4.teamId WHERE
> (T4.teamId IN ( SELECT Team.id FROM Team,AccountTeam null WHERE
> Team.id=T4.teamId AND null.accountId IN (1) GROUP BY Team.id HAVING
> COUNT(*)=1 ) OR t0.accountID = ?::int8)" withBindings: 1:1(accountID)>:
> Next exception:SQL State:42601 -- error code: 0 -- msg: ERROR: syntax
> error at or near "null"
> Position: 281.
>
> There is a null in there where there shouldn't be one (on the inner select).
> Am I using this qualifier incorrectly or is this indicative of a modelling
> error or even a bug? If it helps, I'm using PostgreSQL.
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected])
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/aaron%40chatnbike.com
>
> This email sent to [email protected]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
