Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

Overview

In most of cases the database source of models are SQL tables, there is also another option. You can set SQL views as a souce of the model data. In current version there is no particular API for this case, you can base on fact that hibernate treats SQL views as SQL tables. SQL views in most cases are read only so you have to remember about it when you want to use it. 

Example

Lets consider an example when we need to implement feature: On the list of categories user want to see average price of all products in category.

Model

We will start with qcadoo model:

<?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 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. 

SQL View

Then we will create SQL script to create view in database.

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:
 

<?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.  

 

 

  • No labels