Does HABTM with self require 2x rows in the join table?

Question

I am trying to create a CMS with Nodes as the main model. Each Node belongsTo a NodeType , and each Node can be associated with any / any other Node .

So, I thought this caused HABTM:

 //Node model public $hasAndBelongsToMany = array( 'AssociatedNode' => array( 'className' => 'Node', 'foreignKey' => 'node_id', 'associationForeignKey' => 'associated_node_id', 'joinTable' => 'node_associations' ) ); 

The problem is that it seems that the only way to use it is that I have two lines for each association.

An example with only one association string:

Knots

• ER (id = 1)
• George Clooney (id = 2)

One row in the connection table describing the relationship between these two nodes:

• 'node_id' = 1
• 'associated_node_id' = 2

Now - if I request a TV show and save it. Actor nodes:

 $nodes = $this->Node->find('all', array( 'conditions' => array( 'Node.node_type_id' => '645' //tv shows ), 'contain' => array( 'AssociatedNode' => array( 'conditions' => array( 'AssociatedNode.node_type_id' => '239' //actors ), ) ) )); 

This works and I get ER → George Clooney.

But what if I want to pull out all the shows that George Clooney is in?

 $nodes = $this->Node->find('all', array( 'conditions' => array( 'Node.node_type_id' => '239' //actors ), 'contain' => array( 'AssociatedNode' => array( 'conditions' => array( 'AssociatedNode.node_type_id' => '645' //tv shows ), ) ) )); 

This does not work because it looks for the George Clooney identifier in the "node_id" field, and the ER identifier should be in the "associated_node_id" field - when in reality they are reversed.

The only solution I was thinking of was to keep two lines for the EVERY association. But that seems redundant. But then I have to come up with some kind of ordinary something that guarantees that every duplicate will be synchronized every time the association is saved or deleted ... etc. - and it looks like a big can of worms.

Is there something I am missing?

Answer 1

Perhaps you could do this with a custom query, but in order to support the standard Cake functions, I would think that this would be a declaration of two relations between nodes:

 public $hasAndBelongsToMany = array( 'AssociatedNode1' => array( 'className' => 'Node', 'foreignKey' => 'node_id', 'associationForeignKey' => 'associated_node_id', 'joinTable' => 'node_associations' ), 'AssociatedNode2' => array( 'className' => 'Node', 'foreignKey' => 'associated_node_id', 'associationForeignKey' => 'node_id', 'joinTable' => 'node_associations' ) ); 

and then you can combine both arrays in the afterFind callback.

 function afterFind($results) { foreach($results as &$result) { if(isset($result['AssociatedNode1']) || isset($result['AssociatedNode2'])) { $associated_nodes = array(); if(isset($result['AssociatedNode1'])) { foreach($result['AssociatedNode1'] as $associated_node) { $associated_nodes[] = $associated_node; } } if(isset($result['AssociatedNode2'])) { foreach($result['AssociatedNode2'] as $associated_node) { $associated_nodes[] = $associated_node; } } $result['AssociatedNode'] = $associated_nodes; } } unset($result); return $results; } 

But this will force you to declare both AssociatedNode1 and AssociatedNode2 in the call to contain ();

Answer 2

I'm not sure what the details of your use case are, but I have a few alternative options for you:

You can learn Tree Behavior - it is built to store things on trees, which is similar to what you are doing. I have not used it myself, so I'm not sure how applicable it is to your use.

On the other hand, if you keep the relationship in a sequential direction (i.e. always a TV show-> Actor) and know in which direction your requests are being executed (a tree search for a TV shows that the actor is searching or searching for Actors in a TV show ), you should be able to query the AssociatedNode when you go in the opposite direction, for example

 $nodes = $this->AssociatedNode->find('all', array( 'conditions' => array( 'AssociatedNode.node_type_id' => '239' //actors ), 'contain' => array( 'Node' => array( 'conditions' => array( 'Node.node_type_id' => '645' //tv shows ), ) ) )); 

In this case, for clarity, it is better to use "ChildNode" instead of "AssociatedNode".

But then again, both of these answers depend on the specifics of your use case - nIcO answer is a good general solution. This is (necessarily) uncomfortable and perhaps slower, but it distracts awkwardness perfectly.

Answer 3

One thing I have done in the past that can help is to create a model for the join table. I was able to store additional data there and do whatever I want with my requests. Then, on either side of this join model, simply define the hasMany association (perhaps it also belongs). Then you can search using the join model and write something like (from the controller):

 $this->Node->NodesNode->find('all', array('conditions'=>array("or"=>array('node_id'=>$id,'sub_node_id'=>$id)))); 

IMHO: there is nothing that would force you to use cake conventions. I love cake, but sometimes it and ORM complicate very easy things. You can simply write your own request and analyze the results yourself. This will probably be faster than dealing with the overhead of a different behavior or model, and you could probably write a better query method than the default ones.

Oh, and finally, I will look when you use 1 model for several purposes. Actually consider whether this model should really support everything. I found that whenever I did this, I just rewrote the whole thing for a year or two. You will quickly fall into the throat of a bottle, where some nodes need extra behavior in this way, others need something else, and you have statements (or maybe smarter ones) scattered everywhere. Plus it really slows down to making crazy tree-based queries in db.