List of all people and the company they work for
Locked
List of all people and the company they work for
 
|
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' |
Re: List of all people and the company they work for
|
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' > |
Re: List of all people and the company they work for
|
|
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' >> > |
Re: List of all people and the company they work for
|
|
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 PartyRelationshipAndPartyDetail 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 < [hidden email]> 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' >>> >>> >> > |
Re: List of all people and the company they work for
|
|
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 PartyRelationshipAndPartyDetail 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 < > [hidden email] <javascript:_e({}, 'cvml', > '[hidden email]');>> 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' >>>> >>>> >>> >> > |
Re: List of all people and the company they work for
|
|
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 PartyRelationshipAndPartyDetail 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 < >> [hidden email] <javascript:_e({}, 'cvml', >> '[hidden email]');>> 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' >>>>> >>>>> |
Re: List of all people and the company they work for
|
|
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 < [hidden email]> 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-**software.com<[hidden email]><javascript:_e({}, 'cvml', >>> 'adrian.crum@sandglass-**software.com<[hidden email]>');>> >>> 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' >>>>>> >>>>>> >>>>>> > |
Re: List of all people and the company they work for
|
|
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 < > [hidden email]> 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-**software.com<[hidden email]><javascript:_e({}, 'cvml', >>>> 'adrian.crum@sandglass-**software.com<[hidden email]>');>> >>>> 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' >>>>>>> >>>>>>> >>>>>>> |
Re: List of all people and the company they work for
|
|
Thanks Adrian. Yep, totally agree - in my first attempt, after using
the PartyRelationshipAndPartyDetail 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 < [hidden email]> 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-**software.com <[hidden email]>> >> 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-**software.com<[hidden email]> >>>>> ><javascript:_e({}**, 'cvml', >>>>> 'adrian.crum@sandglass-**softw**are.com <http://software.com>< >>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>> >');>> >>>>> 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' >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> > |
Re: List of all people and the company they work for
|
|
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 <[hidden email]> wrote: > Thanks Adrian. Yep, totally agree - in my first attempt, after using the PartyRelationshipAndPartyDetail > 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 < > [hidden email]> 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-**software.com<[hidden email]>> >>> 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-**software.com<[hidden email]> >>>>>> ><javascript:_e({}**, 'cvml', >>>>>> 'adrian.crum@sandglass-**softw**are.com <http://software.com>< >>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>> >');>> >>>>>> 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' >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >> > |
Re: List of all people and the company they work for
|
|
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 <[hidden email]> wrote: > >> Thanks Adrian. Yep, totally agree - in my first attempt, after using the PartyRelationshipAndPartyDetail >> 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 < >> [hidden email]> 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-**software.com<[hidden email]>> >>>> 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-**software.com<[hidden email]> >>>>>>>> <javascript:_e({}**, 'cvml', >>>>>>> 'adrian.crum@sandglass-**softw**are.com <http://software.com>< >>>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>>>> ');>> >>>>>>> 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' >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> |
Re: List of all people and the company they work for
|
|
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 < [hidden email]> 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 <[hidden email]> >> 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<[hidden email]>> >>> 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<[hidden email]> >>>>> >> >>>>> >>>>> 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<[hidden email]> >>>>>>>> > >>>>>>>> >>>>>>>>> <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<[hidden email]> >>>>>>>> > >>>>>>>> >>>>>>>>> ');>> >>>>>>>>> >>>>>>>> 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' >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> > |
Re: List of all people and the company they work for
|
|
Solved (I couldn't let it go). For anyone else looking to do something
similar, the following will create a view-entity with two columns (person and group) and populated with all EMPLOYMENT relationships (you can adjust the condition at the end to grab other relationships). Main reason I've needed to do this is to simplify dependent code - without this much of my code would need to be duplicated to handle both person -> group and group -> person relationships. (FYI: if you're using mysql, like I am, you'll have to set sql_mode=PIPES_AS_CONCAT so that the || operator is treated as a concat). <view-entity entity-name="XXX" package-name="org.ofbiz.xxx" title="XXX"> <!-- SELECT (coalesce(FROMPERS.PARTY_ID, "") || coalesce(TOPERS.PARTY_ID, "")) as Person, (coalesce(FROMGRP.PARTY_ID, "") || coalesce(TOGRP.PARTY_ID, "")) as PartyGroup FROM PARTY_RELATIONSHIP REL LEFT OUTER JOIN PERSON FROMPERS ON FROMPERS.PARTY_ID = REL.PARTY_ID_FROM LEFT OUTER JOIN PARTY_GROUP TOGRP ON TOGRP.PARTY_ID = REL.PARTY_ID_TO LEFT OUTER JOIN PARTY_GROUP FROMGRP ON FROMGRP.PARTY_ID = REL.PARTY_ID_FROM LEFT OUTER JOIN PERSON TOPERS ON TOPERS.PARTY_ID = REL.PARTY_ID_TO WHERE coalesce(FROMPERS.PARTY_ID, TOPERS.PARTY_ID) IS NOT NULL and coalesce(FROMGRP.PARTY_ID, TOGRP.PARTY_ID) IS NOT NULL and REL.PARTY_RELATIONSHIP_TYPE_ID IS NOT NULL; --> <member-entity entity-alias="REL" entity-name="PartyRelationship"/> <member-entity entity-alias="FROMPERS" entity-name="Person"/> <member-entity entity-alias="TOPERS" entity-name="Person"/> <member-entity entity-alias="FROMGRP" entity-name="PartyGroup"/> <member-entity entity-alias="TOGRP" entity-name="PartyGroup"/> <alias name="person"> <complex-alias operator="||"> <complex-alias-field entity-alias="FROMPERS" field="partyId" default-value="''"/> <complex-alias-field entity-alias="TOPERS" field="partyId" default-value="''"/> </complex-alias> </alias> <alias name="group"> <complex-alias operator="||"> <complex-alias-field entity-alias="FROMGRP" field="partyId" default-value="''"/> <complex-alias-field entity-alias="TOGRP" field="partyId" default-value="''"/> </complex-alias> </alias> <view-link entity-alias="REL" rel-entity-alias="FROMPERS" rel-optional="true"> <key-map field-name="partyIdFrom" rel-field-name="partyId"/> </view-link> <view-link entity-alias="REL" rel-entity-alias="TOGRP" rel-optional="true"> <key-map field-name="partyIdTo" rel-field-name="partyId"/> </view-link> <view-link entity-alias="REL" rel-entity-alias="FROMGRP" rel-optional="true"> <key-map field-name="partyIdFrom" rel-field-name="partyId"/> </view-link> <view-link entity-alias="REL" rel-entity-alias="TOPERS" rel-optional="true"> <key-map field-name="partyIdTo" rel-field-name="partyId"/> </view-link> <entity-condition> <condition-list combine="and"> <condition-list combine="or"> <condition-expr entity-alias="FROMPERS" field-name="partyId" operator="not-equals"/> <condition-expr entity-alias="TOPERS" field-name="partyId" operator="not-equals"/> </condition-list> <condition-list combine="or"> <condition-expr entity-alias="FROMGRP" field-name="partyId" operator="not-equals"/> <condition-expr entity-alias="TOGRP" field-name="partyId" operator="not-equals"/> </condition-list> <condition-expr entity-alias="REL" field-name="partyRelationshipTypeId" operator="equals" value="EMPLOYMENT"/> </condition-list> </entity-condition> </view-entity> On Thu, Feb 21, 2013 at 9:22 PM, James Piechota <[hidden email]> wrote: > 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 < > [hidden email]> 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 <[hidden email]> >>> 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<[hidden email]>> >>>> 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<[hidden email]> >>>>>> >> >>>>>> >>>>>> 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<[hidden email]> >>>>>>>>> > >>>>>>>>> >>>>>>>>>> <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<[hidden email]> >>>>>>>>> > >>>>>>>>> >>>>>>>>>> ');>> >>>>>>>>>> >>>>>>>>> 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' >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >> > |
Re: List of all people and the company they work for
|
|
It's good to know you got it sorted. I would recommend leaving the
condition out of the view - so you can reuse it for other party relationships. -Adrian On 2/22/2013 10:49 PM, James Piechota wrote: > Solved (I couldn't let it go). For anyone else looking to do something > similar, the following will create a view-entity with two columns (person > and group) and populated with all EMPLOYMENT relationships (you can adjust > the condition at the end to grab other relationships). Main reason I've > needed to do this is to simplify dependent code - without this much of my > code would need to be duplicated to handle both person -> group and group > -> person relationships. (FYI: if you're using mysql, like I am, you'll > have to set sql_mode=PIPES_AS_CONCAT so that the || operator is treated as > a concat). > > <view-entity entity-name="XXX" package-name="org.ofbiz.xxx" title="XXX"> > <!-- SELECT > (coalesce(FROMPERS.PARTY_ID, "") || > coalesce(TOPERS.PARTY_ID, "")) as Person, > (coalesce(FROMGRP.PARTY_ID, "") || coalesce(TOGRP.PARTY_ID, > "")) as PartyGroup > FROM PARTY_RELATIONSHIP REL > LEFT OUTER JOIN PERSON FROMPERS ON FROMPERS.PARTY_ID = > REL.PARTY_ID_FROM > LEFT OUTER JOIN PARTY_GROUP TOGRP ON TOGRP.PARTY_ID = > REL.PARTY_ID_TO > LEFT OUTER JOIN PARTY_GROUP FROMGRP ON FROMGRP.PARTY_ID = > REL.PARTY_ID_FROM > LEFT OUTER JOIN PERSON TOPERS ON TOPERS.PARTY_ID = > REL.PARTY_ID_TO > WHERE coalesce(FROMPERS.PARTY_ID, TOPERS.PARTY_ID) IS NOT > NULL > and coalesce(FROMGRP.PARTY_ID, TOGRP.PARTY_ID) IS NOT NULL > and REL.PARTY_RELATIONSHIP_TYPE_ID IS NOT NULL; > --> > > <member-entity entity-alias="REL" entity-name="PartyRelationship"/> > <member-entity entity-alias="FROMPERS" entity-name="Person"/> > <member-entity entity-alias="TOPERS" entity-name="Person"/> > <member-entity entity-alias="FROMGRP" entity-name="PartyGroup"/> > <member-entity entity-alias="TOGRP" entity-name="PartyGroup"/> > > <alias name="person"> > <complex-alias operator="||"> > <complex-alias-field entity-alias="FROMPERS" > field="partyId" default-value="''"/> > <complex-alias-field entity-alias="TOPERS" field="partyId" > default-value="''"/> > </complex-alias> > </alias> > <alias name="group"> > <complex-alias operator="||"> > <complex-alias-field entity-alias="FROMGRP" field="partyId" > default-value="''"/> > <complex-alias-field entity-alias="TOGRP" field="partyId" > default-value="''"/> > </complex-alias> > </alias> > > <view-link entity-alias="REL" rel-entity-alias="FROMPERS" > rel-optional="true"> > <key-map field-name="partyIdFrom" rel-field-name="partyId"/> > </view-link> > <view-link entity-alias="REL" rel-entity-alias="TOGRP" > rel-optional="true"> > <key-map field-name="partyIdTo" rel-field-name="partyId"/> > </view-link> > <view-link entity-alias="REL" rel-entity-alias="FROMGRP" > rel-optional="true"> > <key-map field-name="partyIdFrom" rel-field-name="partyId"/> > </view-link> > <view-link entity-alias="REL" rel-entity-alias="TOPERS" > rel-optional="true"> > <key-map field-name="partyIdTo" rel-field-name="partyId"/> > </view-link> > <entity-condition> > <condition-list combine="and"> > <condition-list combine="or"> > <condition-expr entity-alias="FROMPERS" > field-name="partyId" operator="not-equals"/> > <condition-expr entity-alias="TOPERS" > field-name="partyId" operator="not-equals"/> > </condition-list> > <condition-list combine="or"> > <condition-expr entity-alias="FROMGRP" > field-name="partyId" operator="not-equals"/> > <condition-expr entity-alias="TOGRP" > field-name="partyId" operator="not-equals"/> > </condition-list> > <condition-expr entity-alias="REL" > field-name="partyRelationshipTypeId" operator="equals" value="EMPLOYMENT"/> > </condition-list> > </entity-condition> > </view-entity> > > > > On Thu, Feb 21, 2013 at 9:22 PM, James Piechota <[hidden email]> wrote: > >> 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 < >> [hidden email]> 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 <[hidden email]> >>>> 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<[hidden email]>> >>>>> 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<[hidden email]> >>>>>>> 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<[hidden email]> >>>>>>>>>>> <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<[hidden email]> >>>>>>>>>>> ');>> >>>>>>>>>>> >>>>>>>>>> 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' >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> |
Re: List of all people and the company they work for
|
|
Yep, thanks.
James On Fri, Feb 22, 2013 at 3:27 PM, Adrian Crum < [hidden email]> wrote: > It's good to know you got it sorted. I would recommend leaving the > condition out of the view - so you can reuse it for other party > relationships. > > -Adrian > > > On 2/22/2013 10:49 PM, James Piechota wrote: > >> Solved (I couldn't let it go). For anyone else looking to do something >> similar, the following will create a view-entity with two columns (person >> and group) and populated with all EMPLOYMENT relationships (you can adjust >> the condition at the end to grab other relationships). Main reason I've >> needed to do this is to simplify dependent code - without this much of my >> code would need to be duplicated to handle both person -> group and group >> -> person relationships. (FYI: if you're using mysql, like I am, you'll >> have to set sql_mode=PIPES_AS_CONCAT so that the || operator is treated as >> a concat). >> >> <view-entity entity-name="XXX" package-name="org.ofbiz.xxx" title="XXX"> >> <!-- SELECT >> (coalesce(FROMPERS.PARTY_ID, "") || >> coalesce(TOPERS.PARTY_ID, "")) as Person, >> (coalesce(FROMGRP.PARTY_ID, "") || >> coalesce(TOGRP.PARTY_ID, >> "")) as PartyGroup >> FROM PARTY_RELATIONSHIP REL >> LEFT OUTER JOIN PERSON FROMPERS ON FROMPERS.PARTY_ID = >> REL.PARTY_ID_FROM >> LEFT OUTER JOIN PARTY_GROUP TOGRP ON TOGRP.PARTY_ID = >> REL.PARTY_ID_TO >> LEFT OUTER JOIN PARTY_GROUP FROMGRP ON FROMGRP.PARTY_ID = >> REL.PARTY_ID_FROM >> LEFT OUTER JOIN PERSON TOPERS ON TOPERS.PARTY_ID = >> REL.PARTY_ID_TO >> WHERE coalesce(FROMPERS.PARTY_ID, TOPERS.PARTY_ID) IS NOT >> NULL >> and coalesce(FROMGRP.PARTY_ID, TOGRP.PARTY_ID) IS NOT >> NULL >> and REL.PARTY_RELATIONSHIP_TYPE_ID IS NOT NULL; >> --> >> >> <member-entity entity-alias="REL" entity-name="** >> PartyRelationship"/> >> <member-entity entity-alias="FROMPERS" entity-name="Person"/> >> <member-entity entity-alias="TOPERS" entity-name="Person"/> >> <member-entity entity-alias="FROMGRP" entity-name="PartyGroup"/> >> <member-entity entity-alias="TOGRP" entity-name="PartyGroup"/> >> >> <alias name="person"> >> <complex-alias operator="||"> >> <complex-alias-field entity-alias="FROMPERS" >> field="partyId" default-value="''"/> >> <complex-alias-field entity-alias="TOPERS" >> field="partyId" >> default-value="''"/> >> </complex-alias> >> </alias> >> <alias name="group"> >> <complex-alias operator="||"> >> <complex-alias-field entity-alias="FROMGRP" >> field="partyId" >> default-value="''"/> >> <complex-alias-field entity-alias="TOGRP" field="partyId" >> default-value="''"/> >> </complex-alias> >> </alias> >> >> <view-link entity-alias="REL" rel-entity-alias="FROMPERS" >> rel-optional="true"> >> <key-map field-name="partyIdFrom" rel-field-name="partyId"/> >> </view-link> >> <view-link entity-alias="REL" rel-entity-alias="TOGRP" >> rel-optional="true"> >> <key-map field-name="partyIdTo" rel-field-name="partyId"/> >> </view-link> >> <view-link entity-alias="REL" rel-entity-alias="FROMGRP" >> rel-optional="true"> >> <key-map field-name="partyIdFrom" rel-field-name="partyId"/> >> </view-link> >> <view-link entity-alias="REL" rel-entity-alias="TOPERS" >> rel-optional="true"> >> <key-map field-name="partyIdTo" rel-field-name="partyId"/> >> </view-link> >> <entity-condition> >> <condition-list combine="and"> >> <condition-list combine="or"> >> <condition-expr entity-alias="FROMPERS" >> field-name="partyId" operator="not-equals"/> >> <condition-expr entity-alias="TOPERS" >> field-name="partyId" operator="not-equals"/> >> </condition-list> >> <condition-list combine="or"> >> <condition-expr entity-alias="FROMGRP" >> field-name="partyId" operator="not-equals"/> >> <condition-expr entity-alias="TOGRP" >> field-name="partyId" operator="not-equals"/> >> </condition-list> >> <condition-expr entity-alias="REL" >> field-name="**partyRelationshipTypeId" operator="equals" >> value="EMPLOYMENT"/> >> </condition-list> >> </entity-condition> >> </view-entity> >> >> >> >> On Thu, Feb 21, 2013 at 9:22 PM, James Piechota <[hidden email]> >> wrote: >> >> 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 < >>> adrian.crum@sandglass-**software.com<[hidden email]>> >>> 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 <[hidden email]> >>>>> 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-**softwa**re.com <http://software.com>< >>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>> >> >>>>>> >>>>>> 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-**softwa**re.com <http://software.com>< >>>>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>>>> > >>>>>>>> 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-**softwa**re.com <http://software.com>< >>>>>>>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>>> <javascript:_e({}**, 'cvml', >>>>>>>>>>>> >>>>>>>>>>>> 'adrian.crum@sandglass-******softw**are.com < >>>>>>>>>>> http://software.com>< >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> adrian.crum@sandglass-****softwa**re.com <http://software.com>< >>>>>>>>>>> adrian.crum@sandglass-**softwa**re.com <http://software.com>< >>>>>>>>>>> adrian.crum@sandglass-**software.com<[hidden email]> >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>>> ');>> >>>>>>>>>>>> >>>>>>>>>>>> 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' >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> > |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |