Cross referencing documents - many to many relationships?

Let’s say I have a document container for ’Services’ and another for ’People’. I want to create relationships between one or more people and one or many services, so that on each person’s page I can view links to related services, and on the individual service page I can view links to the people assigned to that service. I know I can use @BINDINGS to output the children of Services as a multi-select TV in the template for a person, but that doesn’t create the relationship for the service. If I was doing this without MODx, I might use a xref table to map the service id to person id.

So my question is, what’s the recommended way of creating many-to-many relationships using MODx 9.6?

Quote from: therebechips at Oct 24, 2007, 05:13 AM

Let’s say I have a document container for ’Services’ and another for ’People’. I want to create relationships between one or more people and one or many services, so that on each person’s page I can view links to related services, and on the individual service page I can view links to the people assigned to that service. I know I can use @BINDINGS to output the children of Services as a multi-select TV in the template for a person, but that doesn’t create the relationship for the service. If I was doing this without MODx, I might use a xref table to map the service id to person id.

So my question is, what’s the recommended way of creating many-to-many relationships using MODx 9.6?

Why would you do it any differently in MODx? Create yourself a cross-reference table and get after it. That’s not to say there aren’t other ways to do it in MODx, using some cleverly crafted TV’s to maintain the relationships, but why? If you have the skills to create an xref table and use it in PHP for this purpose, seems to me utilizing MODx will just make it easier to accomplish.

In the past I’ve created modules to handle these kind of relationships, but I’m now coming round to the view that the document tree could be utilised to model data. It should make it easier for my clients. My problem is that I don’t fully understand how to customise the manager to get it to trigger my code.

This is my guess at the mo:
1. Add an xref table to the database with three columns, xref_id, service_id, person_id
2. Create a TV to allow multi-selection of people, pre-populate the input values with a call to a snippet containing the SQL to return all possible relationships, something like:
@EVAL $result = $modx->runSnippet("GetAllPeople"); return $result;
And populate the default values with a call to a snippet to get any existing relationships based on a query of the xref table joined to the site_content table:
@EVAL $result = $modx->runSnippet("GetExistingPeople"); return $result;
3. Create a plugin to update the xref table on document save if the above TV is present, I presume using the OnDocFormSave system event. (And I’d need to handle other doument events too I suppose, so the xref table stays in sync if a document is deleted for example). This is the bit where it gets sketchy for me...

But... is this on the right track? Has anyone gone down this road before and want to share code?

Cheers

Well, maybe something along the lines of what I’ve used for my latest multilanguage site would work. I have the main category containers correspond to the languages (English, German) then each document has a TV that cross-links a document with the corresponding document in the other language, and the value of that TV for a given document will be the corresponding document ID in the other language.

So in your case, you can categorize your documents in one group by document tree organization, then use one or more TVs to specify which other categories a given document should be in. Then you can use Ditto calls with TV filters and multiple parent parameters to organize the various categorizations.

@sottwell - thank you!
I thought I might be missing something obvious, and I think you may have it! I’m going to try out both these approaches and see what’s quicker.

Hi therebechips,

I’m thinking around the same kind of many-to-many relationship problem.
Would you mind sharing the outcome of your efforts? What option did you go with: xref table or TVs or something else?

Thanks.

Also, have you tried doing this with Revo?

Hi outre99

the above post was from a few years ago - I ended up writing a snippet called Related. There’s also another one called Quill that does the same thing, more or less, and unlike me the author has kept the snippet up to date. So you could use that for Evo.

I’ve not got into Revo properly yet (not sure I will yet), but I think it’s much easier to incorporate custom tables into the document tree so you should investigate that approach since it is far more efficient and scalable than using TVs.