Window functions in propel?

victor.vanherpt · February 12

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? https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

I didn't find any docu in propel's site ( https://propelorm.org/documentation/reference/model-criteria.html ).

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.

Oleksandr Peresypkin · February 12

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 to skipSql and readOnly attributes. The skipSql attribute is set to true to avoid generating SQL for the view. The readOnly attribute is set to true 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 in src/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();

Oleksandr Peresypkin · February 12

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 to skipSql and readOnly attributes. The skipSql attribute is set to true to avoid generating SQL for the view. The readOnly attribute is set to true 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 in src/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();

profuel · February 12

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

Window functions in propel?

Best Answer

Answers

Categories