Yep, thanks. James
On Fri, Feb 22, 2013 at 3:27 PM, Adrian Crum < adrian.c...@sandglass-software.com> 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 <piech...@gmail.com> >> 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<adrian.c...@sandglass-software.com>> >>> 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 <piech...@gmail.com> >>>>> 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<adrian.c...@sandglass-software.com> >>>>>> >> >>>>>> >>>>>> 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<adrian.c...@sandglass-software.com> >>>>>>>> > >>>>>>>> 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<adrian.c...@sandglass-software.com> >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>>> <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<adrian.c...@sandglass-software.com> >>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>>> ');>> >>>>>>>>>>>> >>>>>>>>>>>> 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' >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >
