Question 242569 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

SQL query for woocommerce

I built a woocommerce sql query to list all products with their important values.

Here my query:

SELECT 
p.ID,
p.post_type as Art,
p.post_title AS Produkt,
p.post_excerpt AS Beschreibung,
t.name AS Produzent,

MAX(CASE WHEN pm1.meta_key = 'faktor' then pm1.meta_value ELSE NULL END) as Faktor,
MAX(CASE WHEN pm1.meta_key = 'produzentenvergleich' then pm1.meta_value ELSE NULL END) as PV,
MAX(CASE WHEN pm1.meta_key = 'einkaufspreis' then pm1.meta_value ELSE NULL END) as EP,
MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as RegPreis,
MAX(CASE WHEN pm1.meta_key = 'variationspreis' then pm1.meta_value ELSE NULL END) as VarPreis,
MAX(CASE WHEN pm1.meta_key = '_purchase_note' then pm1.meta_value ELSE NULL END) as Liefertermin,
MAX(CASE WHEN pm1.meta_key = '_stock_status' AND pm1.meta_value = 'instock' then 'Ja' END) as lieferbar,
MAX(CASE WHEN pm1.meta_key = 'produzenten_bestellvorgabe' then pm1.meta_value ELSE NULL END) as Vorgabe,
MAX(CASE WHEN pm1.meta_key = 'durchschnittsgewicht' then pm1.meta_value ELSE NULL END) as ø Gewicht, 
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_min' then pm1.meta_value ELSE NULL END) as Min_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_max' then pm1.meta_value ELSE NULL END) as Max_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_default' then pm1.meta_value ELSE NULL END) as Def_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_step' then pm1.meta_value ELSE NULL END) as Step_Menge,
MAX(CASE WHEN pm1.meta_key = '_unit' then pm1.meta_value ELSE NULL END) as Unit,
MAX(CASE WHEN pm1.meta_key = '_sale_price_label' then pm1.meta_value ELSE NULL END) as Preis_Label


FROM KtS4szE8u_posts p 
LEFT JOIN KtS4szE8u_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN KtS4szE8u_term_relationships AS tr ON tr.object_id = p.ID
JOIN KtS4szE8u_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN KtS4szE8u_terms AS t ON t.term_id = tt.term_id

WHERE p.post_type = 'product' AND p.post_status = 'publish' AND substring(p.post_title,1,12) <> 'Mindermengen' and substring(p.post_title,1,9) <> 'Glaspfand' and substring(p.post_title,1,16) <> 'Mitgliedsbeitrag' and substring(p.post_title,1,10) <> 'Barzahlung' and substring(p.post_title,1,1) <> '€' and substring(p.post_title,1,9) <> 'Umbuchung' and  substring(p.post_title,1,7) <> 'Gutschr'
GROUP BY p.ID,p.post_title

This query works fine but I need also a column with the product tag.
How can I add this to my query?

Thank you
Siegfried

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for your help. I am not so firm with SQL. I changed the following lines to my code but I get no result.

     SELECT 
  p.ID,
  p.post_type as Art,
  p.post_title AS Produkt,
  p.post_excerpt AS Beschreibung,
  t.name AS Produzent,
  t2.name AS Gruppe,

  MAX(CASE WHEN pm1.meta_key = 'faktor' then pm1.meta_value ELSE NULL END) as Faktor,
  MAX(CASE WHEN pm1.meta_key = 'einkaufspreis' then pm1.meta_value ELSE NULL END) as EP,
  MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as RegPreis,
  MAX(CASE WHEN pm1.meta_key = '_purchase_note' then pm1.meta_value ELSE NULL END) as Liefertermin,
  MAX(CASE WHEN pm1.meta_key = '_stock_status' AND pm1.meta_value = 'instock' then 'Ja' END) as lieferbar,
  MAX(CASE WHEN pm1.meta_key = 'durchschnittsgewicht' then pm1.meta_value ELSE NULL END) as ø Gewicht, 
  MAX(CASE WHEN pm1.meta_key = 'woodecimalproduct_min_qnt' then pm1.meta_value ELSE NULL END) as Min_Menge,
  MAX(CASE WHEN pm1.meta_key = 'woodecimalproduct_max_qnt' then pm1.meta_value ELSE NULL END) as Max_Menge,
  MAX(CASE WHEN pm1.meta_key = 'woodecimalproduct_item_qnt' then pm1.meta_value ELSE NULL END) as Def_Menge,
  MAX(CASE WHEN pm1.meta_key = 'woodecimalproduct_step_qnt' then pm1.meta_value ELSE NULL END) as Step_Menge,
  MAX(CASE WHEN pm1.meta_key = '_unit' then pm1.meta_value ELSE NULL END) as Einheit,
  MAX(CASE WHEN pm1.meta_key = '_sale_price_label' then pm1.meta_value ELSE NULL END) as Preis_Label



FROM KtS4szE8u_posts p 

LEFT JOIN KtS4szE8u_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN KtS4szE8u_term_relationships AS tr ON tr.object_id = p.ID
LEFT JOIN KtS4szE8u_term_relationships AS tr2 ON tr2.object_id = p.ID
JOIN KtS4szE8u_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN KtS4szE8u_terms AS t ON t.term_id = tt.term_id
JOIN KtS4szE8u_term_taxonomy AS tt2 ON tt.taxonomy = 'product_tag' AND tt2.term_taxonomy_id = tr2.term_taxonomy_id 
JOIN KtS4szE8u_terms AS t2 ON t2.term_id = tt2.term_id

WHERE p.post_type = 'product' AND p.post_status = 'publish' AND substring(p.post_title,1,12) <> 'Mindermengen' and substring(p.post_title,1,9) <> 'Glaspfand' and substring(p.post_title,1,16) <> 'Mitgliedsbeitrag' and substring(p.post_title,1,10) <> 'Barzahlung' and substring(p.post_title,1,1) <> '€' and substring(p.post_title,1,9) <> 'Umbuchung' and  substring(p.post_title,1,7) <> 'Gutschr'


   GROUP BY p.ID,p.post_title

    Best regards Siegfried


  2. 0

    Sorry
    I found my mistake.

    JOIN KtS4szE8u_term_taxonomy AS tt2 ON tt2.taxonomy = 'product_tag' AND tt2.term_taxonomy_id = tr2.term_taxonomy_id 
JOIN KtS4szE8u_terms AS t2 ON t2.term_id = tt2.term_id

    Now it works fine.
    Thank you once more.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search