- 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