Window functions in propel?
I think it's not supported by propel orm, but just in case:
Is anyone doing window functions (I.E. over()… OVER( partition by…) With propel orm?
I didn't find any docu in propel's site (
).
I can think of a workaround via aggregate columns, but I'd rather not change structure for some calculations. I am trying to skip iterating over all the results in order to get some specific sums.
I guess I could do custom sql queries as well.
Best Answer
-
You could make use of SQL VIEWs. The following is an example of how to achieve this.
- Define the path in
config/Shared/config_propel.php
to custom SQL scripts so that they are committed to the repository:
$config[PropelConstants::PROPEL] = [ ... 'paths' => [ ... 'sqlDir' => APPLICATION_ROOT_DIR . '/src/Pyz/Propel/Sql', ... ], ... ];
- Create an SQL script in
src/Pyz/Propel/Sql
. For example,order_totals.sql
:
create or replace view order_totals (store, average, total, average_by_store, total_by_store) as select spy_sales_order.store, avg(spy_sales_order_totals.grand_total) over () as average, sum(spy_sales_order_totals.grand_total) over () as total, avg(spy_sales_order_totals.grand_total) over (partition by spy_sales_order.store) as average_by_store, sum(spy_sales_order_totals.grand_total) over (partition by spy_sales_order.store) as total_by_store from spy_sales_order inner join spy_sales_order_totals on spy_sales_order.id_sales_order = spy_sales_order_totals.fk_sales_order;
- Along with the SQL script, create the file
sqldb.map
(see https://propelorm.org/documentation/cookbook/adding-additional-sql-files.html) in the same directory with the content:
# Sqlfile -> Database map order_totals.sql=zed
- Create the schema file
order_totals_schema.xml
for the view. It's needed to get the classes generated to use them as usual persistence classes. Pay attention toskipSql
andreadOnly
attributes. TheskipSql
attribute is set totrue
to avoid generating SQL for the view. ThereadOnly
attribute is set totrue
to avoid generating the data modification methods (save
,delete
, etc.) in the generated classes.
<?xml version="1.0" encoding="UTF-8"?> <database ... > <table name="order_totals" skipSql="true" readOnly="true"> <column name="store" type="VARCHAR"/> <column name="average" type="FLOAT"/> <column name="total" type="INTEGER"/> <column name="average_by_store" type="FLOAT"/> <column name="total_by_store" type="INTEGER"/> </table> </database>
- Run
vendor/bin/console propel:install
to generate the classes. - Run
vendor/bin/console propel:sql:insert
to run the scripts insrc/Pyz/Propel/Sql and
create the view in the database. - Now, you'll be able to use the VIEW the same way as the usual ORM functionality:
$orderTotals = OrderTotalsQuery::create() ->where(...) ->limit(...) ->find();
$orderTotals->getFirst()->getTotalByStore();
0 - Define the path in
Answers
-
You could make use of SQL VIEWs. The following is an example of how to achieve this.
- Define the path in
config/Shared/config_propel.php
to custom SQL scripts so that they are committed to the repository:
$config[PropelConstants::PROPEL] = [ ... 'paths' => [ ... 'sqlDir' => APPLICATION_ROOT_DIR . '/src/Pyz/Propel/Sql', ... ], ... ];
- Create an SQL script in
src/Pyz/Propel/Sql
. For example,order_totals.sql
:
create or replace view order_totals (store, average, total, average_by_store, total_by_store) as select spy_sales_order.store, avg(spy_sales_order_totals.grand_total) over () as average, sum(spy_sales_order_totals.grand_total) over () as total, avg(spy_sales_order_totals.grand_total) over (partition by spy_sales_order.store) as average_by_store, sum(spy_sales_order_totals.grand_total) over (partition by spy_sales_order.store) as total_by_store from spy_sales_order inner join spy_sales_order_totals on spy_sales_order.id_sales_order = spy_sales_order_totals.fk_sales_order;
- Along with the SQL script, create the file
sqldb.map
(see https://propelorm.org/documentation/cookbook/adding-additional-sql-files.html) in the same directory with the content:
# Sqlfile -> Database map order_totals.sql=zed
- Create the schema file
order_totals_schema.xml
for the view. It's needed to get the classes generated to use them as usual persistence classes. Pay attention toskipSql
andreadOnly
attributes. TheskipSql
attribute is set totrue
to avoid generating SQL for the view. ThereadOnly
attribute is set totrue
to avoid generating the data modification methods (save
,delete
, etc.) in the generated classes.
<?xml version="1.0" encoding="UTF-8"?> <database ... > <table name="order_totals" skipSql="true" readOnly="true"> <column name="store" type="VARCHAR"/> <column name="average" type="FLOAT"/> <column name="total" type="INTEGER"/> <column name="average_by_store" type="FLOAT"/> <column name="total_by_store" type="INTEGER"/> </table> </database>
- Run
vendor/bin/console propel:install
to generate the classes. - Run
vendor/bin/console propel:sql:insert
to run the scripts insrc/Pyz/Propel/Sql and
create the view in the database. - Now, you'll be able to use the VIEW the same way as the usual ORM functionality:
$orderTotals = OrderTotalsQuery::create() ->where(...) ->limit(...) ->find();
$orderTotals->getFirst()->getTotalByStore();
0 - Define the path in
-
Hi, @victor.vanherpt ,
This looks like a nice addition to the Propel as for me, worth adding a suggestion to the Propel ORM community.
Thanks, AndriyT
0
Categories
- All Categories
- 42 Getting Started & Guidelines
- 7 Getting Started in the Community
- 8 Additional Resources
- 7 Community Ideas and Feedback
- 75 Spryker News
- 924 Developer Corner
- 783 Spryker Development
- 88 Spryker Dev Environment
- 362 Spryker Releases
- 3 Oryx frontend framework
- 35 Propel ORM
- 68 Community Projects
- 3 Community Ideation Board
- 30 Hackathon
- 3 PHP Bridge
- 6 Gacela Project
- 25 Job Opportunities
- 3.2K 📜 Slack Archives
- 116 Academy
- 5 Business Users
- 370 Docker
- 551 Slack General
- 2K Help
- 75 Knowledge Sharing
- 6 Random Stuff
- 4 Code Testing
- 32 Product & Business Questions
- 70 Spryker Safari Questions
- 50 Random