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