Woocommerce -> MySql -> vTaxonomyChained vTaxonomyPaths vProductCatsSummary

  • Product categories have parents…
  • Products can be in more than 1 category

xx

 create view vProductCatsSummary
 as
 select ID
	,count(*) as cat_cnt
    ,min(tt.term_taxonomy_id) as cat_min
    ,max(tt.term_taxonomy_id) as cat_max
    ,(	select group_concat( tex.name separator ' | ' )
		from wp1_term_relationships trx
		JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id
		JOIN wp1_terms tex ON ttx.term_id = tex.term_id
        where trx.object_id = ID
        and ttx.taxonomy = 'product_cat') as cats
from wp1_posts po
JOIN wp1_term_relationships tr ON po.ID = tr.object_id
join wp1_term_taxonomy tt on tr.term_taxonomy_id = tt.term_taxonomy_id
where  po.post_type = 'product'
	aND tt.taxonomy = 'product_cat'
group by ID;

xx

alter view vTaxonomyPaths
 as
 select tt_id
	,concat(
		case when tt3_id is not null then concat(te3_name, ' -> ') else '' end
        ,case when tt2_id is not null then concat(te2_name, ' -> ') else '' end
        ,case when tt1_id is not null then concat(te1_name, ' -> ') else '' end
        ,te_name) as cats
	,concat(
		case when tt3_id is not null then concat(te3_slug, '/') else '' end
        ,case when tt2_id is not null then concat(te2_slug, '/') else '' end
        ,case when tt1_id is not null then concat(te1_slug, '/') else '' end
        ,te_slug) as path
 from vTaxonomyChainedUp
where tt_taxonomy = 'product_cat';

xx

 create view vTaxonomyChainedUp
 as
 -- 20180808 LSR
 -- product categories
 -- daisychained up +3 levels via parents
 select tt0.term_taxonomy_id as tt_id
	,tt0.term_id as te_id
    ,tt0.taxonomy as tt_taxonomy
    ,tt0.description as tt_desc
    ,te0.name as te_name
    ,te0.slug as te_slug
--
	,tt1.term_taxonomy_id as tt1_id
	,tt1.term_id as te1_id
    ,tt1.taxonomy as tt1_taxonomy
    ,tt1.description as tt1_desc
    ,te1.name as te1_name
    ,te1.slug as te1_slug
--
	,tt2.term_taxonomy_id as tt2_id
	,tt2.term_id as te2_id
    ,tt2.taxonomy as tt2_taxonomy
    ,tt2.description as tt2_desc
    ,te2.name as te2_name
    ,te2.slug as te2_slug
--
	,tt3.term_taxonomy_id as tt3_id
	,tt3.term_id as te3_id
    ,tt3.taxonomy as tt3_taxonomy
    ,tt3.description as tt3_desc
    ,te3.name as te3_name
    ,te3.slug as te3_slug
--
 from  wp1_term_taxonomy tt0
 JOIN wp1_terms te0 ON tt0.term_id = te0.term_id
--
 left join wp1_term_taxonomy tt1 on tt1.term_taxonomy_id = tt0.parent
 left JOIN wp1_terms te1 ON tt1.term_id = te1.term_id
--
 left join wp1_term_taxonomy tt2 on tt2.term_taxonomy_id = tt1.parent
 left JOIN wp1_terms te2 ON tt2.term_id = te2.term_id
--
 left join wp1_term_taxonomy tt3 on tt3.term_taxonomy_id = tt2.parent
 left JOIN wp1_terms te3 ON tt3.term_id = te3.term_id;

xx