Many to Many where one table can be any table?

[calzone]

I have a tags table: id, name
And I have an entity_tags table: id, tag_id, tab_name, f_key

The idea was that I could create a tag in the tags table and then assign it to any record in more than a single table in my database. So entity_tags would have the id of the tag, as well as the name of the table and id of the record that the tag was attached to.

Then I started putting together my cake model and realized I’m not sure this is actually remotely supported.

If anyone has done this before or know, please chime in!

[calzone]

Well… after many iterations of DDG → Google searches… lo and behold, I came across this:

Multiple belongsToMany

Is there a (simple) way to have multiple belongsToMany associations ? The goal is to tag everything. So, I have a Tags table and would like it to be associated with Articles, Categories, Images etc. There used to be a tagEverything (I don’t remember the name exactly) plugin but it was cake 2 only.

I guess my search on here was insufficient.

I tried seeing what it might take to do this as a behavior, but that didn’t seem any clearer. Then, the suggestion to denormalize my tags into a single column in every table was a total non-starter.

So I took @Zuluru’s suggestion in that thread … closed my eyes and just tried and hoped.

Instead of setting it up as a belongsToMany(), I created a belongsTo() for each each table using the entity_tags table in the EntityTagsTable.php model… I set up corresponding hasMany() relationships in my other tables back to EntityTags.

In each belongsTo(), I added 'setFinder' => ['tabl_name' => 'xxxxxxxxx'] (where xxxxxx is just whichever one of several tables I’m associating to EntityTags. (Because tabl_name is the name of the column in my bridge table that tells me which entity the f_key field is for).

Fingers crossed, I tweaked the view controller for one of those tables and threw in

'contain' => ['EntityTags','EntityTags.Tags'],

to my ->get()

And… I’m encouraged to report that that much of this whole adventure is indeed working!

Now I need to see how it works with actual tag assignment and saving (need to test the CUD in CRUD, although tags aren’t about U really… they’re C and D as far as write ops)

As well, I’m going to see if I can go full monty and use belongsToMany() in Tags instead of belongsTo() in EntityTags… if for no other reason than it will save me a level in the returned query results (i.e., “bypassing” EntityTags and just giving me actual tags per each record… as it is now, I have $xxxxxxx->entity_tags[n]->tags[y] )

[calzone]

I spoke too soon it seems. It was just a coincidence on the entity I chose to test.

It turns out the join is being made without regard for the “tabl_name” condition.

I’m not sure how setFinder is supposed to work here, then. Because as I have it, it’s not doing anything at all.

[Zuluru]

Here’s a stripped-down current example from my code. foreign_model and foreign_id are the fields in the common table that indicate what table and record it’s for.

$this->hasMany('Documents', [
    'foreignKey' => 'foreign_id',
    'conditions' => ['Documents.foreign_model' => 'ThisTableName'],
]);

[calzone]

There is no bridge table?

[Zuluru]

This example is a many-to-one, not many-to-many.

[calzone]

I see. What’s happening in my case is that the join is working and data appears to be correct, but that is by sheer coincidence of ids, not because it is matching on tabl_name. I looked at the generated query to discover it’s basically ignoring my finder

i.e., I can delete my finder or keep it and it makes no difference to the generated query.
.
.

---

UPDATE:
The reason my finder was being ignored is that I was using 'setFinder' => […conditions…] within my ->belongsToMany() setup. I changed it to 'finder' => […conditions…] and that gave me the error:

non-numeric value encountered [ CORE/src/ORM/Association/BelongsToMany.php , line 1120

Just to try to humor it, I changed my finder conditions to 'EntityTags.id' => 12 — internal server error

[Zuluru]

Did you try just using a conditions array directly in the association declaration?

[calzone]

	$this->belongsToMany('Tags', [
		'foreignKey' => 'f_key',
        'targetForeignKey' => 'tag_id',
        'through' => 'entity_tags',
	])
	->setFinder(['EntityTags.tabl_name'=>'recipes']);

That’s what I was trying. And in TagsTable:

    $this->belongsToMany('Recipes', [
        'foreignKey' => 'tag_id',
        'targetForeignKey' => 'f_key',
        'finder' => ['conditions'=>['tabl_name' => 'recipes']],
        'joinTable' => 'entity_tags',
    ]
    );

I also tried EntityTags.tabl_name and Tags.EntityTags.tabl_name

Or did you mean something else?

[Zuluru]

setFinder specifies a finder method to use. You aren’t giving it a method. You’re giving it a set of conditions. So try setConditions instead?

[calzone]

THANK YOU!!!

I totally missed that option in the docs and thought setFinder was the way. And now it’s making the correct query!

One interesting observation: I can use either:
'through' => 'EntityTags' or ' joinTable' => 'entity_tags' and achieve the query I need.

However, 'through' => 'entity_tags' fails silently (presumably because 'through' => expects a model name and not a table name.

[calzone]

Interesting side effect of the customized conditions clause is I can’t do tag assignment because it only returns those tags that are already assigned.

I think the solution is to create yet another association back to Tags that doesn’t use the conditions and only use this association for writing of records… while the first association will be used for reading the records.

[calzone]

I ended up doing this:

	$this->belongsToMany('Tags', [
		'foreignKey' => 'f_key',
        'targetForeignKey' => 'tag_id',
        'through' => 'EntityTags',
        'conditions' => ['EntityTags.tabl_name' => 'recipes']
	]);

	$this->belongsToMany('TagsLookup', [
		'className' => 'Tags',
		'foreignKey' => 'f_key',
        'targetForeignKey' => 'tag_id',
        'joinTable' => 'entity_tags',
	]);

That enables me to read only the tags that are assigned to that model, while assigning any and all tags available to that same model.

I suppose I could have also just done this in the controller when I want to render all the tags so that are available for assignment:

$tags = TableRegistry::getTableLocator()->get('Tags');

[calzone]

The very last issue I had to overcome is, when saving the data, I needed to be able to write the table name that is owning the tag into tabl_name field in the EntityTags bridge table.

No matter how hard I tried, I couldn’t create form fields with the info, rewrite the data in the controller, nor beforeMarshall the data. Nothing worked. And there’s that whole really kill-joy but totally offhand comment in the book:

// Multiple select element for belongsToMany
// Does not support _joinData

Finally, while searching for some way to just pass a hint from the controller to the model, I discovered you can pass an array of options to the save method and then access that in beforeSave in any of the models.

So:
->save($recipes, ['tabl_name'=>'recipes'])

And in EntityTags’ beforeSave($event, $EntityTags, $options) I simply assign $EntityTags->tabl_name = $options['tabl_name'] and it’s all working now.

For anyone that might want to achieve the same kind of thing, I think most of everything they need to know is now shared in this thread.