I appreciate your help, Adrian, but I'm gonna have to move on. I haven't been able to find the magic code example you've alluded to, and honestly can't think of how to get what I need without using COALESCE, UNION, or an OR condition in a LEFT JOIN - none of which seem to be supported.
No matter how I spin (even using 3 view-entities as you suggest) I can't get a single view-entity holding just a column of Person IDs and a column of PartyGroup IDs representing all EMPLOYEE -> EMPLOYER and EMPLOYER -> EMPLOYEE relationships. I'll either fall back on my 4 line code hack, or cobble something together in groovy. James On Thu, Feb 21, 2013 at 12:51 AM, Adrian Crum < [email protected]> wrote: > You will need three view entities: One for the "from" party, one for the > "to" party, and one to combine the first two view entities. There are > examples of that in code. > > -Adrian > > > On 2/21/2013 1:19 AM, James Piechota wrote: > >> FWIW: As near as I can tell, there's no way to do this. I'll see if I can >> cobble something together using two view-entities (one for each of the to >> and from relationships). >> >> >> On Sat, Feb 16, 2013 at 9:26 AM, James Piechota <[email protected]> >> wrote: >> >> Thanks Adrian. Yep, totally agree - in my first attempt, after using the >>> PartyRelationshipAndPartyDetai**l >>> view-entity as a starting point, I needed to add an OR clause in the join >>> to get what I needed (i.e., a table with only a single Person and >>> PartyGroup column, not a toPerson, fromPerson, toPartyGroup, >>> fromPartyGroup), but that required hacking. Now I'll try the >>> complex-alias >>> approach mentioned above. >>> >>> Thanks for your help, >>> >>> James >>> >>> >>> On Fri, Feb 15, 2013 at 11:22 PM, Adrian Crum < >>> adrian.crum@sandglass-**software.com<[email protected]>> >>> wrote: >>> >>> Exactly. So, copy the view entity and add what you need. >>>> >>>> -Adrian >>>> >>>> >>>> On 2/15/2013 7:24 PM, James Piechota wrote: >>>> >>>> Thanks, Adrian! >>>>> >>>>> Yeah, I'd looked at PartyRelationshipAndDetail view entity (actually >>>>> started my journey through the source there), but it seems like it only >>>>> considers the PartyRelationship.partyIdTo side of the connection, not >>>>> the >>>>> partyIdFrom. >>>>> >>>>> I'd like to build a list of Persons and their associated PartyGroups. >>>>> At >>>>> its most basic the list would contain just: >>>>> >>>>> PersonFirstName PersonLastName CompanyName >>>>> ... >>>>> >>>>> For all Persons. >>>>> >>>>> I'm not sure PartyRelationshipAndDetail will do that for me since the >>>>> relationships could be Person -> PartyGroup as well as PartyGroup -> >>>>> Person >>>>> and it only looks at the end of the relationship. i.e. It grabbs all >>>>> Persons and PartyGroups that sit on the terminal end of a relationship, >>>>> but >>>>> I believe ignores the start end of the relationship. >>>>> >>>>> James >>>>> >>>>> >>>>> On Fri, Feb 15, 2013 at 8:29 AM, Adrian Crum < >>>>> adrian.crum@sandglass-**softwa**re.com <http://software.com>< >>>>> adrian.crum@sandglass-**software.com<[email protected]> >>>>> >> >>>>> >>>>> wrote: >>>>> >>>>> Relationships go from Party to Party. Person and PartyGroup are Party >>>>> >>>>>> subtypes. So, if you want your code to work with all Party subtypes, >>>>>> you >>>>>> need a view entity that includes both Person and PartyGroup. The >>>>>> PartyRelationshipAndDetail view entity is a good example. >>>>>> >>>>>> -Adrian >>>>>> >>>>>> >>>>>> >>>>>> On 2/15/2013 3:47 PM, James Piechota wrote: >>>>>> >>>>>> Update: >>>>>> >>>>>>> I may be able to so what I need using complex aliases (basically map >>>>>>> a >>>>>>> fromPerson and toPerson column to a single person column - assuming >>>>>>> that >>>>>>> only one of the two will be non-null for a given row). >>>>>>> >>>>>>> I'll give it a go. >>>>>>> >>>>>>> James >>>>>>> >>>>>>> On Thursday, February 14, 2013, James Piechota wrote: >>>>>>> >>>>>>> Thank you both for the replies! >>>>>>> >>>>>>> I completely agree: I'd love to avoid hacking as much as possible! >>>>>>>> Maybe >>>>>>>> my searching skills need some help because these are the issues I >>>>>>>> hit >>>>>>>> after >>>>>>>> combing through the source: >>>>>>>> >>>>>>>> 1. >>>>>>>> I believe the relationships can go either Person -> PartyGroup or >>>>>>>> PartyGroup -> Person - is that right? To simplify use of the >>>>>>>> view-entity, >>>>>>>> I'd like to just have single "person" and "partyGroup" fields (as >>>>>>>> opposed >>>>>>>> to the toPerson, fromPerson, toPartyGroup, fromPartyGroup fields >>>>>>>> used >>>>>>>> in >>>>>>>> the scrum PartyRelationshipAndPartyDetai******l entitymodel >>>>>>>> example) >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> 2. >>>>>>>> We'll be tracking this employment relationship for customers and >>>>>>>> other >>>>>>>> external contacts and so I don't think I can rely on the human >>>>>>>> resource >>>>>>>> tables. >>>>>>>> >>>>>>>> I guess what it boils down to is: >>>>>>>> >>>>>>>> A. Are my search skills crappy, and there does in fact exist an >>>>>>>> example >>>>>>>> of >>>>>>>> how to query the employment relationship without needing both to and >>>>>>>> from >>>>>>>> fields for both parties? (if so, I'll keep looking!) >>>>>>>> >>>>>>>> B. Have I misunderstood something fundamental, and there's another >>>>>>>> way to >>>>>>>> get at what I need. >>>>>>>> >>>>>>>> Thanks again for the replies! >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Feb 14, 2013 at 2:20 AM, Adrian Crum < >>>>>>>> adrian.crum@sandglass-****softwa**re.com <http://software.com>< >>>>>>>> adrian.crum@sandglass-**softwa**re.com <http://software.com>< >>>>>>>> adrian.crum@sandglass-**software.com<[email protected]> >>>>>>>> > >>>>>>>> >>>>>>>>> <javascript:_e({}**, 'cvml', >>>>>>>>> >>>>>>>> 'adrian.crum@sandglass-****softw**are.com <http://software.com>< >>>>>>>> >>>>>>>> adrian.crum@sandglass-**softwa**re.com <http://software.com>< >>>>>>>> adrian.crum@sandglass-**software.com<[email protected]> >>>>>>>> > >>>>>>>> >>>>>>>>> ');>> >>>>>>>>> >>>>>>>> wrote: >>>>>>>> >>>>>>>> James, >>>>>>>> >>>>>>>> One thing to always remember: Whatever you are trying to do, there >>>>>>>>> is a >>>>>>>>> good chance someone else has already done it. Looking up a party >>>>>>>>> relationship is a very common requirement, so there is no need to >>>>>>>>> hack >>>>>>>>> up >>>>>>>>> the source code to do it. Just spend some time looking at the >>>>>>>>> current >>>>>>>>> implementations - chances are it already exists. >>>>>>>>> >>>>>>>>> -Adrian >>>>>>>>> >>>>>>>>> >>>>>>>>> On 2/14/2013 9:39 AM, Malin Nicolas wrote: >>>>>>>>> >>>>>>>>> Hi >>>>>>>>> >>>>>>>>> You can create a view-entity between Person - PartyRelationship - >>>>>>>>>> PartyGroup with non optional relation and a entity-condition on >>>>>>>>>> partyRelationshipTypeId = EMPLOYMENT >>>>>>>>>> See applications/humanres/********entitydef/entitymodel.xml for >>>>>>>>>> >>>>>>>>>> example. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Nicolas >>>>>>>>>> >>>>>>>>>> Le 14/02/2013 00:09, James Piechota a écrit : >>>>>>>>>> >>>>>>>>>> Hi, >>>>>>>>>> >>>>>>>>>> What's the recommended approach to getting a list of Persons and >>>>>>>>>>> the >>>>>>>>>>> Party >>>>>>>>>>> Group that they are in an EMPLOYMENT relationship with? >>>>>>>>>>> >>>>>>>>>>> Some context: >>>>>>>>>>> >>>>>>>>>>> Since a Person can be on either end of a PartyRelationship the >>>>>>>>>>> SQL >>>>>>>>>>> query >>>>>>>>>>> I've cooked up involves Left Joins with OR conditions. >>>>>>>>>>> >>>>>>>>>>> I've been unable to do this with a view-entity since the >>>>>>>>>>> view-links >>>>>>>>>>> seem to >>>>>>>>>>> require at least one AND in any boolean condition (i.e., they >>>>>>>>>>> require >>>>>>>>>>> at >>>>>>>>>>> least one key-map which gets AND'ed with any provided >>>>>>>>>>> entity-conditions). >>>>>>>>>>> >>>>>>>>>>> I've edited my local install to relax the view-link requirements >>>>>>>>>>> so >>>>>>>>>>> that >>>>>>>>>>> they just require *some* condition whether from a key-map, >>>>>>>>>>> entity-condition >>>>>>>>>>> or both. If there isn't a recommended approach to the above, I >>>>>>>>>>> can >>>>>>>>>>> look >>>>>>>>>>> into opening a JIRA issue and attaching a patch. >>>>>>>>>>> >>>>>>>>>>> For reference, this is the sort of SQL query I've been trying to >>>>>>>>>>> build: >>>>>>>>>>> >>>>>>>>>>> select PERSON.FIRST_NAME, PARTY_GROUP.GROUP_NAME >>>>>>>>>>> from PERSON >>>>>>>>>>> left outer join PARTY_RELATIONSHIP >>>>>>>>>>> on (PERSON.PARTY_ID = PARTY_RELATIONSHIP.PARTY_ID_**** >>>>>>>>>>> ****FROM >>>>>>>>>>> or >>>>>>>>>>> PERSON.PARTY_ID = PARTY_RELATIONSHIP.PARTY_ID_********TO) and >>>>>>>>>>> PARTY_RELATIONSHIP.PARTY_********RELATIONSHIP_TYPE_ID = >>>>>>>>>>> 'EMPLOYMENT' >>>>>>>>>>> >>>>>>>>>>> left outer join PARTY_GROUP >>>>>>>>>>> on (PARTY_GROUP.PARTY_ID = PARTY_RELATIONSHIP.PARTY_ID_** >>>>>>>>>>> **** >>>>>>>>>>> **FROM >>>>>>>>>>> or >>>>>>>>>>> PARTY_GROUP.PARTY_ID = PARTY_RELATIONSHIP.PARTY_ID_********TO) >>>>>>>>>>> and >>>>>>>>>>> PARTY_RELATIONSHIP.PARTY_********RELATIONSHIP_TYPE_ID = >>>>>>>>>>> 'EMPLOYMENT' >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >
