SQL Views

Owned by Tomasz Landowski (Unlicensed)
Last updated: May 05, 2014
3 min read

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 each 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

Next step is write some 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" >	
	<component type="window" name="window" reference="window">
		<ribbon>
        	<group template="gridNewAndCopyAction" />
        	<group template="gridActivateAndDeactivateAction" />
       	 	<group template="gridGenericExportAction" />
		</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 allowed only from item details. If it is necessary to allow 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.