Conditional join for view entity of OFBiz

Question

I have two tables emp and emp_attributes among which i want to have a conditional join

emp

empno | ename  | location | salary
  1   | Josh   |    LA    |  1200
  2   | Joe    |    NY    |  1000
  3   | George |    LA    |  1500

emp_attributes

empno | attr_name  | attr_value
  1   | appraised  |    10%
  1   | basicSalary|    700
  2   | basicSalary|    600
  2   | appraised  |    12%
  2   | spouseName |   Amelia
  3   | appraised  |    25%
  3   | basicSalary|    700
  3   | spouseName |    Lucy

I want to have a view for query like

SELECT E.*, EA1.ATTR_VALUE AS APPRAISAL_RATE, EA2.ATTR_VALUE AS SPOUSE_NAME FROM EMP E
LEFT JOIN EMP_ATTRIBUTES EA1 ON E.EMPNO=EA1.EMPNO AND EA1.ATTR_NAME='appraised'
LEFT JOIN EMP_ATTRIBUTES EA2 ON E.EMPNO=EA2.EMPNO AND EA2.ATTR_NAME='spouseName';

Expected Output
empno | ename  | location | salary | APPRAISAL_RATE | SPOUSE_NAME
  1   | Josh   |    LA    |  1200  |      10%       |    NULL
  2   | Joe    |    NY    |  1000  |      12%       |   Amelia
  3   | George |    LA    |  1500  |      25%       |    Lucy

I tried

<view-entity entity-name="EmpAndEmpAttributesAndEmpAttributes"
             package-name="com.proj.employee"
             title="Employee and Employee Attribute and Employee Attribute">
     <member-entity entity-alias="E" entity-name="Emp"/>
     <member-entity entity-alias="EA1" entity-name="EmpAttributes"/>
     <member-entity entity-alias="EA2" entity-name="EmpAttributes"/>

     <alias-all entity-alias="E"/>
     <alias-all entity-alias="EA1"/>
     <alias-all entity-alias="EA2"/>

    <view-link entity-alias="E" rel-entity-alias="EA1" rel-optional="true">
        <key-map field-name="empno"/>
    </view-link>
    <entity-condition>
        <condition-expr entity-alias="EA1" field-name="attrName" operator="equals" value="appraised" />
    </entity-condition>

    <view-link entity-alias="E" rel-entity-alias="EA2" rel-optional="true">
        <key-map field-name="empno"/>
    </view-link>
    <entity-condition>
        <condition-expr entity-alias="EA1" field-name="attrName" operator="equals" value="spouseName" />
    </entity-condition>
</view-entity>

Someone let me know what wrong am I doing? or any other way to solve this problem...

score 3 · Answer 1 · answered Apr 18 '13 at 09:55

The condition should be put within "view-link" node, since it's not "where" condition, it's "join" condition.

<view-link entity-alias="E" rel-entity-alias="EA1" rel-optional="true">
    <key-map field-name="empno"/>
    <entity-condition>
        <condition-expr entity-alias="EA1" field-name="attrName" operator="equals" value="appraised" />
    </entity-condition>
</view-link>

score 0 · Answer 2 · answered Oct 14 '13 at 21:26

Looks like this is a known issue, in some versions of OFBiz the entity condition does not work inside a view link, see : https://issues.apache.org/jira/browse/OFBIZ-4781

You can confirm this by inspecting the actual SQL generated by GeneralDao.java, I came across similar issues attempting to do the same the dynamic view entities.

Conditional join for view entity of OFBiz

emp

emp_attributes

2 Answers2