This is a random collection of relationship APIs we sghhould, could, might or might not include.

• set_relation($leftid, $rightid, $relationship, $weight = 0) //make a relationship between two nodes
• get_relation($id, $relationship = “”) //get any related nodes, restricted by type of relationship; returns an array of related nodes

And from http://drupal.pastebin.com/408221 some great SQL ideas by Vlado

==========================================

nodes with the same terms, ordered by number of the related terms

many to many relations defined by terms

can be used to model different labeled sets

==========================================

SELECT tn.nid, n.title, r.teaser, count(tn.tid) AS metric FROM {node} AS n LEFT JOIN {termnode} AS tn ON tn.nid=n.nid INNER JOIN {noderevisions} AS r ON r.vid = n.vid WHERE tn.tid IN (SELECT tid FROM term_node WHERE nid=%d) AND tn.nid != %d GROUP BY tn.nid ORDER BY metric DESC LIMIT %d;

=========================================

Tree structures of a relationship domain

=========================================

All immediate children of a node - in case of tree structures

directly - one->many

with implied order, by walking the tree

SELECT n.nid FROM node AS n LEFT JOIN tree AS t ON n.nid=t.nid WHERE t.lft > $node->left AND t.rgt < $node->right AND t.lvl = $t->lvl+1 ORDER BY t.lft;

All children of a node (trees)

order by distance - first level, then left

SELECT n.nid FROM node AS n LEFT JOIN tree AS t ON n.nid=t.nid WHERE t.lft > $node->left AND t.rgt < $node->right ORDER BY t.lvl , t.lft;

The immediate parent (first node up the tree)

SELECT n.nid FROM node AS n LEFT JOIN tree AS t ON n.nid=t.nid WHERE t.lft < $node->left AND t.rgt > $node->right ORDER BY t.lft DESC LIMIT 1;

All children of a node (trees)

order by distance from the node

SELECT n.nid FROM node AS n LEFT JOIN tree AS t ON n.nid=t.nid WHERE t.lft < $node->left AND t.rgt > $node->right ORDER BY t.lft DESC;

All children of a node (trees)

order from the root

SELECT n.nid FROM node AS n LEFT JOIN tree AS t ON n.nid=t.nid WHERE t.lft < $node->left AND t.rgt > $node->right ORDER BY t.lft;

====================================================

Graph relations domainstructures

Using adjacency lists graph (nid,next_id)

to get a “deeper” or indirect relations you need to iterate through the

results either in SQL or from your program code

====================================================

SELECT n.nid FROM node AS n LEFT JOIN graph AS g ON n.nid=g.next_id WHERE g.nid = $node->nid;

ordered by weight

graph (nid,next_id,weight)

SELECT n.nid FROM node AS n LEFT JOIN graph AS g ON n.nid=g.next_id WHERE g.nid = $node->nid ORDER BY g.weight;

ordered by weight

# SELECT n.nid FROM node AS n LEFT JOIN graph AS g ON n.nid=g.next_id WHERE g.nid = $node->nid;