Query in Wordpress of a Custom Post Type with the postmeta

Question

I am trying to make a wordpress shortcode with a query. I have to make a query of how many orders were made of the products in the last day and in the last week.

I don't know how to filter by date (the date is also in text format). Thank you very much.

Until now I did this:

The Table:

post_id	meta_key	meta_value
164	order_date	09-12-2020 18:17
164	order_items_5_order_product	chcolate
164	order_items_6_order_product	menta
165	order_date	05-12-2020 14:35
165	order_items_1_order_product	chcolate
165	order_items_2_order_product	menta
165	order_items_3_order_product	granizo
166	order_date	02-10-2020 11:37
166	order_items_5_order_product	chcolate
166	order_items_6_order_product	menta

Query:

SELECT meta_value, COUNT(*) Totales_Pedidos FROM tc_10_postmeta um WHERE meta_key LIKE "order_items_%_order_product" GROUP BY meta_value ORDER BY Totales_Pedidos desc

Result:

meta_key	Totales_Pedidos
chcolate	3
menta	3
granizo	1

Shortcode:

function fln_table_shortcode() {
 global $wpdb;
 $html = "";
 $result = $wpdb->get_results('SELECT meta_value, COUNT(*) Totales_Pedidos FROM tc_10_postmeta um WHERE meta_key LIKE "order_items_%_order_product" GROUP BY meta_value ORDER BY Totales_Pedidos desc');
 $count = $wpdb->num_rows;
 if($count >0){
  $html .="<table>";    
    $html .="<tr><td><strong>PRODUCT</strong></td><td><strong>NUMBER OF ORDERS</strong></td></tr>";
    $html .="<tr>";
  foreach($result as $r){
    $html .="<td>".$r->meta_value."</td>";
    $html .="<td>".$r->Totales_Pedidos."</td>";
    $html .="</tr>";
   }
  $html .="</table>";
}

return $html;
}
add_shortcode( 'table_shortcode','fln_table_shortcode');

Answer 1

Basically you must use a pivot table and prepared statements to generate a dynamic table

CREATE TABLE tc_10_postmeta 
    (`post_id` int, `meta_key` varchar(27), `meta_value` varchar(16))
;
    
INSERT INTO tc_10_postmeta 
    (`post_id`, `meta_key`, `meta_value`)
VALUES
    (164, 'order_date', '09-12-2020 18:17'),
    (164, 'order_items_5_order_product', 'chcolate'),
    (164, 'order_items_6_order_product', 'menta'),
    (165, 'order_date', '05-12-2020 14:35'),
    (165, 'order_items_1_order_product', 'chcolate'),
    (165, 'order_items_2_order_product', 'menta'),
    (165, 'order_items_3_order_product', 'granizo'),
    (166, 'order_date', '02-10-2020 11:37'),
    (166, 'order_items_5_order_product', 'chcolate'),
    (166, 'order_items_6_order_product', 'menta')
;

SELECT
    `post_id`,
    MAX(IF(`meta_key` = 'order_date', STR_TO_DATE(`meta_value`, '%m-%d-%Y %H:%i'), NULL ))  as ORDERDate
    , SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'chcolate',1,0)) as 'chcolate'
    ,SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'menta',1,0)) as 'menta'
    ,SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'granizo',1,0)) as 'granizo'
FROM tc_10_postmeta
GROUP BY `post_id`

post_id | ORDERDate           | chcolate | menta | granizo
------: | :------------------ | -------: | ----: | ------:
    164 | 2020-09-12 18:17:00 |        1 |     1 |       0
    165 | 2020-05-12 14:35:00 |        1 |     1 |       1
    166 | 2020-02-10 11:37:00 |        1 |     1 |       0

SET @sql ='';
SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = "',
     `meta_value`,
      '",1,0)) AS "',
      `meta_value`, '"'      
    )
  ) 
  INTO @sql
FROM tc_10_postmeta
WHERE `meta_key`  LIKE "order_items_%_order_product";
SET @sql = CONCAT('select `post_id`
                  ,MAX(IF(`meta_key` = "order_date", STR_TO_DATE(`meta_value`, "%m-%d-%Y %H:%i"), NULL ))  as ORDERDate
                  ,',@sql,' from tc_10_postmeta
                  group by `post_id`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

post_id | ORDERDate           | chcolate | granizo | menta
------: | :------------------ | -------: | ------: | ----:
    164 | 2020-09-12 18:17:00 |        1 |       0 |     1
    165 | 2020-05-12 14:35:00 |        1 |       1 |     1
    166 | 2020-02-10 11:37:00 |        1 |       0 |     1

db<>fiddle here