tripal/extensions/tripal_analysis_go/go_path_counts

SELECT  CVTP.subject_id, CVT.name, CVT2.name, CVTP.object_id, CVT3.name, CVTP.pathdistance, count(F.name)
FROM {cvtermpath} CVTP
   INNER JOIN CVTerm CVT ON CVTP.subject_id = CVT.cvterm_id
   INNER JOIN CVTerm CVT2 on CVTP.type_id = CVT2.cvterm_id
   INNER JOIN CVTerm CVT3 on CVTP.object_id = CVT3.cvterm_id
   INNER JOIN Feature_cvterm FCVT on FCVT.cvterm_id = CVT.cvterm_id
   INNER JOIN Feature F on FCVT.feature_id = F.feature_id
WHERE object_id = 7438
GROUP BY CVTP.subject_id, CVT.name, CVT2.name, CVTP.object_id, CVT3.name, CVTP.pathdistance
ORDER BY pathdistance


// THE MATERIALIZED VIEW!!!
SELECT  CVT3.name, CVTP.object_id, count(F.name)
FROM {cvtermpath} CVTP
   INNER JOIN CVTerm CVT ON CVTP.subject_id = CVT.cvterm_id
   INNER JOIN CVTerm CVT2 on CVTP.type_id = CVT2.cvterm_id
   INNER JOIN CVTerm CVT3 on CVTP.object_id = CVT3.cvterm_id
   INNER JOIN Feature_cvterm FCVT on FCVT.cvterm_id = CVT.cvterm_id
   INNER JOIN Feature F on FCVT.feature_id = F.feature_id
   INNER JOIN CV on CV.cv_id = CVT.cv_id
WHERE CV.name = 'biological_process' or CV.name = 'molecular_function' or CV.name = 'cellular_component'
GROUP BY CVT3.name, CVTP.object_id