PrestaCategories activates the turbo. How to create 5000 categories in 2 min

Posted on8 Years ago by

For this major update of PrestaCatégories, almost all SQL queries and mass treatment were rewritten with one goal in mind, optimization without compromise to save you even more time, much time. Judge for yourself ...

Launch and connection phase

If with the small bases of less than 500 categories this step was painless, those managing sites of 5000 to 50000 famillies had to wait several minutes for the entire tree to be read and displayed. Now only the first level of the tree is loaded at launch, the other levels are loaded on the fly when the needed.

But even on very small databases, you will enjoy the comfort of running categories refresh in seconds.

Test scale, connecting on a site of 40,000 categories:

Temps de lancement

The mode of operation for this launch speed and all other accelerations described below is called "progressive loading".

Is chosen at the time of connection and can be changed from the main interface at any time:

Check "Select reading mode" to open this window and select this new method or the old one (preserved for old versions of PrestaShop 1.3):

Select reading mode

Once connected, you can switch from one mode to another (it is recommended to stay in the new mode as much as possible):
Select progressive loading mode

Also in the side panel, you will find another important parameter:
Automatic re-sync of breadcrumbs data

Leave this selected unless you are certain to work alone on categories during your session. If other people or yourself use the back office or another instance of PrestaCatégories, leave this option checked.

Otherwise, you can uncheck it to further accelerate the software.

Enabling and disabling a series of categories:

Users often do this on many categories at once, so we really pushed the optimization to its maximum.

Activation test of 10,000 categories (if made with PrestaShop, one by one, it would take 3 months):

Temps d'activation d'une série de catégories

Remove categories:

Deletion of 1000  categories in one click:

TEmps de suppression d'un lot de catégories

It is even so fast that it became dangerous. So in addition to the confirmation request, a counter is now indicating you the total number of categories that will be removed (including all invisible subcategories), before the action. And to avoid you to click a second time if you have not seen the hourglass turn a few hundredths of a second, we have also added a ending text message, indicating the completion of the delete operation.

Creating a large number of subcategories, in a multitude of categories:

For most of you, PrestaCatégories is primarily a tool to create the families tree quickly. So it has to be efficient (fast) and practice.

1st axis of improvement, speed. Example, creation of 10 000 categories in a huge database that already contains 100,000 :

Ajout en masse de catégories

2nd axis of improvement, the practical side of it.

Here is the new interface for creating mass categories,. This form pemits to pre-fill the name of the categories, their url, their meta title, with the site name, the name of their parent, free text fields, ID . Also you can decide if they should be enabled or not and to what store(s) to associate it. And automatically associated them to customer groups:
Bulk creation of new families

The name and url can concatenate up to six strings,  the name of the shop, that of the parent category, the identifier and three completely free.

Duplication of branches of categories

Probably one of the most useful and used features  when you have to create a tree with a repetitive structure, typically the spare parts sales sites for cars. This required to have this feature to be also very fast.

Result of an actual test of cloning a set of branches containing more than 1000 categories on four levels:

Temps de lancement

Note : the new script now includes the duplication of discounts applied to categories depending on the clients group.

Bulk displacement of categories

Perhaps the feature that got the highest rate of acceleration compared to the previous version. Surely we cannot do even better.

Example, moving two branches of 1000 categories, made of 5 levels of sub-categories:

Temps de déplacement de branches de catégories

Association of categories to shops

This possibility is less known, PrestaCatégories can associate (or remove) hundreds of categories to shop in one click. We re-work on this piece of code searching for a significant speed gain.

Example, link 600 categories (with 4 levels of subcategories) in a new empty store:

Lier des catégories à des boutiques

Cross-association between classes and customer groups

A major weakness of PrestaShop: who has not ever have to link hundreds of categories one by one to a newly created group of customers ? Do you know that PrestaCatégories can create thousands of customers groups to categories associations in a few selections and one click.

The script was already quite fast, but it there was some room for further improvement.

Case in point, activate three customer groups in 1000 new product families:

Lier les groupes de clients et les catégories

Managing products features has not been forgotten. Let's start with the most common action, bulk activation and deactivation of articles

In fact it is now virtually impossible to distinguish between the activation of 100 or 1000 products, in both cases it is almost instantaneous. F

For example 1,000 products activated in :

Temps d'activation d'une série de catégories

Displacement (default category change) of all (or just some) products of selected categories (or whole branches with the recursive mode) to another category.

Is now taking a few seconds instead of several minutes to move thousands of products.

For example about 4000 products across a thousand different categories, displaced in one click into a new empty category:

Temps de déplacements de produits

In addition we added a new window to chain the movements without having to re-select the visibility options. Also permits to add filters to limit the choice of products moved (active or not, with or without images, of a particular supplier, ...):

Products move options

Note : To comply with the behavior of PrestaShop, it is not any longer possible to unlink a product to its default category.

Removing products

What could be more painful than waiting to delete hundreds or thousands of products. However this this is a complex process, because the script must do the cleaning in many linked tables of the database containing information on these products removed. In Prestashop, it is not lesst thanr 15 SQL queries by product that must be executed.

So we reviewed the technical approach from scratch, so that everything is done in less than 10 queries in total, regardless of the number of products to remove.

The result is amazing. Instad of 15 days of work with the back office, this is wh tit takes with PrestaCatégories to remove 12,000 Products:

Temps de suppression de produits

As for categories, management of the association of products with various shops was also boosted

Usefull when you add a new store and forgot to ask PrestaShop to transfer the products from an existing.

Time it takes to transfer 2,000 products from one shop to another:

Temps de déplacements de produits

Distributing products in various categories becomes a joke

When you have to re-organizes your shop, for example when having to makee visible thousands of products in a category "sales", you are usually pressed for time. So having a super fast bulk processing tool can make all the difference with the competition.

Sitolog has paid particular attention to these products transfers between categories, to make them super fast, even on a very large number of items and families.

The result is astonishing, for example making visible 500 products in 5 families, so a total of 2500 associations to achieve:

Temps d'activation d'une série de catégories

Auto Repairs unstable or corrupted databases

The new "progressive loading" mode is very quick thanks to the use of the breadcrumbs. If the breadcrum is wrong for some categories, the above mentioned functions will not give the desired result.

On the other hand it does not go a week without that one asks for assistance to repair his PrestaShop database after having tried a third-party module or made a failed import using PrestaShop. The most common issues are orphaned categories (not belonging to any valid parent category) or the presence of several "Root" categories or the existence of many invisible products.

For these reasons, Sitolog included in PrestaCatégories V6, a set of auto repair tools, including the following management window:

We tried to think of every possible case that we have already met to propose a solution. But it is possible that this toolkit is enriched even further with additional cleaning functions in the future. Are actually included:

Concerning categories:

-Clean Tables containing information on null categories.

-Correct duplicate category "Root" (id_category = 0 AND Nleft = 1).

-Repairr orphaned categories (when id_parent = 0, other than the root category).

-Repair levels (when the level of a sub-category is different from that of its parent + 1).

-Rebuild the Nleft values ​​and Nright breadcrumb.

-Check that the categories are associated with their default store, otherwise link them.

-Check that when a family is associated with a store, its parent categories are associated, otherwise link.

-Check that when a branch is associated with a shop, it does have a name and url rewritten in the database for this store and all languages. Otherwise create the missing data.

-Remove the holes and duplicates in the order (positions) of series of categories.

About products:

-Remove in all tables managing product , lines with id_product set to 0.

-Repair orphaned products (no default category defined or existing).

-Check that the products are associated with their default store, otherwise link them.

-Remove wrong links between products and categories.

-Check that the products are linked to their default category, otherwise link them.

-Remove the holes and duplicates in the order (positions) of series of products.

We found on Presta Addons modules more expensive than PrestaCatégories, and who offer nothing but one or two of the possibilities offered by this new tool. The cost of an upgrade of PrestaCategory can be amortized just by using once this new set of features.

It can be launched from the connection window (useful if the database is in such a state that the connection fails) and from the main window.

Some repair scripts of this list are run by PrestaCatégories during usage. For example, the dynamic reconstruction of breadcrumb or the removing duplicates and gaps in the position values ​​of categories and products are often executed after categories creation or movements.

Changing the display order of products in the front office and in the admin

The "123" button, although very convenient to order a selection of products with a click, was not enough intuitive. So we decided to replace it with a new "step by step" and guided interface:

Products order bulk change

As this was very often suggested, we added the ability to order products via drag and drop.

The same way, categories can now also be reorganized in bulk by a simple drag and drop

Ré-ordonner les catégories par glisser déposer

Other new main features

  • Progress meter or meter long on equities as a cloning and mass creation of new categories.
  • Switching automatic FTP connection mode "Active" when "Passive" is unchecked.
  • Fourth choice of display order, ascending Nleft. This verifies that there is no hole in the Nleft sequence and nrigth
  • As in the BO PrestaShop, a product is now necessarily associated with its default category. We can choose a default category that among the list of categories already associated with the product. We can change the déassocier without first default category.
  • Play branch right or left, automatic sync between trees
  • New checkbox to toggle the display modes of the products bundled by default category or grouped by related categories. This choice becomes independent of inclusion in the column table "Related Categories" listing the Ids of those. Furthermore the addition of the column "position" automatically enables this mode to a more intuitive modification of the order of products.
  • Auto calculation the position of other products when you change the position of one of them in the new utilsant reordering window.
  • Double click online category => recursive read the branch. Furthermore selection recursive option if parent-child is on yes (green men)
  • Single-click online classes => single or recursive selection as option
  • Click on the + => held subcategories already read only
  • New option to automatically un-associate subcategories when dis-associate a category of a shop.
  • New column to define one (or several at once) as category or not type "root"
  • Double click button unfold, any place with full base reading. Simple click: place only branch already read
  • type search magnifying glass "contains" instead of "beginning" in all tables

Corrected bugs found in the previous version

  • SQL errors (duplicate) when cloning several branches of categories
  • 7.0.0.e and V f: lack columns shops in tree categories !!!
  • Reread categories after change choice shop (ps1541), with select all depressed, selects the last empty line => crash if we act on it (as suppression, for example)
  • Increase height separators in table products and centering text height
  • Filter mass activation window of all the products of a selection of categories do not work in all cases.
  • Translation table flags button makes disappear the other buttons of the tab, so should not be visible at first. And one remains.
  • PS 1.3, impossible to change id_parent value categories
  • Changing "position" only saves in 1 shop, while one is in single selection Shop 2 (16100), when the mode save all botuiques is 1
  • Folder Names Recovery bug containing a focus in the window adding photos to the list by choosing and looking for a folder. Accent é gives Windows badly coded characters and thus the image search is not done then.
  • PrestaShop does not require a that a class is associated with its default store => PC amended accordingly
  • PrestaShop does not force the disassociation of children categories to the store when déassocie the parent category of the shop => optional choice in PC.
  • Association of products to shops, correction oblivion duplicate association to carriers. Fixed other errors regarding the management of multi shops (eg amount shared between shop)
  • SQL error when the de-association groups of customers to categories, for the removal of rows in the group table reduction.

Franck Bugnet - Sitolog
Leave a Comment
Leave a Reply

  • Login
    • £ GBP
    • $ USD