sql - get woocommerce product image url and price from database

admin管理员组

文章数量:1332377

Closed. This question is off-topic. It is not currently accepting answers.

---

Your question should be specific to WordPress. Generic PHP/JS/SQL/HTML/CSS questions might be better asked at Stack Overflow or another appropriate Stack Exchange network site. Third-party plugins and themes are off-topic for this site; they are better asked about at their developers' support routes.

Closed 4 years ago.

Improve this question

I want to get some product data directly from the database, currently I have this, but I'm missing the featured image URL:

 SELECT 
  p.ID,
  p.post_title,
  `post_content`,
  `post_excerpt`,
  t.name AS product_category,
  t.term_id AS product_id,
  t.slug AS product_slug,
  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,
  MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 
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' AND p.post_content <> ''
GROUP BY p.ID,p.post_title

Closed. This question is off-topic. It is not currently accepting answers.

---

Your question should be specific to WordPress. Generic PHP/JS/SQL/HTML/CSS questions might be better asked at Stack Overflow or another appropriate Stack Exchange network site. Third-party plugins and themes are off-topic for this site; they are better asked about at their developers' support routes.

Closed 4 years ago.

Improve this question

I want to get some product data directly from the database, currently I have this, but I'm missing the featured image URL:

 SELECT 
  p.ID,
  p.post_title,
  `post_content`,
  `post_excerpt`,
  t.name AS product_category,
  t.term_id AS product_id,
  t.slug AS product_slug,
  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,
  MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 
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' AND p.post_content <> ''
GROUP BY p.ID,p.post_title

• woocommerce-offtopic
• sql

Share Improve this question asked Jul 3, 2020 at 1:56 FedeFede 13777 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The only way I see is to use nested WHERE clause:

SELECT p.*, pm2.meta_value as image_file FROM (
  SELECT 
    p.ID,
    p.post_title,
    `post_content`,
    `post_excerpt`,
    t.name AS product_category,
    t.term_id AS product_id,
    t.slug AS product_slug,
    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,
    MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku,
    MAX(CASE WHEN pm1.meta_key = '_thumbnail_id' then pm1.meta_value ELSE NULL END) as thumbnail_id
  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' AND p.post_content <> ''
  GROUP BY p.ID,p.post_title
) as p
JOIN wp_postmeta pm2 ON pm2.post_id = p.thumbnail_id
WHERE pm2.meta_key = '_wp_attached_file'

本文标签： sqlget woocommerce product image url and price from database