...
| Code Block | ||
|---|---|---|
| ||
<?xml version="1.0" encoding="UTF-8"?> <model name="productCategoryListItem" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schema.qcadoo.org/model" xsi:schemaLocation="http://schema.qcadoo.org/model http://schema.qcadoo.org/model.xsd" activable="false" deletable="false" auditable="false" insertable="false" updatable="false"> <fields> <string name="name" unique="true" required="true" /> <belongsTo name="productGroup" model="productGroup" required="true"/> <decimal name="productsAvgPrice" /> <hasMany name="products" joinField="productCategory" model="product" cascade="nullify" /> </fields> <hooks /> </model> |
This is a realy simple view model with 4 fields, the most important optins here are: activable, deletable, auditable, insertable, updatable set to false. As I mentioned before SQL views are readonly and this options account that fact.
...
| Code Block | ||
|---|---|---|
| ||
DROP TABLE IF EXISTS basic_productcategorylistitem; CREATE OR REPLACE VIEW basic_productcategorylistitem AS WITH productcategory_prices AS (SELECT avg(baseprice) AS price, productcategory_id from basic_product group by productcategory_id) SELECT pc.id, pc.name, pc.productgroup_id, pcp.price as productsavgprice FROM basic_productcategory pc LEFT OUTER JOIN productcategory_prices pcp ON pcp.productcategory_id = pc.id |
In SQL script we drop sql table if exists. This table will be created by hibernate when application starts first time. Then we create view with exactly the same name. This view is sql query that calculates avg price of each category.
Use our view
We can now use our SQL view in Qcadoo view. In our example we want to see list of categories with additional column which contains avg price. So lets create list of categories:
| Code Block | ||
|---|---|---|
| ||
<?xml version="1.0" encoding="UTF-8"?>
<view xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schema.qcadoo.org/view"
xsi:schemaLocation="http://schema.qcadoo.org/view http://schema.qcadoo.org/view.xsd"
name="productCategoriesList" modelName="productCategoryListItem" menuAccessible="true"
defaultAuthorizationRole="ROLE_PRODUCT_VIEW">
<component type="window" name="window" reference="window">
<ribbon>
<group template="gridNewAndCopyAction" defaultAuthorizationRole="ROLE_PRODUCT_ADD"/>
<group template="gridActivateAndDeactivateAction" defaultAuthorizationRole="ROLE_PRODUCT_EDIT"/>
<group template="gridGenericExportAction" defaultAuthorizationRole="ROLE_PRODUCT_EXPORT"/>
</ribbon>
<component type="grid" name="productCategories" reference="grid" defaultAuthorizationRole="ROLE_PRODUCT_EDIT">
<option type="column" name="name" fields="name" link="true" />
<option type="column" name="productGroup" fields="productGroup" expression="#productGroup['name']"/>
<option type="column" name="productsAvgPrice" fields="productsAvgPrice" />
<option type="order" column="name" direction="asc"/>
<option type="correspondingView" value="basic/productCategoryDetails" />
<option type="correspondingComponent" value="form" />
<option type="searchable" value="name,productGroup,productsAvgPrice"/>
<option type="orderable" value="name,productGroup,productsAvgPrice"/>
<option type="multiSearch" value="name,productGroup,productsAvgPrice"/>
<option type="fullscreen" value="true"/>
<option type="multiselect" value="true" />
</component>
<option type="fixedHeight" value="true" />
<option type="header" value="false" />
</component>
</view> |
As you can see this is standard qcadoo grid view, but you need to remember about:
Removing from that grid should be disabled, because it will try to remove SQL View item and this is impossible. In this example you can see that there is no ribbon template called gridRemoveAction. Removing will be alloved only from item details. If it is necessary to allove removing from grid then you will have to implement your own event listener for that.
correspondingView of the grid can't use our SQL View as a source model. It will have to use productCategory model for that. Our view use product category id as its own id field so there should not be conflict when you open product category details from our grid.