Calling Developers!
We are reenergizing our code contribution process! Learn More

What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

Hey, can anyone tell me how we can use CTE method to import categories? OR give me an eg of using CT

Options
U03PVQ3MDCP
U03PVQ3MDCP Posts: 6 🧑🏻‍🚀 - Cadet

Hey, can anyone tell me how we can use CTE method to import categories? OR give me an eg of using CTE method for importing in Spryker??

Comments

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer
    edited August 2022
    Options

    To my knowledge CTE* are not supported in MariaDB, so if you run in the Spryker Cloud you might not be able to use them.

    They also seem to be removed from the demo shops, so I assume that this method is no longer supported at all.

    Such a CTE can still be found in the documentation at https://docs.spryker.com/docs/pbc/all/price-management/tutorials-and-howtos/howto-handle-twenty-five-million-prices-in-spryker-commerce-os.html#[…]s, which you might use as blue print.
    But it's no longer supported for a reason. As Spryker decided to go with MariaDB for the Spryker Cloud they can no longer use the unnest function, which was the magic part of the CTE based importers, and building those function in a database function on your own might be a lot slower than the native DB support Postgres offers.
    There is a 7 years old feature request for MariaDB to implement unnest but seems it was never developed: https://jira.mariadb.org/browse/MDEV-7417

    For your exact case, importing categories, I would not use CTE's at all as a big part of the category import is to rebuild the closure table to transform flat categories into a tree structure.
    While it is possible to do this in native SQL, we have done this in a project, it's a hell of complexity. But without putting the closure table calculation into the database as well, you might not increase the performance at all, except you have millions of categories and writing them to the database is your bottleneck.

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer
    Options
    • To clarify, CTE's are supported by MariaDB, , as this means common table expressions (e.g.:
    WITH <name> AS (SELECT ...)
    

    ). But I have read the question in regard to CTE's in the context of importers, where the CTE itself is supported, but unnest is not, which is one of the parts that made it work for imports.

  • U03PVQ3MDCP
    U03PVQ3MDCP Posts: 6 🧑🏻‍🚀 - Cadet
    Options

    Thanks @UL6DGRULR for such a thorough answer 🙂

  • U03PVQ3MDCP
    U03PVQ3MDCP Posts: 6 🧑🏻‍🚀 - Cadet
    Options

    Also, @UL6DGRULR can you give me some insight of how to import categories then with a traditional method and less complexity?