How to setup many-to-many relationship between resources?

Hi all,
i've spent a lot of time going through everything i could find about MigsDb and XPDO to try to figure out the best solution but i think i've hit a wall and need some wisdom.

Here's the problem. The site is a medical directory. Doctors, Specialties, Conditions, and Treatments are represented by resources and are stored in separate sub-trees under the site root. I need to establish a many-to-many relationship between these resources. Let's take Specialties for example. A Doctor has many specialties and each specialty has many sub-specialties and many doctors. The specialty - sub-specialty relationship is taken care of by the Specialty tree structure and a few symlinks.

But i can't seem to find an example of setting up many-to-many relationship between resources. In the examples that i've seen it's accomplished by a custom package with it's own schema that defines aggregate relationship between the associative and entity tables, and composite relationship the other way. The problem here is that i don't want to create my own entitiy tables. I just need to associate site-content table with my custom associative table. So that would mean that i need to change modx schema definition for modResource class and regenerate it, which is problematic for any future upgrades.

I looked into using TVs and Migs but that would solve only Doctor to Specialty half. Listing all Doctors for a specialty would require string search through all of the Doctors TV values, which sounds very slow and inefficient.

Any ideas or suggestions would be greatly appreciated.

Don't know if I fully understand the problem, but I would use tvs to handle the ui aspect of the problem and a plugin to move the data into one of the standard resource fields. This makes it much faster to filter for those attributes. Link attributes is usually a good choice.

Can you give me a rough estimate about the number of doctors and specialies you'll have to handle?

Cheers,

pepebe

Thanks Pepebe,
yes the manager UI for these relationships will be handled by MigsDb TVs on Doctor and Specialty templates. This will allow me to link the two in both directions.
The issue i was not clear about is how to extend MODx schema to support this new relationship. I found a lead on the forums (gotta love MODx community) suggesting to create a new custom schema to define the associative table and extend modResource to create Doctor and Specialty objects that have the needed relationships with the associative table. So i'm heading down that path now.
Will post my progress.

However reading a lot of discussions and critiques i was suprised to find a lack of information about setting up many-to-many relationships between modResources unless i'm looking in the wrong places. It would seem that those relationships would be required for most web applications.

All documentation and tutorials i could find related to the subject assume that both custom entity and associative tables would be created in the new schema but that would mean having to replicate a lot of MODx functionality already existing for resources for these new entities. This would be a shame. For example both a Doctor and a Specialty will need to have their own fixed url, SEO sections, security and access with ACL, image galleries, user comments with Quip, possible sub-pages with additional content, granular caching, management UI, etc. etc. I'd much rather avoid having to write all of that functionality for the new entities in a custom table and simply leverage modResource.

Of course it's entirely possible that i'm missing something important so I'm always grateful for any suggestions.

Thanks in advance.

One way to do this is to create your own schema that defines each "resource" (doctor, specialty, condition) and their relationships. Each of the main objects extends modResource and might have its own table to hold the fields that aren't in the modResource object.

The relationships are stored in intersect tables and are represented by objects like these in the schema:

modDoctorSpecialty
modConditionTreatment

The table holding the modDoctorSpecialty objects, for example, would typically have just three fields, one containing the autoincrement ID of the intersect, one for the ID of the doctor and one for the ID of the specialty.

This approach does away with TVs altogether. TVs are very convenient, but as you suspect, they suck big time when it comes to searching and sorting data for display. OTOH, doing it this way is very time-consuming to set up and requires a fair amount of custom code to use. It's probably not worth it unless you will have a significant number of related pages.

On the plus side, it would allow you do *very* fast searches like these:



This page might be helpful, although some of it will not apply since you would need multiple inter-related objects that extend modResource: http://bobsguides.com/blog.html/2014/06/02/why-extend-modresource/

Thanks BobRay,

that's exactly the approach i've settled on. Since the specialties count in the dozens, treatments in the hundreds, and doctors in the thousands and most pages would include multiple quieries i'll need them to be fast and TVs were not cutting it.
I got the schema almost setup. Will share when done.

Also wanted to thank you for the great documentation work you've been doing. I use your book and tutorials often.

Thanks for the kind words (and for buying my book).

Good luck with the project.

Quote from: outre99 at Jun 23, 2014, 09:24 AM

The site is a medical directory. Doctors, Specialties, Conditions, and Treatments are represented by resources and are stored in separate sub-trees under the site root. I need to establish a many-to-many relationship between these resources. Let's take Specialties for example. A Doctor has many specialties and each specialty has many sub-specialties and many doctors. The specialty - sub-specialty relationship is taken care of by the Specialty tree structure and a few symlinks.

I looked into using TVs and Migs but that would solve only Doctor to Specialty half. Listing all Doctors for a specialty would require string search through all of the Doctors TV values, which sounds very slow and inefficient.
.

I would like to know more about this project and how you set up the doctor db. I would like to chat with you in detail if you are available? You just made my day and you got to love this ModX community!

Skype: inmodedesign