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