Support needed for a query

Question

I need help to finish a SQL Query for a Woocommerce product list. I need this for a wpdatatable sql input.

I searched for a example code and have adapted a found code:

SELECT
  p.ID,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
GROUP BY p.ID,p.post_title

It works but without product rows for variable products. It shows only the main variation of a product.

Example: product steak: I have 6 different variations of steaks as own products with different prices and also different weight characteristics. With my SQL query I can only see the main product "steak" without variations and variation prices.

Table wp_Posts

id   | post_title                     | post_type         | post_parent | post_name | regular_price |
-----+--------------------------------+-------------------+-------------+-----------+---------------|
4113 | Steaks                         | product           | 0           |
4119 | Steaks-Lungenbraten Steak 125g | product_variation | 4113        | steaks_4  |6              |
4120 | Steaks-Hüftsteak 200g          | product_variation | 4113        | steaks_5  |4,4            |
4121 | Steaks-Flankensteak 600g       | product_variation | 4113        | steaks_6  |8,4            |

Table wp_postmeta-

Post_ID |meta_key                | meta_value         |
--------+----------------+----------------------------+---------
4113    |                        | 
4119    | attribute_pa_steaks    | lungenbraten-steak |
4119    | _price.                | 6                  |
4120    | attribute_pa_steaks    | hueftsteak         |
4120    | _price.                | 4,4                |
4121    | attribute_pa_steaks    | flanksteak         |
4121    | _price.                | 8,4                |

wp_term_relationship

object_id.  | term_taxonomy_id. | term_order |
------------+-------------------+------------+
4113        |    6              |    0       |
4113        |   296             |    0       |
4113        |   297             |    0       |
4113        |   298             |    0       |

wp_term_taxonomy

term_taxonomy_id. | term_id  | taxonomy    | description. | parent |count |
------------------+----------+-------------+--------------+--------+------+
296               |   296    |  pa_steaks  |              |  0     | 1.   |
297               |   297    |  pa_steaks  |              |  0     | 1.   |
298               |   298    |  pa_steaks  |              |  0     | 1.   |

wp_terms

term_id     |  name              | slug               | term_group | 
------------+--------------------+--------------------+------------+
296         | Flanksteak         | flanksteak         | 0          |
297         | Hüftsteakk         | hueftsteak         | 0          |
298         | Lungenbraten Steak | Lungenbraten-steak | 0          |

You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
Show us some sample table data and the expected result - as formatted text, not images. And take a look at minimal reproducible example before you start. — jarlh
Example: woo variable product: Name Price Steak variation 1: Flank steak 8,4 variation 2: Hüftsteak. 4,4 Variation 3: Lungenbraten 6,0 Result SQL Query as is: Steak 8,4 — uebes
As to jarlh's comment on GROUP BY: You group by post, so as to get one result row per post. But a post can have many terms, how then can you select the term with the post. We'd expect that you either group by term, too, so as to get one result row per post and term, or only select aggregates from the terms, e.g. the maximum term_id and the minimum term_taxonomy_id. — Thorsten Kettner
As to sample data: Please edit your request to show it. Show an example with data per table. For your three steak variants: what is in table posts? What is in table wp_postmeta? What is in table terms... — Thorsten Kettner

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2019-12-12T16:46:15

Your query is invalid, as the select list doesn't match the GROUP BY. Even if you DBMS is flawed and doesn't raise an error, I don't recommend using such a query. It's results may be arbitrary (as is the case in MySQL for instance). Write a query that leaves no doubt as to what data you really want to select.

As you want just one value from the key/value table wp_postmeta, you don't need aggregation:

SELECT
  p.id,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  pm1.meta_value AS price
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID AND pm1.meta_key = '_price'
JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat'
                           AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
ORDER BY
  CASE WHEN p.post_parent = 0 THEN id else post_parent END,
  CASE WHEN p.post_parent = 0 THEN 1 else 2 END;

Support needed for a query

1 Answers