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

U03PVQ3MDCP

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

Alberto Reyer

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

• 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

Thanks @UL6DGRULR for such a thorough answer 🙂

U03PVQ3MDCP

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