[OFBiz] Users - Question about Entity Engine Guide
Locked
[OFBiz] Users - Question about Entity Engine Guide
 
|
Hi all,
I came across this statement at the Entity Engine Guide page , which I am not very sure what does it mean and would like to seek for advise: "The primary key for a view entity should include all primary key fields of each member entity of the view. This means that all of the primary key fields must be aliased, but fields that are used to link the entities need only be aliased once. For example, if an OrderHeader entity has an orderId primary key and an OrderLine entity has an orderId primary key and an orderLineSeqId primary key, and the orderId was the mapped key that links the two entities, then the view entity would have two primary keys: orderId and orderLineSeqId. In this case orderId would only be aliased once since by definition the orderIds from each entity will always be the same since that is how the entities are linked (or joined)." Is there anybody that can help me to elaborate more? or maybe provide me the sample configuration based on the sample given on the statement? Moreover, I am much appreciate if someone can really help me to explain the sample configuration on the Entity Engine Guide about View-Entities. It is a great idea that we can do join queries using View-Entities but I believe I need more guidance and explanation in order for me to really understand it. the sample is pasted below: <view-entity title="Sample View Entity" copyright="Copyright (c) 2001 John Doe Enterprises" author="John Doe" version="1.0" package-name="org.ofbiz.commonapp.sample" entity-name="SampleViewEntity"> <member-entity entity-alias="SE" entity-name="SampleEntity" /> <member-entity entity-alias="OSE" entity-name="OtherSampleEntity" /> <alias entity-alias="SE" name="primaryKeyFieldOne" /> <alias entity-alias="SE" name="primaryKeyFieldTwo" /> <alias entity-alias="SE" name="fieldOne" /> <alias entity-alias="SE" name="fieldTwo" /> <alias entity-alias="OSE" name="primaryKeyOne" /> <alias entity-alias="OSE" name="otherFieldOne" field="fieldOne" /> <view-link entity-alias="SE" rel-entity-alias="OSE"> <key-map field-name="foreignKeyOne" rel-field-name="primaryKeyOne" /> </view-link> <relation type="one" rel-entity-name="OtherSampleEntity"> <key-map field-name="primaryKeyOne" /> </relation> <relation type="many" title="AllOne" rel-entity-name="SampleEntity"> <key-map field-name="primaryKeyFieldOne" /> </relation> </view-entity> Last but not least, I would like to ask is Entity Views support nested join queries? If no, does it mean I have to run the query using jdbc? THank you. Regards, Gan __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
RE: [OFBiz] Users - Question about Entity Engine Guide
|
|
Hello Gan,
I'm not an expert on OFBiz but I think I'm able to help with this one. I'll base my explaination on a concrete example from the entitymodel.xml. In entitymodel.xml you'll find two elements:
ProductStoreShipmentMeth and ProductStoreShipmentMethView. The former is beign
the entity
denfinition of the product_store_shipment_meth database
table (note the underscores). The second one is a query definition, which will ultimately be translated
into an SQL query by the entity engine. Let's
take a closer look at this
definition.
Under ProductStoreShipmentMethView you'll
find one or more lines like this one:
<member-entity entity-alias="PSSM" entity-name="ProductStoreShipmentMeth"/> Such a line will declare those tables that will be used in this query. In the example above, we will refer to the ProductStoreShipmentMeth table, product_store_shipment_meth, by the PSSM alias (which corresponds to an SQL table alias). So far this translates to the following SQL: SELECT * FROM product_store_shipment_meth AS
PSSM
Using this
alias we will then choose what columns we want to retreeve the data
for, for example:
<alias
entity-alias="PSSM" name="productStoreId"/>
means that we want to retrieve the values of
product_store_id column from the product_store_shipment_meth table (alias PSSM).
And the corresponding SQL:
SELECT product_store_id FROM
product_store_shipment_meth AS
PSSM Then comes a
view-link element:
<view-link entity-alias="PSSM"
rel-entity-alias="SM">
<key-map field-name="shipmentMethodTypeId"/> </view-link> Under the
view-link element we define how tables (or entities) should
be joined. In the above example, PSSM and SM (alias for
ShipmentMethod) will be joined on the shipment_method_type_id field. And the
pseudo-SQL:
SELECT product_store_id...
FROM product_store_shipment_meth AS PSSM,
shipment_method AS SM
WHERE PSSM.shipment_method_type_id =
SM.shipment_method_type_id
After
that comes the relation element:
<relation
type="one-nofk" title="Include" rel-entity-name="Geo">
<key-map field-name="includeGeoId" rel-field-name="geoId"/> </relation> The relation element gives you the ability to inlclude a table, not previously declared in entitymodel.xml, in your query. In the example above Geo has not been declared as an entity in the entitymodel.xml. The nested key-map element defines the criteria for this join, i.e: ...
PSSM.include_geo_id = geo.geo_id...
Since the
geo table has not been declared as an entity erlier, I belive that all of its
columns will be returned in the query result. Which leads us to the
final pseudo-SQL query:
SELECT product_store_id, ...more
columns...,
PSSM.include_geo_id AS
"Include",
PSSM.exclude_geo_id AS "Exclude",
geo.*
FROM product_store_shipment_meth AS PSSM,
shipment_method AS SM, geo
WHERE PSSM.shipment_method_type_id =
SM.shipment_method_type_id AND
PSSM.include_geo_id = geo.geo_id OR
PSSM.exclude_geo_id =
geo.geo_id As you can
see the the entity engine is capable of processing nested queries. You
can either modify en existing entity-view or create your own one. I
myself have modified the ProductStoreShipmentMethView we talked about
here.
Hope this'll
help.
My
regards,
</Firas>
-----Original Message----- From: [hidden email] [[hidden email]] On Behalf Of Gan Chun Yee Sent: Friday, September 09, 2005 5:17 AM To: [hidden email] Subject: [OFBiz] Users - Question about Entity Engine Guide Hi all, I came across this statement at the Entity Engine Guide page , which I am not very sure what does it mean and would like to seek for advise: "The primary key for a view entity should include all primary key fields of each member entity of the view. This means that all of the primary key fields must be aliased, but fields that are used to link the entities need only be aliased once. For example, if an OrderHeader entity has an orderId primary key and an OrderLine entity has an orderId primary key and an orderLineSeqId primary key, and the orderId was the mapped key that links the two entities, then the view entity would have two primary keys: orderId and orderLineSeqId. In this case orderId would only be aliased once since by definition the orderIds from each entity will always be the same since that is how the entities are linked (or joined)." Is there anybody that can help me to elaborate more? or maybe provide me the sample configuration based on the sample given on the statement? Moreover, I am much appreciate if someone can really help me to explain the sample configuration on the Entity Engine Guide about View-Entities. It is a great idea that we can do join queries using View-Entities but I believe I need more guidance and explanation in order for me to really understand it. the sample is pasted below: ... _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
|
|
In reply to this post by Gan Chun Yee
Hi Gan,
Nice to hear that my answer helped you in some way. Unfortunately I'm not able satisfy your latest request; I simply don't have the time. Can only give this tip: If you rewrite your query in this manner: SELECT a.aid, b.bname, c.cdescription FROM tableA a, tableB b, tableC c WHERE b.bid = a.aid, c.cid = b.bid ..you'll se that's a bit easier to translate it to the OFBiz XML syntax. Good luck! </Firas> -----Original Message----- From: Gan Chun Yee [mailto:[hidden email]] Sent: Tuesday, September 13, 2005 5:52 PM To: Firas A. Subject: RE: [OFBiz] Users - Question about Entity Engine Guide Hi Firas, Thank you for the description and now i have better understanding on it. However, I will appreciate if you can help me to show the configuration that will generate this outcome: SELECT a.aid, b.bname, c.cdescription FROM TableA a LEFT OUTER JOIN (TableB b LEFT OUTER JOIN TableC c ON c.cid = b.bid ) ON b.bid = a.aid Thanks in advance. Regards, Gan --- "Firas A." <[hidden email]> wrote: > Hello Gan, > > I'm not an expert on OFBiz but I think I'm able to help with this one. > I'll base my explaination on a concrete example from the > entitymodel.xml. > > In entitymodel.xml you'll find two elements: > ProductStoreShipmentMeth and > ProductStoreShipmentMethView. The former is beign the entity > denfinition of the product_store_shipment_meth database table (note > the underscores). The second one is a query definition, which will > ultimately be translated into an SQL query by the entity engine. Let's > take a closer look at this definition. > > Under ProductStoreShipmentMethView you'll find one or more lines like > this > one: > <member-entity entity-alias="PSSM" > entity-name="ProductStoreShipmentMeth"/> > > Such a line will declare those tables that will be used in this query. > In the example above, we will refer to the ProductStoreShipmentMeth > table, product_store_shipment_meth, by the PSSM alias (which > corresponds to an SQL table alias). So far this translates to the > following SQL: > SELECT * FROM product_store_shipment_meth AS PSSM > > Using this alias we will then choose what columns we want to retreeve > the data for, for example: > <alias entity-alias="PSSM" > name="productStoreId"/> > means that we want to retrieve the values of product_store_id column > from the product_store_shipment_meth table (alias PSSM). > And the corresponding > SQL: > SELECT product_store_id FROM > product_store_shipment_meth AS PSSM > > Then comes a view-link element: > <view-link entity-alias="PSSM" > rel-entity-alias="SM"> > <key-map field-name="shipmentMethodTypeId"/> > </view-link> > > Under the view-link element we define how tables (or > entities) should be > joined. In the above example, PSSM and SM (alias for > ShipmentMethod) will be > joined on the shipment_method_type_id field. And the > pseudo-SQL: > SELECT product_store_id... > FROM product_store_shipment_meth AS PSSM, shipment_method AS SM WHERE > PSSM.shipment_method_type_id = SM.shipment_method_type_id > > After that comes the relation element: > <relation type="one-nofk" title="Include" > rel-entity-name="Geo"> > <key-map field-name="includeGeoId" > rel-field-name="geoId"/> > </relation> > The relation element gives you the ability to inlclude a table, not > previously declared in entitymodel.xml, in your query. In the example > above Geo has not been declared as an entity in the entitymodel.xml. > The nested key-map element defines the criteria for this join, > i.e: > ... PSSM.include_geo_id = geo.geo_id... > Since the geo table has not been declared as an entity erlier, I > belive that all of its columns will be returned in the query result. > Which leads us to the final pseudo-SQL query: > > SELECT product_store_id, ...more columns..., > PSSM.include_geo_id AS "Include", > PSSM.exclude_geo_id AS "Exclude", geo.* FROM > product_store_shipment_meth AS PSSM, shipment_method AS SM, geo WHERE > PSSM.shipment_method_type_id = SM.shipment_method_type_id AND > PSSM.include_geo_id = geo.geo_id OR PSSM.exclude_geo_id = > geo.geo_id > > As you can see the the entity engine is capable of processing nested > queries. You can either modify en existing entity-view or create your > own one. I myself have modified the ProductStoreShipmentMethView we > talked about here. > > Hope this'll help. > > My regards, > > </Firas> > > > > -----Original Message----- > From: [hidden email] [ > <mailto:[hidden email]> > mailto:[hidden email]] On Behalf Of Gan Chun Yee > Sent: Friday, September 09, 2005 5:17 AM > To: [hidden email] > Subject: [OFBiz] Users - Question about Entity Engine Guide > > Hi all, > I came across this statement at the Entity Engine Guide page , which I > am not very sure what does it mean and would like to seek for advise: > > "The primary key for a view entity should include all primary key > fields of each member entity of the view. > This means that all of the primary key fields must be aliased, but > fields that are used to link the entities need only be aliased once. > For example, if an OrderHeader entity has an orderId primary key and > an OrderLine entity has an orderId primary key and an orderLineSeqId > primary key, and the orderId was the mapped key that links the two > entities, then the view entity would have two primary keys: orderId > and orderLineSeqId. In this case orderId would only be aliased once > since by definition the orderIds from each entity will always be the > same since that is how the entities are linked (or joined)." > > Is there anybody that can help me to elaborate more? > or maybe provide me the sample configuration based on the sample given > on the statement? > > Moreover, I am much appreciate if someone can really help me to > explain the sample configuration on the Entity Engine Guide about > View-Entities. It is a great idea that we can do join queries using > View-Entities but I believe I need more guidance and explanation in > order for me to really understand it. > the sample is pasted below: > ... > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com _______________________________________________ Users mailing list [hidden email] http://lists.ofbiz.org/mailman/listinfo/users |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |