entity-engine union support

[Adam Heath-2]

Has anyone else ever wanted to do a UNION type query in ofbiz?
Currently, I'm doing a hack, where I use the entity engine sql
generation to get the different parts of the UNION query, but then I
concatenate them myself before doing a raw jdbc call.

I don't have any plans right now to actually add support for this, but
was just sending out a feeler.

[Bob Morley]

Do you have a bug in our office?  I happen to be working on this very thing today.  A number of months ago a developer here had added this support to the entity model.  In affect, you could union two entities together -- the issues we had today were around proper typing in the model (it needed to derive its type based on its union members).  Here is a snippet from one of ours ...

    <union-view-entity entity-name="PhoneAddressContactView" package-name="com.emforium.ofbiz" title="all contacts">
        <member-entity entity-alias="phone" entity-name="PartyPhoneView" />
        <member-entity entity-alias="address" entity-name="PartyAddressView" />

        <union-field name="partyId">
            <union-member-field entity-alias="phone" name="partyId"/>
            <union-member-field entity-alias="address" name="partyId"/>
        </union-field>
        <union-field name="contactMechId">
            <union-member-field entity-alias="phone" name="contactMechId"/>
            <union-member-field entity-alias="address" name="contactMechId"/>
        </union-field>
        <union-field name="contactMechPurposeTypeId">
            <union-member-field entity-alias="phone" name="contactMechPurposeTypeId"/>
            <union-member-field entity-alias="address" name="contactMechPurposeTypeId"/>
        </union-field>
    </union-view-entity>

We do not have an equivalent to "alias-all" here, we have to be explicit about the fields we want to expose by our union entity.  What happens at load and basic populate time, a ModelViewEntity is constructed (there is a union flag on it) with the appropriate set of aliases.

What you do not see here is that we have added support to be able to create view-entities based on other view-entities and we support a complex-alias-constant that can be used as "filler columns" when your two unioned entities do not match up exactly.

If there is interest we fit our support into Ofbiz trunk and provide a patch.

[Adam Heath-2]

Bob Morley wrote:

> Do you have a bug in our office?  I happen to be working on this very thing
> today.  A number of months ago a developer here had added this support to
> the entity model.  In affect, you could union two entities together -- the
> issues we had today were around proper typing in the model (it needed to
> derive its type based on its union members).  Here is a snippet from one of
> ours ...
>
>     <union-view-entity entity-name="PhoneAddressContactView"
> package-name="com.emforium.ofbiz" title="all contacts">
>         <member-entity entity-alias="phone" entity-name="PartyPhoneView" />
>         <member-entity entity-alias="address" entity-name="PartyAddressView"
> />
>
>         <union-field name="partyId">
>             <union-member-field entity-alias="phone" name="partyId"/>
>             <union-member-field entity-alias="address" name="partyId"/>
>         </union-field>
>         <union-field name="contactMechId">
>             <union-member-field entity-alias="phone" name="contactMechId"/>
>             <union-member-field entity-alias="address"
> name="contactMechId"/>
>         </union-field>
>         <union-field name="contactMechPurposeTypeId">
>             <union-member-field entity-alias="phone"
> name="contactMechPurposeTypeId"/>
>             <union-member-field entity-alias="address"
> name="contactMechPurposeTypeId"/>
>         </union-field>
>     </union-view-entity>
>
> We do not have an equivalent to "alias-all" here, we have to be explicit
> about the fields we want to expose by our union entity.  What happens at
> load and basic populate time, a ModelViewEntity is constructed (there is a
> union flag on it) with the appropriate set of aliases.
>
> What you do not see here is that we have added support to be able to create
> view-entities based on other view-entities and we support a
> complex-alias-constant that can be used as "filler columns" when your two
> unioned entities do not match up exactly.
>
> If there is interest we fit our support into Ofbiz trunk and provide a
> patch.

File jira, I could then put this in a branch, and we might need do
some tweaking before it hits trunk.

You may want to reuse the <field-map> stuff.

The <member-entity> here should have a feature to add new conditions.

[Harmeet Bedi]

I believe David jones had looked at various view enhancements we had
done mid-last year and had a way of extending entity model to handle all
kinds of extensions we did. Not sure what the extensions were, but maybe
David remembers.

Harmeet

On 20/03/10 2:39 AM, Adam Heath wrote:

> Bob Morley wrote:
>> Do you have a bug in our office?  I happen to be working on this very thing
>> today.  A number of months ago a developer here had added this support to
>> the entity model.  In affect, you could union two entities together -- the
>> issues we had today were around proper typing in the model (it needed to
>> derive its type based on its union members).  Here is a snippet from one of
>> ours ...
>>
>>      <union-view-entity entity-name="PhoneAddressContactView"
>> package-name="com.emforium.ofbiz" title="all contacts">
>>          <member-entity entity-alias="phone" entity-name="PartyPhoneView" />
>>          <member-entity entity-alias="address" entity-name="PartyAddressView"
>> />
>>
>>          <union-field name="partyId">
>>              <union-member-field entity-alias="phone" name="partyId"/>
>>              <union-member-field entity-alias="address" name="partyId"/>
>>          </union-field>
>>          <union-field name="contactMechId">
>>              <union-member-field entity-alias="phone" name="contactMechId"/>
>>              <union-member-field entity-alias="address"
>> name="contactMechId"/>
>>          </union-field>
>>          <union-field name="contactMechPurposeTypeId">
>>              <union-member-field entity-alias="phone"
>> name="contactMechPurposeTypeId"/>
>>              <union-member-field entity-alias="address"
>> name="contactMechPurposeTypeId"/>
>>          </union-field>
>>      </union-view-entity>
>>
>> We do not have an equivalent to "alias-all" here, we have to be explicit
>> about the fields we want to expose by our union entity.  What happens at
>> load and basic populate time, a ModelViewEntity is constructed (there is a
>> union flag on it) with the appropriate set of aliases.
>>
>> What you do not see here is that we have added support to be able to create
>> view-entities based on other view-entities and we support a
>> complex-alias-constant that can be used as "filler columns" when your two
>> unioned entities do not match up exactly.
>>
>> If there is interest we fit our support into Ofbiz trunk and provide a
>> patch.
>
> File jira, I could then put this in a branch, and we might need do
> some tweaking before it hits trunk.
>
> You may want to reuse the<field-map>  stuff.
>
> The<member-entity>  here should have a feature to add new conditions.

[David E. Jones-2]

There are a few thing in place to do conditions and sort order and such on view-entities, but nothing to do unions.

-David


On Mar 23, 2010, at 5:58 AM, Harmeet Bedi wrote:

> I believe David jones had looked at various view enhancements we had done mid-last year and had a way of extending entity model to handle all kinds of extensions we did. Not sure what the extensions were, but maybe David remembers.
>
> Harmeet
>
> On 20/03/10 2:39 AM, Adam Heath wrote:
>> Bob Morley wrote:
>>> Do you have a bug in our office?  I happen to be working on this very thing
>>> today.  A number of months ago a developer here had added this support to
>>> the entity model.  In affect, you could union two entities together -- the
>>> issues we had today were around proper typing in the model (it needed to
>>> derive its type based on its union members).  Here is a snippet from one of
>>> ours ...
>>>
>>>     <union-view-entity entity-name="PhoneAddressContactView"
>>> package-name="com.emforium.ofbiz" title="all contacts">
>>>         <member-entity entity-alias="phone" entity-name="PartyPhoneView" />
>>>         <member-entity entity-alias="address" entity-name="PartyAddressView"
>>> />
>>>
>>>         <union-field name="partyId">
>>>             <union-member-field entity-alias="phone" name="partyId"/>
>>>             <union-member-field entity-alias="address" name="partyId"/>
>>>         </union-field>
>>>         <union-field name="contactMechId">
>>>             <union-member-field entity-alias="phone" name="contactMechId"/>
>>>             <union-member-field entity-alias="address"
>>> name="contactMechId"/>
>>>         </union-field>
>>>         <union-field name="contactMechPurposeTypeId">
>>>             <union-member-field entity-alias="phone"
>>> name="contactMechPurposeTypeId"/>
>>>             <union-member-field entity-alias="address"
>>> name="contactMechPurposeTypeId"/>
>>>         </union-field>
>>>     </union-view-entity>
>>>
>>> We do not have an equivalent to "alias-all" here, we have to be explicit
>>> about the fields we want to expose by our union entity.  What happens at
>>> load and basic populate time, a ModelViewEntity is constructed (there is a
>>> union flag on it) with the appropriate set of aliases.
>>>
>>> What you do not see here is that we have added support to be able to create
>>> view-entities based on other view-entities and we support a
>>> complex-alias-constant that can be used as "filler columns" when your two
>>> unioned entities do not match up exactly.
>>>
>>> If there is interest we fit our support into Ofbiz trunk and provide a
>>> patch.
>>
>> File jira, I could then put this in a branch, and we might need do
>> some tweaking before it hits trunk.
>>
>> You may want to reuse the<field-map>  stuff.
>>
>> The<member-entity>  here should have a feature to add new conditions.
>

[Bob Morley]

JIRA-3575 has been created that contains union support.  A couple of things I would consider to enhance this capability ...

1) The ability to have a short-hand in the xml representation similar to "alias-all" the idea here is you could define the union and the fields you want, but then just take "all" for each field across all of the entity members rather than having to define each of them (when the field name matches the name).

2) This becomes much more powerful if you couple it with the ability to create view-entities that can have member-entity references to other view-entities / union-view-entities.  We have found this very powerful, being able to create a view-entity that can collect all the data for a specific presentment artifact like a form or a screen reducing the need for multiple queries, etc.

If we feel this is desirable for the community/project I can gather up the pieces that allow #2 and include that as a separate patch.

[Harmeet Bedi]

The reason why i had avoided short hands such as 'alias-all' when developing
ModelUnionViewEntity are:

- Union is on strictly homogeneous sets. Doing auto mapping can cause
runtime errors if view changes.  A lot of people are not terribly familar
with unions so alias-all may cause pain.
- While very useful, unions are rarely used. Convenience would come into
play only rarely.
- Generally short expression is nice but i think there is a chance of query
getting more expensive than desired with more columns fetched and perhaps
more joins(in underlying views) if things like 'alias-all' are used in
view/union entity. For things to do with db fetches(views/unions) i tend to
be conservative and use explicit fields.

Harmeet

On Fri, Mar 26, 2010 at 12:19 AM, Bob Morley <[hidden email]> wrote:

>
> JIRA-3575 has been created that contains union support.  A couple of things
> I
> would consider to enhance this capability ...
>
> 1) The ability to have a short-hand in the xml representation similar to
> "alias-all" the idea here is you could define the union and the fields you
> want, but then just take "all" for each field across all of the entity
> members rather than having to define each of them (when the field name
> matches the name).
>
> 2) This becomes much more powerful if you couple it with the ability to
> create view-entities that can have member-entity references to other
> view-entities / union-view-entities.  We have found this very powerful,
> being able to create a view-entity that can collect all the data for a
> specific presentment artifact like a form or a screen reducing the need for
> multiple queries, etc.
>
> If we feel this is desirable for the community/project I can gather up the
> pieces that allow #2 and include that as a separate patch.
> --
> View this message in context:
> http://n4.nabble.com/entity-engine-union-support-tp1607240p1691129.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
>