Mysql query - Showing a second taxonomy for a product stopped me

Question

I am in the situation where I need of the following information:

1. ID (posts.ID)
2. TITLE (posts.title)
3. SKU (postmeta.metakey = ‘_sku’) --> value stored in (postmeta.metavalue)
4. PRICE (postmeta.metakey= ‘regular_price’) -> value stored in (postmeta.metavalue)
5. BRAND (terms.name WHERE term_taxonomy_term.taxonomy=‘product_cat’)
6. CATEGORY (terms.name WHERE term_taxonomy_term.taxonomy=‘brand’)

SKU and PRICE belongs to the same column "postemeta.metakey" which related values are in the "postemeta.metavalue".

BRAND and CATEGORY belongs to the same column "terms.name" but they have different taxonomies defined in "term_taxonomy.taxonomy" and are ('product_brand' and 'product_cat').

Developing the query below I was able to collect all the information except for one that is the BRAND (terms.name).

   SELECT p.ID
          , MAX(CASE WHEN pm1.meta_key = '_sku' 
                     THEN pm1.meta_value 
                     ELSE NULL END) AS sku
          , p.post_title
          , t.name AS product_category
          , MAX(CASE WHEN pm1.meta_key = '_price' 
                     THEN pm1.meta_value 
                     ELSE NULL END) AS price
     FROM mg_posts p 
LEFT JOIN mg_postmeta AS pm1 
       ON pm1.post_id = p.ID
LEFT JOIN mg_term_relationships AS tr 
       ON tr.object_id = p.ID
     JOIN mg_term_taxonomy AS tt 
       ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
     JOIN mg_terms AS t 
       ON t.term_id = tt.term_id
    WHERE p.post_type IN ('product', 'product_variation') 
    GROUP BY p.ID, p.post_title

Any help to fix this issue??

Answer 1

Philipxy, I think it's better to proceed step by step, being focused on the issue.

Category and Brand are stored both in the same way:

TERMS            TERM_TAXONOMY          TERM_RELATIONSHIP          PRODUCT
---------------------------------------------------------------------------
term_id  ---->   term_id                product_id      --->   product_id
name             †erm_taxonomy_id   --> term_taxonomy_id
                 taxonomy           

Brand and Category are identified in term.taxonomy.taxonomy as 'product_brand' and 'product_cat' and their name is stored in terms.term_id.

Now the task is to get result in separated column

|ProductID |   Brand    |     Category      |
---------------------------------------------
| 001      |    Levis   |     Men Jeans     |

After solving this I keep to develop the matter.

Answer 2

To whom can be interested I found the final solution to get the information I needed.

SELECT 
     p.id, 
     p.post_title, 
     p.post_status,
     MAX(CASE WHEN t.taxonomy = 'product_cat' then tr.name ELSE NULL END) as category,
     MAX(CASE WHEN t.taxonomy = 'product_brand' then tr.name ELSE NULL END) as brand,
     MAX(CASE WHEN pm.meta_key = '_sku' then pm.meta_value ELSE NULL END) as sku,
     MAX(CASE WHEN pm.meta_key = '_price' then pm.meta_value ELSE NULL END) as price
     FROM mg_posts as p
     LEFT JOIN mg_term_relationships as r ON r.object_id=p.id
     JOIN mg_term_taxonomy as t ON t.term_taxonomy_id=r.term_taxonomy_id
     JOIN mg_terms as tr ON tr.term_id=t.term_id
     JOIN mg_postmeta pm ON (pm.post_id = p.ID)
     WHERE P.post_type='product' 
     GROUP BY P.ID

Hope it can helps!