Hi Chris,
At our company we make very heavy use of view-entities; but in order to do
so we have made quite a few enhancements that have yet to be bundled up and
offered back to the community. The one thing that hit me was (I believe)
Ofbiz OOTB does not support the notion of having a v-e based on another v-e
(this is something we make a lot of use of). We have made so many changes I
often have to do some research to be sure; but I remember fixing a bug in
the v-e population based on the fact that the v-e was not based solely on
entities.
With this support you could create your aggr. v-e and then have a v-e that
will use it joined with your entity using the technique we talked about
earlier.
Another "hack" we put in was the notion of being able to specify
"qualifier-sql" in the v-e definition. Effectively you could write a piece
of sql right in the v-e, so it could absolutely do the where clause you were
looking for. Now I think we would have to formalize the grammar of such an
enhancement.
I think the approach you are taking is fine. It is good to hear that other
folks have struggled with a few of the limitations of view-entities, and I
will look towards trying to bundle/formalize some of this support to try to
get it in the product moving forward.
Take care,
Bob
snowc wrote:
>
> So close, yet so far...
>
> I simplified my problem description. Actually, the complex-alias needed
> was:
>
> <alias name="amountDue">
> <complex-alias operator="-">
> <complex-alias-field entity-alias="SalvageAmounts"
> field="calculatedSalvageGross"/>
> <complex-alias-field
> entity-alias="VehiclePaymentSummaryView"
> field="vehiclePaymentTotalAmount"/>
> </complex-alias>
> </alias>
>
> Where:
>
> - SalvageAmounts is an entity that sits on top of a database view, not a
> table.
> - VehiclePaymentSummaryView is a view-entity with an aggregate function.
>
> Running the report results in the error:
>
> ---- exception report
> ----------------------------------------------------------
>
> Failure in by condition find operation, rolling back transaction
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:SELECT
> Uplift.ingUplNumber,
> Uplift.strUplInsurer, Uplift.dtmUplPickupDate,
> VehiclePaymentSummaryView.SUM_Ve
> hiclePayment_ingAmount_, UpliftFlags.strFlag, UpliftFlags.blnAgreed,
> UpliftFlags
> .blnReviewed, SalvageAmounts.upliftPAVBand, SalvageAmounts.ingPercentage,
> Salvag
> eAmounts.calculatedSalvageNet, SalvageAmounts.calculatedSalvageVat,
> SalvageAmoun
> ts.calculatedSalvageGross, (SalvageAmounts.calculatedSalvageGross -
> VehiclePayme
> ntSummaryView.SUM(VehiclePayment.ingAmount)) FROM ((dbo.tblUplift Uplift
> LEFT OU
> TER JOIN (SELECT Uplift.ingUplNumber AS Uplift_ingUplNumber,
> SUM(VehiclePayment.
> ingAmount) AS SUM_VehiclePayment_ingAmount_ FROM dbo.tblUplift Uplift LEFT
> OUTER
> JOIN dbo.tblVehiclePayment VehiclePayment ON Uplift.ingUplNumber =
> VehiclePayme
> nt.ingUpliftNumber GROUP BY Uplift.ingUplNumber) VehiclePaymentSummaryView
> ON Up
> lift.ingUplNumber = VehiclePaymentSummaryView.Uplift_ingUplNumber) INNER
> JOIN db
> o.tblUpliftFlags UpliftFlags ON Uplift.ingUplNumber =
> UpliftFlags.ingUplift) INN
> ER JOIN dbo.vSalvageAmounts SalvageAmounts ON Uplift.ingUplNumber =
> SalvageAmoun
> ts.ingUplNumber WHERE (Uplift.ingUplNumber > ? AND UpliftFlags.strFlag = ?
> AND U
> pliftFlags.blnAgreed = ? AND UpliftFlags.blnReviewed = ? AND
> Uplift.dtmUplPickup
> Date IS NOT NULL) (Cannot find either column "VehiclePaymentSummaryView"
> or the
> user-defined function or aggregate "VehiclePaymentSummaryView.SUM", or the
> name
> is ambiguous.)
>
> --
>
> I think I will push my logic back into a database view. I find that using
> pure entity and view-entity does not have the power needed to work with
> some legacy data designs. For example other posts have mentioned wanting
> to specify join conditions in view-link's which would be very useful.
>
> Many thanks, Chris
>
>
>
> Bob Morley wrote:
>>
>> I think I would create a view-entity that aliased all of the fields you
>> have here and added a complex-alias for the difference of the actual and
>> estimated net amounts. Something like --
>>
>> <alias name="actualEstimatedDelta">
>> <complex-alias operator="-">
>> <complex-alias-field entity-alias="E1"
>> field="actualNetAmount"/>
>> <complex-alias-field entity-alias="E1"
>> field="estimatedNetAmount"/>
>> </complex-alias>
>> </alias>
>>
>> Then do your entity condition on this field in your v-e.
>>
>> <condition-expr field="actualEstimatedDelta" operator="greater"
>> value="0"/>
>>
>> Bob
>>
>>
>> snowc wrote:
>>>
>>> I have some legacy data that I am trying to access in ofbiz.
>>>
>>> <entity name="E1">
>>> <field name="e1ID"/>
>>> <field name="actualNetAmount"/>
>>> <field name="estimatedNetAmount"/>
>>> </entity>
>>>
>>> Conceptually, I want to filter as shown here:
>>>
>>> SELECT e1Id, actualNetAmount, estimatedNetAmount
>>> FROM E1 WHERE actualNetAmount > estimatedNetAmount
>>>
>>> <entity-condition entity="E1">
>>> <condition-expr field="actualNetAmount" operator="greater"
>>> value="${this.estimatedNetAmount}"/>
>>> </entity-condition>
>>>
>>> How can I achieve the equivalent of "this" is a entity condition
>>> expression?
>>>
>>> I do not want to filter by iterating the list as the data volumes are
>>> huge.
>>>
>>> One option I am thinking of is to create a SQL view and use that to
>>> filter the data:
>>>
>>> SELECT
>>> e1Id, actualNetAmount, estimatedNetAmount,
>>> CASE
>>> WHEN actualNetAmount > estimatedNetAmount THEN 1
>>> ELSE 0
>>> END CASE AS actualGTEstimated
>>> FROM E1
>>>
>>> Maybe I could use DynamicViewEntities, but I was trying to use less
>>> beanshell/java and more minilang.
>>>
>>> Many thanks in advance, Chris
>>>
>>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/entity-condition-expression-problem-tp25010865p25038183.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
