Nested set with full ancestors path for pandas data wragling

taxon-nested-set-ancesstor.sql

WITH

ancesstor_taxons AS (
	SELECT 
		taxon.id as taxon_id,
		taxon.taxon_type as taxon_type,
		taxon.parent_id, 
		ancesstor.id AS ancesstor_id,
		ancesstor.parent_id AS ancesstor_parent_id,
		trans.name AS ancesstor_name,
		taxon.lft, 
		taxon.rgt, 
		taxon.depth
	FROM 
		spree_taxons AS taxon,
		spree_taxons AS ancesstor
	
	INNER JOIN spree_taxon_translations AS trans ON trans.spree_taxon_id = ancesstor.id
	
	WHERE 
		trans.locale = 'en'
		AND ancesstor.lft <= taxon.lft 
		AND ancesstor.rgt >= taxon.rgt
		AND ancesstor.parent_id IS NOT NULL
	
	ORDER BY ancesstor.lft
),

taxon_with_list_ancesstor AS (
	SElECT 
		taxon_id,
		taxon_type,
		STRING_AGG(ancesstor_id::varchar, ' -> '  ORDER BY ancesstor_id ASC ) AS ancesstor_ids,
		STRING_AGG(ancesstor_name::varchar, ' -> '  ORDER BY ancesstor_id ASC ) AS ancesstor_names
		
	FROM ancesstor_taxons
	GROUP BY taxon_id, taxon_type
)


-- SElECT * FROM ancesstor_taxons;
SELECT * FROM taxon_with_list_ancesstor;