I am currently working on a database dat needs to filter a 1.000.000 Products. For this I created 2 wordpress websites. 1 to show the data and 1 for storing the data.
Now I am first building the script in a plugin and displaying it on the data website. But now I keep struggeling with the filtering and I am questioning my current approach to handle over 1.000.000 products. (The products only need to be displayed with a form so it more like a catalog and not really a store.)
So the main question is. Is this the correct approach and is datatables.net a right way of doing this. The second question is how can I fix this filtering problem.
When the filtering is empty and noting is selected in the table I get 0 results so the
<?php
// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;
//encrypt('encrypt', $users[$x][1], $usertokens[$x][2], 'test')
//decrypt('decrypt', $results[$i]->emergencyemailaddress, $results[$i]->token, 'test');
// Add Post title to custom meta
add_action( 'transition_post_status', 'duplicate_title', 10, 3 );
function duplicate_title( $new, $old, $post ) {
if ( $post->post_type == 'autos' ) {
update_post_meta( $post->ID, 'd_title', $post->post_title );
}
}
// Add DataTables Resources
function add_datatables_scripts() {
wp_register_script('datatables', 'https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js', array('jquery'), true);
wp_enqueue_script('datatables');
wp_register_script('datatables_bootstrap', 'https://cdn.datatables.net/1.10.13/js/dataTables.bootstrap.min.js', array('jquery'), true);
wp_enqueue_script('datatables_bootstrap');
}
function add_datatables_style() {
wp_register_style('bootstrap_style', 'https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css');
wp_enqueue_style('bootstrap_style');
wp_register_style('datatables_style', 'https://cdn.datatables.net/1.10.13/css/dataTables.bootstrap.min.css');
wp_enqueue_style('datatables_style');
}
add_action('wp_enqueue_scripts', 'add_datatables_scripts');
add_action('wp_enqueue_scripts', 'add_datatables_style');
function autos_datatables_scripts() {
wp_enqueue_script( 'autos_datatables', '/js/autostable.js?ver=1.0', array('jquery'), true );
wp_localize_script( 'autos_datatables', 'ajax_url', admin_url('admin-ajax.php?action=autos_datatables') );
}
// Add a function to retrieve available Merk options
function get_merk_options() {
$terms = get_terms(array(
'taxonomy' => 'car_merk',
'hide_empty' => true, // Include even terms with no posts
));
$options = array();
foreach ($terms as $term) {
$options[] = $term->name;
}
return $options;
}
// Modify your shortcode or HTML to generate checkboxes dynamically
function generate_merk_checkboxes() {
$merk_options = get_merk_options();
$dropdown_html = '<div>';
$dropdown_html .= '<label for="filter-merk">Selecteer merk:</label>';
$dropdown_html .= '<select id="filter-merk" class="filter-merk">';
$dropdown_html .= '<option value="">Selecteer merk</option>';
foreach ($merk_options as $option) {
$dropdown_html .= '<option value="' . $option . '">' . $option . '</option>';
}
$dropdown_html .= '</select>';
$dropdown_html .= '</div>';
return $dropdown_html;
}
// Update your autos_datatables function to include the generated checkboxes
function autos_datatables() {
autos_datatables_scripts();
ob_start(); ?>
<div>
<?php echo generate_merk_checkboxes(); ?>
</div>
<table id="autostable" class="table table-striped table-hover">
<thead>
<tr>
<th>Auto title</th>
<th>brandstof</th>
<th>Merk</th>
<th>bouwjaar</th>
<th>afbeelding</th>
</tr>
</thead>
</table>
<?php return ob_get_clean();
}
add_shortcode ('autos_datatables', 'autos_datatables');
add_action('wp_ajax_autos_datatables', 'datatables_server_side_callback');
add_action('wp_ajax_nopriv_autos_datatables', 'datatables_server_side_callback');
function datatables_server_side_callback() {
header("Content-Type: application/json");
$request = $_GET;
$columns = array(
0 => 'post_title',
1 => 'car_brandstof',
2 => 'car_merk', // Assuming 'Merk' is the third column
3 => 'car_bouwjaar',
4 => 'image_0',
);
$args = array(
'post_type' => 'cars',
'post_status' => 'publish',
'posts_per_page' => $request['length'],
'offset' => $request['start'],
'order' => $request['order'][0]['dir'],
);
if ($request['order'][0]['column'] == 0) {
$args['orderby'] = $columns[$request['order'][0]['column']];
} elseif ($request['order'][0]['column'] == 1 || $request['order'][0]['column'] == 2) {
$args['orderby'] = 'meta_value_num';
$args['meta_key'] = $columns[$request['order'][0]['column']];
}
// Update the query to handle the merk_filter parameter
if (!empty($request['merk_filter'])) {
$merk_terms = array_map('sanitize_text_field', $request['merk_filter']);
$args['tax_query'][] = array(
'taxonomy' => 'car_merk',
'field' => 'name',
'terms' => $merk_terms,
'operator' => 'IN',
);
}
// Add condition to include all posts when merk_filter is empty
if (empty($request['merk_filter'])) {
$args['tax_query'][] = array(
'taxonomy' => 'car_merk',
'operator' => 'EXISTS', // Include all posts
);
}
// Other meta queries and search query
$autos_query = new WP_Query($args);
$totalData = $autos_query->found_posts;
$data = array();
if ($autos_query->have_posts()) {
while ($autos_query->have_posts()) {
$autos_query->the_post();
// Your code to populate $data with results
$nestedData = array();
$nestedData[] = get_the_title();
$terms = get_the_terms(get_the_ID(), 'car_brandstof');
// Brandstof
if ($terms && !is_wp_error($terms)) {
$brandstof = array();
foreach ($terms as $term) {
$brandstof[] = $term->name;
}
$nestedData[] = implode(', ', $brandstof);
} else {
$nestedData[] = '';
}
// Get the terms for the 'car_merk' taxonomy
$merk_terms = get_the_terms(get_the_ID(), 'car_merk');
if ($merk_terms && !is_wp_error($merk_terms)) {
$merk = array();
foreach ($merk_terms as $term) {
$merk[] = $term->name;
}
$nestedData[] = implode(', ', $merk);
} else {
$nestedData[] = 'geen merk';
}
// Get the terms for the 'car_merk' taxonomy
$merk_terms = get_the_terms(get_the_ID(), 'car_bouwjaar');
if ($merk_terms && !is_wp_error($merk_terms)) {
$merk = array();
foreach ($merk_terms as $term) {
$merk[] = $term->name;
}
$nestedData[] = implode(', ', $merk);
} else {
$nestedData[] = '';
}
// Get the URL of the first image and decode it from base64
$image_base64 = get_post_meta(get_the_ID(), 'image_0', true);
$image_url = 'data:image/jpeg;base64,' . $image_base64;
// Wrap the image URL with an <img> tag
$image_tag = '<img width="50px" src="' . $image_url . '" alt="' . get_the_title() . '">';
// Add the image tag to the nested data
$nestedData[] = $image_tag;
// Add the nested data to the main data array
$data[] = $nestedData;
}
wp_reset_postdata();
wp_reset_query();
}
$json_data = array(
"draw" => intval($request['draw']),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalData),
"data" => $data,
);
echo json_encode($json_data);
wp_die();
}
?>
Javascript:
$ = jQuery;
$(document).ready(function() {
// DataTables initialization
var table = $('#autostable').DataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": ajax_url,
"data": function(d) {
// Get an array of checked Merk values
var checkedMerks = $('.filter-merk').map(function() {
return $(this).val();
}).get();
// If no selection is made, pass an empty array to the server
if (checkedMerks.length === 0) {
checkedMerks = [''];
}
// Add the merk_filter parameter to the data object
d.merk_filter = checkedMerks;
return d;
}
}
});
// Checkbox change event handler
$(document).on('change', '.filter-merk', function() {
// Reload DataTables with new filter criteria
table.ajax.reload();
});
});