Self-Referential ManyToMany Convention in CakePHP

Question

I have an existing data model where I can rename things freely to match CakePHP's conventions. I have a type of graph node, where a node can have an arbitrary number of child nodes and an arbitrary number of parent nodes (uni-directional relationships).

Here's the table of nodes, following CakePHP's conventions:

Table: nodes
Column: node_id (INT)
Column: description (TEXT)

My question is what the join table should look like? Here is what it looks like now:

Table: nodes_nodes
Column: parent_node_id (INT)
Column: child_node_id (INT)

And what the documentation implies it should be:

Table: nodes_nodes
Column: node_id (INT)
Column: node_id (INT)

Notice that two column names are the same, which obviously won't work. What should these two columns be called? Or can CakePHP's conventions not handle this situation without configuration?

Answer 1

If a node has child nodes, do those child nodes automatically have the first node as a parent?

This relationship may be similar to a users to users relationship where the relationship symbolises the common 'friend' notion in social networks. Suggest you have a google around for user/friend data models to see if that helps.

Answer 2

As neilcrookes noted, there are some articles on this on the web about doing this in CakePHP. Here is one of them, using User HABTM User (friends) as an example.

In that linked article, you can ignore everything after the User class definition if you aren't going to be paginating on the model.