Skip to content

Instantly share code, notes, and snippets.

@hartzell
Last active August 29, 2015 14:05
Show Gist options
  • Save hartzell/d2d55d0ec9988b2cbc5c to your computer and use it in GitHub Desktop.
Save hartzell/d2d55d0ec9988b2cbc5c to your computer and use it in GitHub Desktop.
Dump the features related to a hard-coded starting feature, using two common table expressions (one recursive to build the set, one not recursive to expand set of columns)
-- Here's an example that takes the output of the recursive CTE
-- and then decorates it with details about the features and relationships.
with recursive pair(initial_feature,
subj_feature_id, relationship_id, obj_feature_id,
feat_id_path,
cycle
) as
(
select subj.feature_id,
subj.feature_id, fr.feature_relationship_id, obj.feature_id,
array[subj.feature_id, obj.feature_id],
false
from feature subj
inner join feature_relationship fr on
subj.feature_id = fr.subject_id
inner join feature obj on
obj.feature_id = fr.object_id
where
subj.feature_id = 54794423
union
select null,
p.obj_feature_id, fr.feature_relationship_id, obj.feature_id,
p.feat_id_path || obj.feature_id,
obj.feature_id = any(p.feat_id_path)
from pair p
inner join feature_relationship fr on
p.obj_feature_id = fr.subject_id
inner join feature obj on
obj.feature_id = fr.object_id
where not cycle
),
decorated_pair(initial_feature,
subj_feature_id, subject_name, subject_uniquename, subject_type,
relationship_name,
obj_feature_id, object_name, object_uniquename, object_type,
feat_id_path, cycle
) as
(
select initial_feature,
s.feature_id, s.name, s.uniquename, s_cvterm.name as sub_type,
rel_cvterm.name as relationship_name,
o.feature_id, o.name, o.uniquename, o_cvterm.name as obj_type,
feat_id_path,
cycle
from pair
inner join feature s on
pair.subj_feature_id = s.feature_id
inner join feature_relationship fr on
pair.relationship_id = fr.feature_relationship_id
inner join feature o on
pair.obj_feature_id = o.feature_id
inner join cvterm s_cvterm on
s.type_id = s_cvterm.cvterm_id
inner join cvterm rel_cvterm on
fr.type_id = rel_cvterm.cvterm_id
inner join cvterm o_cvterm on
o.type_id = o_cvterm.cvterm_id
)
select initial_feature,
subj_feature_id, subject_name, subject_uniquename, subject_type,
relationship_name,
obj_feature_id, object_name, object_uniquename, object_type,
feat_id_path,
cycle
from decorated_pair;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment