How to get distinct record from OFBiz entity model?

Question

I want the view entity for query

select distinct p.product_id, p.internal_name from product p inner join supplier_product sp on p.product_id=sp.product_id

I tried

<view-entity entity-name="ProductAndSupplierProduct"
  package-name="org.ofbiz.product.supplier"
  title="Supplier-product and product antityview for purchase order entry">
  <member-entity entity-alias="SP" entity-name="SupplierProduct"/>
  <member-entity entity-alias="PR" entity-name="Product"/>
  <alias-all entity-alias="SP"/>
  <alias-all entity-alias="PR">
    <exclude field="productId"/>
    <exclude field="comments"/>
    <exclude field="quantityUomId"/>
  </alias-all>
  <view-link entity-alias="SP" rel-entity-alias="PR" >
    <key-map field-name="productId" rel-field-name="productId"/>
    <entity-condition distinct="true"><condition-expr field-name="productId"  entity-alias="SP" rel-entity-alias="PR" rel-field-name="productId" operator="in"/></entity-condition>
  </view-link>

</view-entity>

It will not give the correct output. Please help me to find the solution.

Try by adding entity-condition for the view-link or view-entity. If that is not possible you can also create dynamic views with some Java code and use that. — Bilgin Ibryam, May 14 '14 at 20:30
Thank You.I don`t want to use java code it will affect application. I am just looking for entity-condition but don`t know how it is possible. — ms74, May 16 '14 at 03:29
Just curios, why do you need distinct p.product_id, p.internal_name, these fields supposed to be unique anywya? — Bilgin Ibryam, May 17 '14 at 13:56
Sorry for late reply.I know product details will be unique but anyhow for the product there will be a number of suppliers.I need records of the product which are having suppliers. So i have to join the two tables like previously posted. Thanks for your help. — ms74, Jun 19 '14 at 08:17

ms74 · Answer 1 · 2014-06-19T08:18:26.507

Thanks for the replies. I got a partial answer by excluding all the fields available in the Supplier Product Table.

Just like

<view-entity entity-name="ProductAndSupplierProduct"
  package-name="org.ofbiz.product.supplier"
  title="Supplier-product and product antityview for purchase order entry">
  <member-entity entity-alias="SP" entity-name="SupplierProduct"/>
  <member-entity entity-alias="PR" entity-name="Product"/>

  <alias-all entity-alias="PR">
    <exclude field="productId"/>
    <exclude field="comments"/>
    <exclude field="quantityUomId"/>
  </alias-all>
  <alias-all entity-alias="SP">
            <exclude field="productId" />
            <exclude field="partyId" />  ...
  </alias-all>

  <view-link entity-alias="PR" rel-entity-alias="SP" >
    <key-map field-name="productId" rel-field-name="productId"/>
  </view-link>

</view-entity

Anyway I am looking for best option because this table contains 20 columns and I think excluding the column is not a best solution.

Thanks.

How to get distinct record from OFBiz entity model?

1 Answers1