CODEWITHSUNDEEP
mysqlwordpresswoocommerce
marcin łyczko
marcin łyczko

Reputation: 33

Query with name, price, price regular, SKU and categories - Woocommerce

I have this query to show name, price, price regular and SKU:

SELECT post.post_title, metaSku.meta_value AS sku, metaPrice.meta_value AS price, metaPriceRegular.meta_value AS priceregular
            FROM wp_posts AS post
            LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_sku' ) AS metaSku ON post.ID = metaSku.post_id
            LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_price' ) AS metaPrice ON post.ID = metaPrice.post_id
            LEFT JOIN ( SELECT * FROM wp_postmeta WHERE meta_key = '_regular_price' ) AS metaPriceRegular ON post.ID = metaPriceRegular.post_id
            WHERE post.post_type = 'product

I need to add one more thing - column categories for my products. Example: product "T-Shirt with Logo" https://www.screenpresso.com/=4kh0b (table "wp_posts") is located in categories "Tshirts", "Accessories", "Clothing" - https://www.screenpresso.com/=9Ce4b (table "wp_terms").

There is a connection: https://www.screenpresso.com/=rDHfd (table "wp_term_relationships")

Could you please help me with this one thing? The biggest obstacle for me is that product can be assigned to many categories. My queries froze the server 2 times.

Upvotes: 1

Views: 1508

Answers (1)

Booboo
Booboo

Reputation: 44013

I am thinking you could combine all the categories in one column using the MySql Group_Concat function. You can also simplify your current SQL a bit:

SELECT post.post_title, metaSku.meta_value AS sku, metaPrice.meta_value AS price, metaPriceRegular.meta_value AS priceregular, terms.categories
FROM wp_posts AS post
LEFT JOIN wp_postmeta AS metaSku ON post.ID = metaSku.post_id AND metaSku.meta_key = '_sku'
LEFT JOIN wp_postmeta AS metaPrice ON post.ID = metaPrice.post_id AND metaPrice.meta_key = '_price'
LEFT JOIN wp_postmeta AS metaPriceRegular ON post.ID = metaPriceRegular.post_id AND metaPriceRegular.meta_key = '_regular_price'
LEFT JOIN (
    SELECT wtr.object_id, GROUP_CONCAT(wp_terms.name ORDER BY wp_terms.term_order ASC SEPARATOR ', ') AS categories
    FROM wp_term_relationships wtr join wp_terms on wtr.term_taxonomy_id = wp_terms.term_id
    GROUP BY wtr.object_id
) terms on post.ID = terms.object_id
WHERE post.post_type = 'product'

Upvotes: 2

Related Questions