- 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