Window functions in propel?

victor.vanherpt
victor.vanherpt Spryker Solution Partner Posts: 62 🪐 - Explorer
edited February 12 in Propel ORM

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.

Tagged:

Best Answer

  • Oleksandr Peresypkin
    Oleksandr Peresypkin Lead Demo Engineering Sprykee Posts: 24 ✨ - Novice
    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 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();
    

Answers

  • Oleksandr Peresypkin
    Oleksandr Peresypkin Lead Demo Engineering Sprykee Posts: 24 ✨ - Novice
    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 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
    profuel SSA Sprykee Posts: 78 🪐 - Explorer

    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