Hi,
Here is an example usecase of dynamic view where this enhancement is needed
We need to identify customers who do not have an SSN (Social Security Number) recorded in the system.
Initially, we were joining the PartyIdentification
entity with Party
, but all types of identification records (e.g., Passport Number, Driverβs License, SSN, etc.) were included in the join. Later, we filtered out the unwanted records using a WHERE
condition.
This was inefficient because:
- All party identification records were retrieved, even if we only needed SSN-related records.
- Filtering was done after the join, making the process slow and consuming unnecessary resources.
Solution Using Join Conditions in Moqui Dynamic View
To optimize this, we added a condition at the time of the JOIN to ensure that only SSN-related records (partyIdTypeEnumId = 'PtidSsn'
) are considered.
Now, when we check for missing SSNs (PID.ID_VALUE IS NULL
), we get only the relevant parties efficiently.
Here is the code for getting the dynamic view:
def entityFind = ec.entity.find("mantle.party.Party")
def dynamicView = entityFind.makeEntityDynamicView()
dynamicView.addMemberEntity("P", "mantle.party.Party", null, null, null)
// Conditionally Join PartyIdentification - Only if PtidSsn exists
dynamicView.addMemberEntity("PID", "mantle.party.PartyIdentification", "P", true,
["partyId": "partyId"], ["partyIdTypeEnumId": "PtidSsn"], null)
dynamicView.addMemberEntity("PRS", "mantle.party.Person", "P", true,
["partyId": "partyId"])
dynamicView.addMemberEntity("PCM", "mantle.party.contact.PartyContactMech", "P", false,
["partyId": "partyId"])
dynamicView.addAliasAll("P", null)
dynamicView.addAlias("PID", "partySsnId", "idValue", null)
dynamicView.addAliasAll("PRS", null)
dynamicView.addAliasAll("PCM", null)
entityFind.condition("partySsnId", null, null)
def result = entityFind.list()
Generated Dynamic view Entity Node:
<member-entity entity-alias="P" entity-name="mantle.party.Party"/>
<member-entity entity-alias="PID" entity-name="mantle.party.PartyIdentification" join-from-alias="P" join-optional="true">
<key-map field-name="partyId" related="partyId"/>
<entity-condition>
<econdition entity-alias="PID" field-name="partyIdTypeEnumId" value="PtidSsn"/></entity-condition>
</member-entity>
<member-entity entity-alias="PRS" entity-name="mantle.party.Person" join-from-alias="P" join-optional="true">
<key-map field-name="partyId" related="partyId"/></member-entity>
<member-entity entity-alias="PCM" entity-name="mantle.party.contact.PartyContactMech" join-from-alias="P" join-optional="false">
<key-map field-name="partyId" related="partyId"/></member-entity>
<alias-all entity-alias="P" prefix="null"/>
<alias entity-alias="PID" name="partySsnId" field="idValue"/>
<alias-all entity-alias="PRS" prefix="null"/>
<alias-all entity-alias="PCM" prefix="null"/>
</view-entity>
Generated SQL:
PID.ID_VALUE AS PID_ID_VALUE,
P.PARTY_ID AS P_PARTY_ID,
P.PSEUDO_ID AS P_PSEUDO_ID,
P.PARTY_TYPE_ENUM_ID AS P_PARTY_TYPE_ENUM_ID,
P.DISABLED AS P_DISABLED,
P.CUSTOMER_STATUS_ID AS P_CUSTOMER_STATUS_ID,
P.OWNER_PARTY_ID AS P_OWNER_PARTY_ID,
P.EXTERNAL_ID AS P_EXTERNAL_ID,
P.DATA_SOURCE_ID AS P_DATA_SOURCE_ID,
P.GATEWAY_CIM_ID AS P_GATEWAY_CIM_ID,
P.COMMENTS AS P_COMMENTS,
P.SHIPPING_INSTRUCTIONS AS P_SHIPPING_INSTRUCTIONS,
P.HAS_DUPLICATES AS P_HAS_DUPLICATES,
P.LAST_DUP_CHECK_DATE AS P_LAST_DUP_CHECK_DATE,
P.MERGED_TO_PARTY_ID AS P_MERGED_TO_PARTY_ID,
PRS.SALUTATION AS PRS_SALUTATION,
PRS.FIRST_NAME AS PRS_FIRST_NAME,
PRS.MIDDLE_NAME AS PRS_MIDDLE_NAME,
PRS.LAST_NAME AS PRS_LAST_NAME,
PRS.PERSONAL_TITLE AS PRS_PERSONAL_TITLE,
PRS.SUFFIX AS PRS_SUFFIX,
PRS.NICKNAME AS PRS_NICKNAME,
PRS.GENDER AS PRS_GENDER,
PRS.BIRTH_DATE AS PRS_BIRTH_DATE,
PRS.DECEASED_DATE AS PRS_DECEASED_DATE,
PRS.HEIGHT AS PRS_HEIGHT,
PRS.WEIGHT AS PRS_WEIGHT,
PRS.MOTHERS_MAIDEN_NAME AS PRS_MOTHERS_MAIDEN_NAME,
PRS.MARITAL_STATUS_ENUM_ID AS PRS_MARITAL_STATUS_ENUM_ID,
PRS.EMPLOYMENT_STATUS_ENUM_ID AS PRS_EMPLOYMENT_STATUS_ENUM_ID,
PRS.RESIDENCE_STATUS_ENUM_ID AS PRS_RESIDENCE_STATUS_ENUM_ID,
PRS.OCCUPATION AS PRS_OCCUPATION,
PCM.CONTACT_MECH_ID AS PCM_CONTACT_MECH_ID,
PCM.CONTACT_MECH_PURPOSE_ID AS PCM_CONTACT_MECH_PURPOSE_ID,
PCM.FROM_DATE AS PCM_FROM_DATE,
PCM.THRU_DATE AS PCM_THRU_DATE,
PCM.EXTENSION AS PCM_EXTENSION,
PCM.ALLOW_SOLICITATION AS PCM_ALLOW_SOLICITATION,
PCM.USED_SINCE AS PCM_USED_SINCE,
PCM.USED_UNTIL AS PCM_USED_UNTIL,
PCM.VERIFY_CODE AS PCM_VERIFY_CODE,
PCM.VERIFY_CODE_DATE AS PCM_VERIFY_CODE_DATE,
PCM.VERIFY_CODE_ATTEMPTS AS PCM_VERIFY_CODE_ATTEMPTS
FROM
(
PARTY P
LEFT OUTER JOIN PARTY_IDENTIFICATION PID ON P.PARTY_ID = PID.PARTY_ID
AND PID.PARTY_ID_TYPE_ENUM_ID = ?
LEFT OUTER JOIN PERSON PRS ON P.PARTY_ID = PRS.PARTY_ID
INNER JOIN PARTY_CONTACT_MECH PCM ON P.PARTY_ID = PCM.PARTY_ID
)
WHERE
PID.ID_VALUE IS NULL