ancestor_topics

remove_ancestor_topics.sql

delete from links_topics where (link_id, topic_id) in (
	select
		j_parent.link_id,
		j_parent.topic_id

		from (
			select
				lt.link_id,
				lt.topic_id,
				t.path
			from links_topics as lt
			inner join topics as t on lt.topic_id = t.id
		) as j_child
		inner join (
			select
				lt.link_id,
				lt.topic_id,
				t.path
			from links_topics as lt
			inner join topics as t on lt.topic_id = t.id
		) as j_parent on j_parent.link_id = j_child.link_id and j_parent.path @> subpath(j_child.path, 0, nlevel(j_child.path) - 1)
);
	

select_ancestor_topics.sql

select
	j_parent.link_id,
	j_parent.topic_id as parent_path,
	j_parent.path as parent_path,
	j_child.topic_id as child_path,
	j_child.path as child_path
	from (
		select
			lt.link_id,
			lt.topic_id,
			t.path
		from links_topics as lt
		inner join topics as t on lt.topic_id = t.id
	) as j_child
	inner join (
		select
			lt.link_id,
			lt.topic_id,
			t.path
		from links_topics as lt
		inner join topics as t on lt.topic_id = t.id
	) as j_parent on j_parent.link_id = j_child.link_id and j_parent.path @> subpath(j_child.path, 0, nlevel(j_child.path) - 1);