Recently we had a client who needed to launch a store with 115K of products, filter them by multiple categories and provide up to 3 seconds load speed for all pages at a low cost of hosting.
As the client meant to launch an online bookshop then you can imagine how many taxonomies and terms filter may have. So below we will how to create a WordPress multiple taxonomy filter for a big amount of products in Woocommerce.
So, at first it was sound like a challenge for a developer who worked on the project. Because, as you know, basically, WordPress does not allow working with a large number of goods and taxonomies.
But you would probably say that no problem, there are tons of ready-made plugins out there. Well we thought so, and as turned you can’t have the sweet without the sour.
In the process of implementing the filter of goods, we went through the following stages:
- Firstly, we of course tried to use ready-made plugins. As a result, none of them could process all taxonomies with terms included in them, which we have a total of 83924 items.
- Then, we tried to display the filter with standard functions of WordPress and W As a result – got the same problem as ready-made plugins. We were faced with the memory limit for processing php request and category page simply refused to load, yielding Fatal Error: Allowed Memory Size.
- So It was further decided to abandon the use of standard functions and to receive an information by direct optimized database queries from each category. But due to the fact that there are too many products in categories (near 28,000 + in some) that need to be processed on the fly, receiving information links of each product with different taxonomies and their terms, as well as filtering them by popularity and discount percentage, the most we could achieve is to get a ready-made category page with generated filter elements in 10-30 seconds. Depending on the number of products included in the category and the number of their links with taxonomies.
- On the next step was decided to write a plug-in that will process all goods and their connections in json format, save the data in one file and so at the front you can get the filter parameters without accessing the database and the tables necessary for the filter in it.The only drawback of this solution is the client, after working with products or categories (adding, deleting, changing), need to press a special button in the admin panel that will update the data in our json file so that the data in the filters become relevant.
Later, we faced again the problem of generating json with data for all 83924 taxonomies. In order to add data to the same file for each taxonomy, you must first read the already recorded file. When the json file size exceeded 10mb, the script again did not have enough time to read the file before writing.
In total, we need to process 83924 subqueries in one request, for each of them should be made 11 interrelated calls with different parameters to five separate database tables.
It was necessary to optimize the plug-in so that for each taxonomy and terms included in it a separate json file was recorded with the necessary data from the database. The record should be broken into iterations, each of which will work out alternately with a slight delay.
How the plugin works.
We pass to the callback function the value of the step in which we are
if( isset($_POST['step']) && $_POST['step'] != '' )
{
$step = $_POST['step'];
}
Then, if this is the first iteration, we delete the old json files and count the number of terms in the desired taxonomies in order to calculate the required number of iterations for recording.
if ($step === 'step_start') {
unlink(plugin_dir_path( __FILE__ ).'tax_name.json');
$tax_name = $wpdb->get_results( "SELECT term_taxonomy_id
FROM wp_term_taxonomy
WHERE taxonomy LIKE 'tax_name'
" );
...
$data[] = array (
'tax_name' => count($tax_name),
...
);
$json = json_encode($data, true);
...
}
Next we get to the repeater step, in which we transfer the name of the taxonomy in the processing and the number of already processed terms, get a list of all products and go through the conditions necessary for each filter group, collecting json for each taxonomy with a specific set of data.
else if ($step === 'step_repeat') {
if( isset($_POST['tax']) && $_POST['tax'] != '' )
{
$taxonomy = $_POST['tax'];
}
$file = file_get_contents(plugin_dir_path( __FILE__ ).$taxonomy.'.json');
$taskList = json_decode($file,TRUE);
if( isset($_POST['recorded_up']) && $_POST['recorded_up'] != '' )
{
$recorded = $_POST['recorded_up'];
}
$data = $wpdb->get_results( "SELECT term_taxonomy_id, termtax.taxonomy, termtax.parent
FROM wp_term_taxonomy termtax
WHERE termtax.taxonomy LIKE '".$taxonomy."'
ORDER BY termtax.taxonomy ASC LIMIT 100 OFFSET ".$recorded."" );
$taxonomy_data = [];
foreach ($data as $tax) {
$products_dat = $wpdb->get_results( "SELECT wtr.object_id
FROM wp_term_relationships wtr
WHERE wtr.term_taxonomy_id = '.$tax->term_taxonomy_id.'
" );
$product_data = [];
if ($products_dat) {
foreach ($products_dat as $t_dat) {
$product_data[] = $t_dat->object_id;
}
}
...
$taxonomy_data = array(
'parameter_name' => $tax->term_taxonomy_id,
...
);
$taskList[] = $taxonomy_data;
}
}
file_put_contents(plugin_dir_path( __FILE__ ).$taxonomy.'.json',json_encode($taskList));
unset($taskList);
...
}
Having thus prepared 7 main files with data for each taxonomy, products included in it and their relations among themselves, other taxonomies and their terms, we were able to increase the page loading speed in the most voluminous taxonomy from 30 seconds to 3.
When calling a taxonomy or term page, we check the id of the parent taxonomy, depending on this, parsing a certain json, pulling from it a list of incoming taxonomies and terms for products belonging to the current product category.
After all taxonomies are processed and files are written, we can parse them in a template and get the necessary data for the current taxonomy.
if (is_product_category()) {
$content = file_get_contents(plugins_url('plugin_name/json/product_cat.json'));
} else if (...){
...
}
foreach (json_decode($content, true) as $events) {
if ($events['tax_id'] == $category_id) {
if (count($events['tax_name']) != 0) {
$tax_name[] = $events['tax_name'];
}
...
}
}
So we just showed our path to completing such a goal. Hope that experience was helpful. If you also faced with such kind of tasks, please share your thoughts in the comments below.
Get a Custom Solution with Web Design Sun
At Web Design Sun, we specialize in building web applications for clients in every business and industry. If you’re interested in custom applications for your business, contact us today.
Contact us today to get started