How to implement left outer join, right outer join?

[gobiit25]

I have used inner join in view but not able to use the list of joins given below

1. left outer join or left join
2. right outer join or right join

Can some one suggest a way to implement above joins in Moqui.

Example code for implementing inner join

<view-entity entity-name="ProductCategoryParent" package-name="mantle.product.category">
        <member-entity entity-alias="PCR" entity-name="mantle.product.category.ProductCategoryRollup"/>
        <member-entity entity-alias="PCAT" entity-name="mantle.product.category.ProductCategory" join-from-alias="PCR">
            <key-map field-name="parentProductCategoryId"/></member-entity>
        <alias-all entity-alias="PCAT"><exclude field="productCategoryId"/></alias-all>
        <alias-all entity-alias="PCR"/>
    </view-entity>

[jonesde]

I recommend reading through the documentation in the Making Apps with Moqui book:

http://moqui.org/MakingAppsWithMoqui-1.0.pdf

What you're looking for is the member-entity.@join-optional attribute.

[gobiit25]

Left outer join works fine with join-optional attribute(join-optional="true") - Thanks for the input. But how to implement right outer join ?

[jonesde]

There is no support for right outer join now, as typically a left outer join is adequate and structuring joins this way keeps them more simple and straightforward.

Do you need both left and right outer joins? In general view-entity and the EntityFacade doesn't try to reproduce all of what you can do with SQL, not even close, but rather the most common things needed in business applications. With automatic view-entity trimming based on conditions, order by fields, and selected fields and automatic condition simplification it efficiently handles quite a lot and only in very rare occasions (typically not in application code) do I find any need to drop down to SQL (which you can do fairly easily through the EntityFacade.sqlFind() method).

[chunlinyao]

Right outer join useful in some rare case. If you have A join B join C right join D, the result with contains all rows of D link to intersection of A B C. This can be rewrite to a nested left join D left join (A join B join C). Moqui not support nested join. It can also be rewrite to CTE or sub query. select * from D left join (select * from A join B join C).

Sometimes I have a query I known it can be resolved simply in native SQL (eg. Use CTE, window function), But view-entity not support that feature. If I use SQL I must deal with JDBC.

View entity will not abstract all SQL feature, but If view-entity can support CTE, using CTE expression as A member entity, then I can use modern SQL, fall back to SQL to do some thing.

[jonesde]

The existing options for SQL directly include:

• EntityFacade.sqlFind(): can get an EntityListIterator back as long as the selected fields match some entity or view-entity and you enumerate the fields to put results in for each EntityValue object (in the fieldList parameter); you can pretty much always use this to avoid dropping to straight JDBC (not that direct JDBC is really all that bad)
• if most of what you are doing fits in with a view-entity you can do whatever you want in a where condition using the WhereCondition class (an EntityCondition, can be ANDed with others or used on its own); with this if you insert values to query by you have to watch for SQL injection

I generally avoid using any SQL directly to avoid issues with running on various databases, but there is quite a bit of 'safe' SQL that will run on any database.

What this doesn't support is arbitrary SQL in the from clause... that is quite a bit trickier to support. We might be able to do nested joins somehow, perhaps that is what it should do if a member-entity of a view-entity is another view-entity.

Other ideas?

[shendepu]

It seems member-entity of a view-entity can only be entity right now. If view-entity as member-entity to be impleted as nested joins, every single entity to be in nested joins needs to have a view-entity for it. It seems verbose.

How about add an attribute to member-entity to indicate it is a join or nested join?

And it seems currently member-enity only supports entity. Supporting view-entity as member-entity is supposed to have same behavior with entity as member-entity.

[chunlinyao]

EntityFacade.sqlFind works fine as long as I have a existing entity or view-entity. If I want to create a new view-entity for a native SQL query, I will face a problem about how to link the member-entities together, the fake join information in view-entity definition will confuse source reader, sometimes it is difficult to create a fake join relation, If i want to create a entity instead of view-entity for this SQL query. the entity will always create a new table, I can't define a entity with no-table="true". I hope there's a method to define view-entity without join or entity without table.

WhereCondition is good for complex where clause, even sub query in where clause.

view-entity as member-entity is more like a sub query in from/join clause, not all sub queries can be rewrite to joins, so use nested join to implement nested view-entity have some limitation.

Supporting group member-entity together will support nested join, then right join can be write on nested joins.

[jonesde]

Closing as part of general cleanup for the move to HiveMind and for no recent activity on this issue. If this comes up again or anyone wants to pursue it feel free to create a request on moqui.org. For more information see:

https://www.moqui.org/m/docs/moqui/Community+Guide