Thanks for your help Aaron, Your identity qualifier suggestion was extremely helpful. I was entirely unaware of the EOEntity method qualifierForPrimaryKey so learning about that makes so many things much easier.
I've updated to the latest WebObjects changes in the master branch. I think I'm starting to get the hang of the ERXExistsQualifier, after taking a look at your example and at the presentation here http://www.chatnbike.com/presentation_existsQualifier/. I'm still having a problem with one aspect of it — Account.TEAMS.dot(Team.ACCOUNTS).key() this key path is a flattened to many relationship that is "looking back on itself" if that makes any sense, and the EOSQLExpression class has some difficulty generating a SQL statement that will work for that situation. It always seems to happen no matter how I arrange it as long as I'm trying to work with a to many relationship in that way. Here's the stack trace that generates: ERROR 06:21:28 (com.webobjects.eoaccess.ERXEntityDependencyOrderingDelegate:101) -Unexpected non-EOGeneralAdaptorException exception java.lang.NullPointerException at er.extensions.eof.qualifiers.ERXExistsQualifier$ExistsQualifierSQLGenerationSupport.sqlStringForSQLExpression(ERXExistsQualifier.java:256) at com.webobjects.eoaccess.EOQualifierSQLGeneration$Support._sqlStringForSQLExpression(EOQualifierSQLGeneration.java:165) at com.webobjects.eoaccess.EOSQLExpression.prepareSelectExpressionWithAttributes(EOSQLExpression.java:997) at com.webobjects.jdbcadaptor.JDBCExpression.prepareSelectExpressionWithAttributes(JDBCExpression.java:146) at com.webobjects.jdbcadaptor.PostgresqlExpression.prepareSelectExpressionWithAttributes(PostgresqlExpression.java:770) at er.extensions.eof.qualifiers.ERXExistsQualifier$ExistsQualifierSQLGenerationSupport.sqlStringForSQLExpression(ERXExistsQualifier.java:266) at com.webobjects.eoaccess.EOQualifierSQLGeneration$Support._sqlStringForSQLExpression(EOQualifierSQLGeneration.java:165) at com.webobjects.eoaccess.EOSQLExpression.sqlStringForArrayOfQualifiers(EOSQLExpression.java:1528) at com.webobjects.eoaccess.EOSQLExpression.sqlStringForDisjoinedQualifiers(EOSQLExpression.java:1574) at com.webobjects.eoaccess.EOQualifierSQLGeneration$_OrQualifierSupport.sqlStringForSQLExpression(EOQualifierSQLGeneration.java:578) at com.webobjects.eoaccess.EOQualifierSQLGeneration$Support._sqlStringForSQLExpression(EOQualifierSQLGeneration.java:165) at com.webobjects.eoaccess.EOSQLExpression.prepareSelectExpressionWithAttributes(EOSQLExpression.java:997) at com.webobjects.jdbcadaptor.JDBCExpression.prepareSelectExpressionWithAttributes(JDBCExpression.java:146) at com.webobjects.jdbcadaptor.PostgresqlExpression.prepareSelectExpressionWithAttributes(PostgresqlExpression.java:770) at com.webobjects.eoaccess.EOSQLExpressionFactory.selectStatementForAttributes(EOSQLExpressionFactory.java:225) at com.webobjects.jdbcadaptor.JDBCChannel.selectAttributes(JDBCChannel.java:213) at er.extensions.jdbc.ERXJDBCAdaptor$Channel.selectAttributes(ERXJDBCAdaptor.java:203) at com.webobjects.eoaccess.EODatabaseChannel._selectWithFetchSpecificationEditingContext(EODatabaseChannel.java:897) at com.webobjects.eoaccess.EODatabaseChannel.selectObjectsWithFetchSpecification(EODatabaseChannel.java:234) at com.webobjects.eoaccess.EODatabaseContext._objectsWithFetchSpecificationEditingContext(EODatabaseContext.java:3055) at com.webobjects.eoaccess.EODatabaseContext.objectsWithFetchSpecification(EODatabaseContext.java:3195) at com.webobjects.eocontrol.EOObjectStoreCoordinator.objectsWithFetchSpecification(EOObjectStoreCoordinator.java:488) at com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4069) at er.extensions.eof.ERXEC.objectsWithFetchSpecification(ERXEC.java:1308) at com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4444) at co.coralstone.apparatus.synchronization.data.eo._Profile.fetchProfiles(Unknown Source) at co.coralstone.apparatus.synchronization.service.Application.finishInitialization(Unknown Source) at er.extensions.appserver.ERXApplication.finishInitialization(ERXApplication.java:1296) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.webobjects.foundation.NSSelector._safeInvokeMethod(NSSelector.java:122) at com.webobjects.foundation.NSNotificationCenter$_Entry.invokeMethod(NSNotificationCenter.java:588) at com.webobjects.foundation.NSNotificationCenter.postNotification(NSNotificationCenter.java:532) at com.webobjects.foundation.NSNotificationCenter.postNotification(NSNotificationCenter.java:546) at com.webobjects.appserver.WOApplication.run(WOApplication.java:1229) at er.extensions.appserver.ERXApplication.run(ERXApplication.java:1417) at com.webobjects.appserver.WOApplication.main(WOApplication.java:548) at er.extensions.appserver.ERXApplication.main(ERXApplication.java:861) at co.coralstone.apparatus.synchronization.service.Application.main(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.webobjects._bootstrap.WOBootstrap.main(WOBootstrap.java:87) The following qualifier does work. I'm not sure it is ideal but it works and I'll probably evolve it as I gain a better understanding of how the Exists Qualifier works. public static EOQualifier visibleToAccountQualifier(Account account) { EOQualifier qualifier = null; NSArray<Team> teams = account.teams(); if (true == teams.isEmpty()) { // if the account is not a member of any teams, then just get the profiles for this account qualifier = account.identityQualifier(false); } else { // otherwise since we have the account, use their teams in the qualifier instead of traversing the to-many as before qualifier = ERXQ.has(Account.TEAMS_KEY, account.teams()); } return new ERXExistsQualifier(qualifier, Profile.ACCOUNT.key()); } On 15 Apr 2014, at 12:30, Aaron Rosenzweig <[email protected]> wrote: > 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]
