Well, as we all know now, you must have either an aggregate function or
group-by clause unless your select statement does not reference the fields
in the view. So, I did this (note the use of the "max" function on the
string and date fields) just as a test:
<view-entity entity-name="InvoiceAndItemTotal"
package-name="org.ofbiz.accounting.ar"
title="Invoice and InvoiceItem Total">
<member-entity entity-alias="I" entity-name="Invoice"/>
<member-entity entity-alias="II" entity-name="InvoiceItem"/>
<alias entity-alias="II" name="invoiceId" group-by="true" />
<alias entity-alias="I" name="partyId" group-by="true"/>
<alias entity-alias="I" name="partyIdFrom" group-by="true"/>
<alias entity-alias="I" name="statusId" function="max" />
<alias entity-alias="I" name="billingAccountId" function="max"/>
<alias entity-alias="I" name="invoiceDate" function="max"/>
<alias entity-alias="I" name="paidDate" group-by="true"/>
<alias entity-alias="I" name="invoiceTypeId" function="max"/>
<alias entity-alias="II" name="orderTotal" function="sum">
<complex-alias operator="*">
<complex-alias-field entity-alias="II" field="quantity"
default-value="1"/>
<complex-alias-field entity-alias="II" field="amount"
default-value="0"/>
</complex-alias>
</alias>
<view-link entity-alias="I" rel-entity-alias="II">
<key-map field-name="invoiceId"/>
</view-link>
</view-entity>
This actually works in both derby and postgres and the raw sql works in
mysql (didnt actually try hooking it up, just ran a similiar test). Not
entirely sure of the impact on performance as this was a small data set.
Fun as this was, what I really want to do is just sum the InvoiceItem parts
and relate the results to Invoice in a single view.
Is there a way to do this? Can I create a view from a view-entity and
entity?
Skip
-----Original Message-----
From: Scott Gray [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 20, 2007 8:57 PM
To: [email protected]
Subject: Re: Entity View Problem
I'm no sql expert but isn't there some rule about the select fields needing
to be in the group by?
Regards
Scott
On 21/11/2007, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Anyone up late who can spot the problem with this:
>
> <view-entity entity-name="InvoiceAndItemTotal"
> package-name="com.opensourcestrategies.financials.invoice"
> title="Invoice and InvoiceItem Total">
> <member-entity entity-alias="I" entity-name="Invoice"/>
> <member-entity entity-alias="II" entity-name="InvoiceItem"/>
> <alias entity-alias="I" name="partyId" />
> <alias entity-alias="I" name="partyIdFrom" />
> <alias entity-alias="I" name="statusId" />
> <alias entity-alias="I" name="billingAccountId" />
> <alias entity-alias="I" name="invoiceDate" />
> <alias entity-alias="I" name="paidDate" />
> <alias entity-alias="I" name="invoiceTypeId" />
> <alias entity-alias="II" name="invoiceId" group-by="true" />
> <alias entity-alias="II" name="orderTotal" function="sum">
> <complex-alias operator="*">
> <complex-alias-field entity-alias="II" field="quantity"
> default-value="1"/>
> <complex-alias-field entity-alias="II" field="amount"
> default-value="0"/>
> </complex-alias>
> </alias>
> <view-link entity-alias="II" rel-entity-alias="I">
> <key-map field-name="invoiceId"/>
> </view-link>
> <relation type="one-nofk" rel-entity-name="InvoiceItem">
> <key-map field-name="invoiceId"/>
> <key-map field-name="invoiceItemSeqId"/>
> </relation>
> </view-entity>
>
> Results in:
> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
> Exception
> while executing the following:
> SELECT I.PARTY_ID, I.PARTY_ID_FROM, I.STATUS_ID, I.BILLING_ACCOUNT_ID,
> I.INVOICE_DATE, I.PAID_DATE, I.INVOICE_TYPE_ID, I.INVOICE_ID,
> SUM((COALESCE(II.QUANTITY,1) * COALESCE(II.AMOUNT,0))) FROM
> OFBIZ.INVOICE_ITEM II INNER JOIN OFBIZ.INVOICE I ON II.INVOICE_ID =
> I.INVOICE_ID GROUP BY I.INVOICE_ID
>
> Thanks in advance.
>
> Skip
>
>
