Enhancing EntityDynamicViewImpl: Adding Conditional Joins and Sub-Select Support

nutanshinde · March 22, 2025, 5:15am

Hello Everyone,

I hope you all are doing well! I wanted to share some challenges I have encountered while working with EntityDynamicViewImpl and explore possible enhancements to improve its flexibility.

Challenges in the Current Implementation:

Lack of Conditional Joins in Dynamic Views
→ Currently, we can only add a member entity in a dynamic view by passing the entityKeyMaps map, which adds the key-map in the view definition. However, there is no direct way to add conditions while joining member entities.
→ This makes it difficult to construct complex relationships dynamically based on specific conditions.
Inability to Select a Static View in a Dynamic View
→ At present, it is not possible to select a static view as a member entity in a dynamic view.
→ Currently, if we need to use a sub-select function within a dynamic view, it is not possible.
→ Enabling this would allow us to reuse existing static views and build more advanced query structures.

nutanshinde · March 22, 2025, 5:40am

Suggested Enhancements:

Support for Conditional Joins in Dynamic Views

Introduce functionality to allow conditions while joining entities, similar to static views.
This would provide better control over entity relationships and improve dynamic query building.

Allow Selecting Static Views in Dynamic Views

Enhance EntityDynamicViewImpl to allow selecting a static view as a member entity.
Ensure it integrates smoothly with existing query mechanisms for efficiency.

I have overwritten the addMemberEntity method.

EntityDynamicView addMemberEntity(String entityAlias, String entityName, String joinFromAlias, Boolean joinOptional,
                                      Map<String, String> entityKeyMaps, Map<String, String> entityConditions, String subSelect) {
        MNode memberEntity = entityNode.append("member-entity", ["entity-alias":entityAlias, "entity-name":entityName])
        if (joinFromAlias) {
            memberEntity.attributes.put("join-from-alias", joinFromAlias)
            memberEntity.attributes.put("join-optional", (joinOptional ? "true" : "false"))
        }
        if (entityKeyMaps) for (Map.Entry<String, String> keyMapEntry in entityKeyMaps.entrySet()) {
            memberEntity.append("key-map", ["field-name":keyMapEntry.getKey(), "related":keyMapEntry.getValue()])
        }
        // Add entity-condition if provided
        if (entityConditions && !entityConditions.isEmpty()) {
            MNode entityCondition = memberEntity.append("entity-condition", null)
            for (Map.Entry<String, String> keyValueEntry in entityConditions.entrySet()) {
                entityCondition.append("econdition", ["entity-alias": entityAlias, "field-name": keyValueEntry.getKey(), "value": keyValueEntry.getValue()])
            }
        }
        // Handle subSelect
        if (subSelect) {
            memberEntity.attributes.put("sub-select", subSelect)
        }

        return this
    }

I would appreciate insights from the community on the following:

→ Do these enhancements align with the intended use cases of EntityDynamicViewImpl?

→ Would this improvement be a valuable addition to Moqui?

→ If this approach seems appropriate, should I proceed with creating an issue and submitting a PR for review?

nutanshinde · March 27, 2025, 9:43am

Created the PR:

github.com/moqui/moqui-framework

Enhancing Dynamic Views in Moqui: Expanding Capabilities for Flexible Query Construction

moqui:master ← Shinde-nutan:entity-dynamic-view-impl

opened 09:30AM - 27 Mar 25 UTC

Shinde-nutan

+18 -0

Related: https://github.com/moqui/moqui-framework/issues/654 Overview This …MR introduces enhancements to EntityDynamicViewImpl, improving its flexibility in handling complex query structures. The goal is to make dynamic views more powerful by supporting conditional joins, sub-selects, and potential future improvements. Key Enhancements ✅ Conditional Joins Support Added the ability to define custom join conditions when adding member entities. This allows more control over how entities are joined dynamically. ✅ Sub-Select (Nested Query) Support Enabled the use of static views as member entities in dynamic views. This allows for sub-select queries, improving query reusability and efficiency.

grozadanut · March 27, 2025, 10:07am

Do you have some usage examples?

nutanshinde · March 27, 2025, 11:18am

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