HABTM with self requires 2x the rows in join table?
I'm trying to build a CMS with Nodes as the main model. Each Node belongsTo a NodeType , and every Node can be related to any/every other Node .
So - thought this called for HABTM:
//Node model
public $hasAndBelongsToMany = array(
'AssociatedNode' => array(
'className' => 'Node',
'foreignKey' => 'node_id',
'associationForeignKey' => 'associated_node_id',
'joinTable' => 'node_associations'
)
);
The problem is, it seems like the only way it works is if I have TWO rows for each association.
Example with just one association row:
Nodes
A single row in the join-table describing the relationship between those two nodes:
Now - If I query for TV Shows and Contain it's 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 all 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 doesn't work because it's looking for George Clooney's ID to be in the 'node_id' field, and ER's ID to be in the 'associated_node_id' field - when in reality they're reversed.
The only solution I've thought of is to keep two rows for EVERY association. But this seems overkill. But then I have to come up with some kind of custom something that makes sure to keep each duplicate in sync w/ the other every time an association is saved or deleted...etc - and this seems like a large can of worms.
Is there something I'm missing?
You could probably do it with a custom query, but to keep with standard Cake functions, one thing I can think of would be to declare two relationships between the 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 could merge both arrays in an 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 would force you to declare both AssociatedNode1 and AssociatedNode2 in the call to contain();
I'm not sure what the details of your use case are, but I've got a couple of alternate options for you:
You might look into using the Tree Behavior - this is built for storing things in trees, which it sounds like might be what you're doing. I haven't used it myself, so I'm not sure how applicable it is to your use.
On the other hand, if you store the relationships in a consistent direction (ie always TV Show->Actor) and know which direction your queries run (looking up the tree for TV Shows an Actor is in vs looking down for finding Actors in a TV Show), you should be able query AssociatedNode when you're going the reverse direction, eg
$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, it might be better to use "ChildNode" instead of "AssociatedNode" for clarity.
But again, both of these answers depend on the particulars of your use case - nIcO's answer is a good general solution. It is (necessarily) awkward and possibly slower, but it abstracts away the awkwardness nicely.
One thing I've done in the past that might help is to make a model for the join table. I was able to store extra data in there and do whatever I wanted with my queries. Then on both sides of that join model just define a hasMany association (maybe a belongsTo as well). Then you can do a find using the join model and write something like (from a controller):
$this->Node->NodesNode->find('all', array('conditions'=>array("or"=>array('node_id'=>$id,'sub_node_id'=>$id))));
IMHO: there is nothing really forcing you to use cake conventions. I love cake, but sometimes both it and the ORMs complicate really easy things. You may just want to write your own query and parse the results yourself. It'd probably be faster than dealing with the overhead of another behavior or model plus you could probably write a way better query than the defaults given.
Oh and lastly, I'd watch out when you are using 1 model for multiple purposes. Really think if that one model should really be supporting everything. I've found that whenever I've done that I've just rewritten the entire thing within a year or two. You will quickly hit a bottle neck where some nodes need extra behavior in this way, others need something else and you have if statements (or maybe something more clever) scattered everywhere. Plus it really slows thing down to do crazy tree based queries in a db.
链接地址: http://www.djcxy.com/p/64884.html