The problem is my SQL need to have left join on two tables, which are essentially a header and detail tables and they need to have a inner join to make it meaningful.
I found that there are a few existing view-entity in Example and in product that are doing what i'm looking to do, but strange thing is my view-entity wouldn't pick up the internal view-entity aliases... Thanx FooShyn On Sat, Apr 7, 2012 at 6:56 PM, Jacques Le Roux < [email protected]> wrote: > I did not into any details at all, but I'd suggest you to rather create > another view-entity which would combine what you need from the 2 others. > (ie using directly real entities you used in both others) > > Jacques > > From: "Foo Shyn Chung" <[email protected]> > > Further on the issue, i tried moving the selection from using the >> entity-condition in xml to a groovy script and using delegator.findList. >> However the problem seems like persisting. >> >> Is this a bug in OFBiz? or simple the way i declare my view-entity is >> wrong? >> >> Thanx >> FooShyn >> >> On Thu, Apr 5, 2012 at 5:30 PM, Foo Shyn Chung <[email protected]> wrote: >> >> Hi all, >>> >>> I'm having issue trying to select a view entity field from within another >>> view entity. >>> >>> I have to view entity currently, first one looks like this: >>> >>> <view-entity entity-name="**ViewOfficerPaymentInfo" >>> package-name="com.trideas.ams.**payment" title="OfficerPayment joins >>> OfficerPaymentCommission"> >>> <member-entity entity-alias="OP" entity-name="OfficerPayment"/> >>> <member-entity entity-alias="PC" >>> entity-name="**OfficerPaymentCommission"/> >>> <alias entity-alias="OP" name="paymentId" /> >>> <alias entity-alias="OP" name="officerId"/> >>> >>> <view-link entity-alias="OP" rel-entity-alias="PC"> >>> <key-map field-name="paymentId"/> >>> </view-link> >>> </view-entity> >>> >>> >>> The second one: >>> >>> <view-entity entity-name="**ViewOfficerSalesReport" >>> package-name="com.trideas.ams.**customer" title="Customer tables joined >>> with >>> outer joins the Payment table for Officer Sales report"> >>> <member-entity entity-alias="PI" entity-name="** >>> CustomerPersonalInfo"/> >>> <member-entity entity-alias="FD" >>> entity-name="**CustomerFinancingDetails"/> >>> <member-entity entity-alias="VP" >>> entity-name="**ViewOfficerPaymentInfo"/> >>> <alias entity-alias="PI" name="customerId" field="customerId" /> >>> <alias entity-alias="PI" name="customerName" field="name" /> >>> <alias entity-alias="FD" name="officerId" field="marketingOfficerId" >>> /> >>> <alias entity-alias="VP" name="opBankId" field="opBankId"/> >>> <alias entity-alias="VP" name="opAccNo" field="opAccNo"/> >>> <alias entity-alias="VP" name="opPaymentDate" field="opPaymentDate" >>> /> >>> <alias entity-alias="VP" name="opChequeNo" field="opChequeNo" /> >>> <alias entity-alias="FD" name="loanApproved" field="loanApproved" /> >>> <alias entity-alias="FD" name="approvedDate" field="approvedDate" /> >>> <alias entity-alias="PI" name="createdDate" field="createdDate" /> >>> <view-link entity-alias="PI" rel-entity-alias="FD"> >>> <key-map field-name="customerId"/> >>> </view-link> >>> <view-link entity-alias="FD" rel-entity-alias="VP" >>> rel-optional="true"> >>> <key-map field-name="customerId" rel-field-name="customerId"/> >>> <key-map field-name="**marketingOfficerId" >>> rel-field-name="officerId"/> >>> </view-link> >>> </view-entity> >>> >>> When i try to use a entity-condition to select the entity, i found that i >>> can't select the opBankId and whatever else field from the VP view. >>> There's >>> a warning message in the log that says: >>> >>> "The field name (or key) [opBankId] is not valid for entity >>> [ViewOfficerSalesReport], printing IllegalArgumentException instead of >>> throwing it because Map interface specification does not allow throwing >>> that exception." >>> >>> I did a logging and found that the SQL itself is correct bar that the >>> fields from the view is not selected:- >>> >>> SELECT PI.CUSTOMER_ID, PI.NAME, FD.MARKETING_OFFICER_ID, >>> FD.LOAN_APPROVED, FD.APPROVED_DATE, PI.CREATED_DATE >>> FROM ((public.CUSTOMER_PERSONAL_**INFO PI >>> INNER JOIN public.CUSTOMER_FINANCING_**DETAILS FD ON PI.CUSTOMER_ID = >>> FD.CUSTOMER_ID) >>> LEFT OUTER JOIN ( >>> SELECT OP.PAYMENT_ID AS OP_PAYMENT_ID, OP.OFFICER_ID AS OP_OFFICER_ID, >>> OP.PAYMENT_DATE AS OP_PAYMENT_DATE, OP.BANK_ID AS OP_BANK_ID, OP.ACC_NO >>> AS >>> OP_ACC_NO, OP.CHEQUE_NO AS OP_CHEQUE_NO, >>> PC.CUSTOMER_ID AS PC_CUSTOMER_ID FROM public.OFFICER_PAYMENT OP INNER >>> JOIN >>> public.OFFICER_PAYMENT_**COMMISSION PC ON OP.PAYMENT_ID = >>> PC.PAYMENT_ID) VP >>> ON FD.CUSTOMER_ID = VP.PC_CUSTOMER_ID AND FD.MARKETING_OFFICER_ID = >>> VP.OP_OFFICER_ID) >>> WHERE ((PI.NAME LIKE '%')) >>> ORDER BY FD.APPROVED_DATE DESC >>> >>> The reason i'm putting the VP field with the prefix 'op' is because i'm >>> trying to let it select the fields, but it doesn't work. >>> >>> Anyone have any idea how this should work? Is there something i should >>> add >>> to the alias so that it will select those fields? >>> >>> Thanx >>> FooShyn >>> >>> >>
