Re: To-Many Qualifiers
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:) 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
Re: To-Many Qualifiers
Hi Kevin, I did poke inside the ERXExistsQualifier to see… seems that it doesn’t properly handle a “flattened” relationship. When you say “flattened” you mean there is another EO join table in there that is hidden correct? Yeah, it seems to be a shortcoming of ERXExistsQualifier. You *could* create some unflattened relationships and use those instead, then you could still use exists qualifier. While your new qualifier doesn’t throw an error, I don’t think it does what you want. Your new qualifier would probably work better like this: public static EOQualifier visibleToAccountQualifier(Account account) { EOQualifier qualifier = new ERXOrQualifier( account.identityQualifier(false), Account.TEAMS.has(account.teams()) ); return new ERXExistsQualifier(qualifier, Profile.ACCOUNT.key()); } Notice the subtle difference. The “identity” qualifier will happen all the time… you’ll want that because even if you have teams it might just be “you” directly. Wow, that is nice and easy. AND, now that I understand your situation better, makes sense. You traverse the flattened relationship right at “account.teams()” to get the set of teams that could possibly point back to your Account. Very nice. Should perform well. Now try the following: public static EOQualifier visibleToAccountQualifier(Account account) { boolean useInClauseInstead = false; EOQualifier qualifier = new ERXOrQualifier( account.identityQualifier(false), Account.TEAMS.has(account.teams()) ); return new ERXExistsQualifier(qualifier, Profile.ACCOUNT.key(), useInClauseInstead); } Play with the true / false value of “useInClauseInstead” to see what is faster for your data set. Have fun! :-) AARON ROSENZWEIG / Chat 'n Bike e: aa...@chatnbike.com t: (301) 956-2319 On Apr 16, 2014, at 3:13 PM, Kevin Hinkson k...@coralstone.co wrote: 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
Re: To-Many Qualifiers
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: aa...@chatnbike.com t: (301) 956-2319 On Apr 14, 2014, at 4:49 PM, Kevin Hinkson k...@coralstone.co 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 NSArrayAccount(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.
To-Many Qualifiers
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 NSArrayAccount(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 (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com