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

SQL Views

Overview

Example

Model

SQL View