I’m looking for a way to establish many-to-many relationships between resources. For example, a college site, where there may be a number if disciplines (with a page for each one) and number of professors (with a page for each one). Each professor may have multiple disciplines to teach, and each discipline may have multiple professors associated with it.
When user visits a professor’s profile, there should be a list of associated disciplines there. And on the discipline’s page there should be a list of professors.
When a new teacher is added in manager (by adding a new resource i suppose), there should be a way to choose from list of disciplines associated with him (one or many). When a new discipline is added, there should be a way to choose from teachers.
Is there a way to achieve this with TV’s? Some combination of Resource list and Multi-select listbox comes to mind, but both of them are somewhat limited for this purpose. Of course, I could create, say, 10 Resource list TVs, hoping this would be enough for most cases, but may be there is more elegant way to achieve this?
Please trust me. You don’t want to do this with TVs. They are meant to hold information about the resource they’re attached to, not serve as repositories for many-to-many relationships.
There are also cumbersome and slow for searching and sorting.
What you want are custom “intersect” tables that hold the relationship data.
What Bob Ray suggested is obviously the best, but if you’re not that much of a programmer, or can’t or don’t want to set up the custom tables needed for that approach (and then you’d have to find a way to insert the resource IDs into these tables), then you could use TVs. But you really do want to use just one TV to create the links.
I created a site for health practitioners (https://holistichealthcommunity.org — see the Volunteers and Modalities pages) using just one TV to link practitioners and therapies together. The TV is multi-select, with the values generated by a script that loads all the therapies and associated ids (as values) for the selection list. So, for your case, you would just need one TV to use for the professors to assign which disciplines they teach. Then, on the bio page for an individual professor, you just explode the TV value, which may contain multiple IDs, into individual IDs and run a loop to load each discipline for that professor. For the individual discipline pages, you would query (using PDOtools or whatever) the list of professors and search for associated TVs that contain the ID of the discipline. That’s not as fast as using intersect tables, but unless you have thousands of professors, you probably won’t notice that much of a difference. Plus, once the contents of each professor page or discipline page has been created, it will be cached, so no lookup will be needed after the first view of that page by anyone on the Internet.
This approach does mean that after you add a new discipline, you would have to go into each individual professor and assign the new discipline. But you really do need to use just one TV to link the two together. On another site I programmed (a conference site linking speakers and conferences together), the conferences are linked to individual speakers. It became a pain to add new speakers, because after adding a speaker, the editors had to then go back to the conference resources and link the speakers there. So I created a multiselect TV assigned to speaker resources that lists all the conferences, but it doesn’t get stored. Instead, it dynamically loads all the current conferences and shows which conferences the speaker is assigned to. When a new speaker is entered, you can pick from the list of conferences to assign that speaker to the conferences. A custom plugin is used so that, on saving the speaker info, the value of that submitted TV is taken and the data is transferred to the TV in the individual conferences, retaining the TVs in the conference resources as the source for linking speakers and conferences together. This approach works really well to provide two-way linking (which is really just one-way linking, using the plugin to convert the apparent reverse link in the speaker info to one-way linking in each conference upon saving). I hope how this works is making sense to you!
How many professors and disciplines will you have?
Can you share some code?
I once tried to do something similar using the events OnResourceTVFormRender (to set the TV value) and OnDocFormSave (to save the value somewhere else), but OnResourceTVFormRender is a pain to use and OnDocFormSave fires too late (when the data is already saved to the database table for TVs). So I was wondering, if you have a cleaner and more elegant solution.
// plugin to get speaker links to conferences
// run onDocFormSave
if(!empty($resource->uri)) {
$thisres = $modx->getObject('modResource',array('uri'=>$resource->uri));
$thisconfs = explode('||',$thisres->getTVValue('spkr_conferences'));
foreach ($thisconfs as $thisconf) {
$c = $modx->newQuery('CollectionSelection');
$c->select(array('collection','resource'));
$c->where(array('collection'=>$thisconf,'resource'=>$thisres->id));
$conflink = $modx->getCollection('CollectionSelection',$c);
if(empty($conflink)) {
// get highest current menuindex
$prefix = $modx->getOption('table_prefix');
$q = "SELECT menuindex FROM ".$prefix."collection_selections
WHERE collection=$thisconf ORDER BY menuindex DESC LIMIT 1";
$r = $modx->query($q);
$thismenuindex = 0;
if(!empty($r) && $r->rowcount()>0) {
while ($row = $r->fetch(PDO::FETCH_ASSOC)) {
$thismenuindex = $row['menuindex'] + 1;
}
$obj = $modx->newObject('CollectionSelection');
$obj->set('collection',$thisconf);
$obj->set('resource',$thisres->id);
$obj->set('menuindex',$thismenuindex);
$obj->save();
}
}
}
}
The list of speakers is in a Selection, as you can see, but you could use the “foreach ($thisconfs as $thisconf)” loop to put together a value for a TV just as well. So if it were a TV, it would be more like this:
if(!empty($resource->uri)) {
$thisres = $modx->getObject('modResource',array('uri'=>$resource->uri));
$thisconfs = explode('||',$thisres->getTVValue('spkr_conferences'));
foreach ($thisconfs as $thisconf) {
$c = $modx->newQuery('modDocument');
$c->select(array('id'));
$c->where(array('id'=>$thisconf));
$conflink = $modx->getCollection('modDocument',$c);
if(empty($conflink)) {
// get the current TV value
$tv = $modx->getObject('modTemplateVar',array('name'=>'NAME_OF_TV'));
$currentvalue = $tv->value;
$tvvals = explode('||',$currentvalue);
// add the new ID to the value if it's not there already
if(!in_array($thisconf, $tvvals) {
$newvalue = $currentvalue . '||' . $thisconf;
$tv->setValue($conflink->id,$newvalue);
$tv->save();
}
}
}
}
I haven’t totally vetted that code, but that’s the idea. Ideally, you could also add some code that would check the current TV values for ALL the conferences and REMOVE the speaker if the particular conference’s ID isn’t in the submitted list of conference IDs for the speaker.
And here’s the system to create the popup list of conferences to link to.
A multi-select listbox TV is assigned to speakers, and is only used to temporarily store date. The options for the popup value in the TV are created just using a standard query to output the list of conferences as pickable values.
This plugin sets the value of that temporary TV, i.e. the conferences this speakers is currently linked to, each time the individual speaker resource is loaded (the value of the temporary TV gets updated just before the manager page loads):
// plugin to get speaker links to conferences
// run onDocFormRender
$speakertemplateid = 129; // enter the ID of the template assigned to individual speakers
if(!empty($_REQUEST['id'])) {
$thisres = $modx->getObject('modResource',$_REQUEST['id']);
if($thisres->template == $speakertemplateid) { // check for Individual Speaker template
$c = $modx->newQuery('CollectionSelection');
$c->select(array('collection','resource'));
$c->where(array('resource'=>$_REQUEST['id']));
$c->sortby('collection','DESC');
$conflinks = $modx->getCollection('CollectionSelection',$c);
$selections = array();
if(!empty($conflinks)) {
foreach ($conflinks as $conflink) {
$selections[] = $conflink->collection;
}
$string = implode('||', $selections);
$tv = $modx->getObject('modTemplateVar',array('name'=>'spkr_conferences'));
$tv->setValue($_REQUEST['id'],$string);
$tv->save();
}
}
}
Once again, this is using Collections. If the conferences used TVs instead, then all you would do is grab the value of the TV instead of the list of Selections.
Thanks for the code. There are some interesting ideas here.
Unfortunately it’s not exactly what I was hoping for. What I’m trying to do, is to avoid, that the data is saved in the table modx_site_tmplvar_contentvalues at all. With your solution the data is stored twice in the database and I’d like to avoid that.
What I don’t understand in your code is why you load the resource from the database (and moreover by the value of the uri).
Data is saved in the TV assigned to speakers, but only just before displaying that TV on the speaker resource, so that conferences can be assigned there. This TV isn’t used for any other purpose on the website, it’s only used as a temporary container. So, why is it a problem for you to have this TV in the database? It seems like the easiest way to make this happen. You need a TV set up in order to display the list of conferences, and data will get saved into it whenever you save that resource, even though the TV isn’t used elsewhere. So, data gets stored in this TV as an after-effect of saving the speaker info, even though we don’t need it.
I don’t remember exactly, but I’m sure I must have tried that and it didn’t work for some reason. I guess I should try it again to find out what this issue is, if any.
It’s not really a problem. I’d just prefer if the data was only in one place. That way no-one has to guess where the correct data is stored.
I suppose you are right.
I was just hoping, there was some easy way to read the tv-value in an event ($tv_value = $_POST['tv1'])) and store it in a custom table and then to clear that value ($_POST['tv1'] = ''; so that it doesn’t get stored to the TV-table. But I guess that’s not possible.
Well, I think you’re misunderstanding what I’m trying to do. Of course I can remove the entry from the TV-table in the event onDocFormSave, but constantly adding and then immediately deleting rows from a database table isn’t my idea of a clean solution.
Furthermore with the solution you propose, when you open a speaker-resource, the database-entry is created again and if you leave the page without clicking the “save”-button, it won’t be deleted.
I suspected you would say that! You are certainly thorough. Yes, I was aware of that, but it doesn’t bother me. (Well, it bothers me a little. See below.) I know no one is going to notice that data or try to do something with it because I’m the only one who codes this website. If I were creating a plugin for public distribution I might have to rethink how I accomplish this. But as long as it’s clear to everyone involved that this TV is for temporary data, I don’t see an issue.
When you upload a file through a web form using a php script, the file gets stored in a temporary location on the server first, then it gets moved or copied to the final destination. Does this make the process not a “clean” solution? There is a “tmp” folder used for all sorts of purposes by operating system software. I just took a look in the tmp folder on my web server, and there is an astounding amount of stuff in there. Is this not a “clean” solution? There are many good reasons for using a temporary holding place for data.
If you’d rather use a javascript solution to get the data into the TV after loading, then fine, but that’s going to involve a lot more code. I’d rather keep things simple. The MODX architecture is in general really flexible and capable, but sometimes there are things that are hard to do within it, so I end up having to find creative, if not ideal, solutions without spending hours doing it. If MODX had an “OnAfterDocFormRender” event, then I could easily prevent any data getting saved in this TV. There are lots of other times I’ve wished for such an event as well.
To be honest, though, I don’t know why “OnDocFormRender” works here. The description of that event says it “Fires after a Resource editing form is loaded in the manager. Useful for inserting HTML into forms”. If it fires AFTER the editing form is loaded, then why does saving data into the TV on this event result in that data being displayed in the TV? Shouldn’t that data already have been fetched and loaded? I’m not inserting HTML into the forms here, I’m using PHP to edit the TV value in the database. From the event description, I would expect that if I delete the TV data OnDocFormRender, then that would delete the entry in the database after the editing form is already created and displaying the data I want. I can instead use OnDocFormPrerender to fetch the data I want in the TV and save it to the database, and that works as well. But, I would expect the form to load along with the data, and then I can delete the temporary data in the database OnDocFormRender, but that’s not what happens. If I delete the TV data in the DB OnDocFormRender, it’s gone from the TV in the loaded form as well.
I suspect that ExtJS is loading the TV data after OnDocFormRender has fired. If that’s the case, then maybe injecting a script that uses ajax to run another script that deletes the data after ExtJS has finished loading would delete the saved temporary data before the Save button is clicked?
Following up on this. Yes, “$resource” is available, so I don’t need to use getObject here. Don’t know why I did that; I suspect I copied this script from somewhere else I used it, in a place where $resource wasn’t available. Much simpler!
Ideally, you wouldn’t even need to do that. The value of the TV is generated on the fly when the resource editing form is loaded. The question is, how do we get that data into the TV in the form without having to use the database at all? If I knew more about how ExtJS works, I could probably inject some javascript to make that happen.
I realise (from reading your post) that I must have come across more negative than intended.
To be clear, your (initial) solution is probably the most reasonable and practical solution. I’d probably do the same, if I had this use case in a real-world project.
In your initial post, you stated that you “created a multiselect TV” that “doesn’t get stored”. So I was intrigued, because I tried to do that, but couldn’t avoid the TV-value being stored. Turns out, the value of your TV is stored as well .
I believe your solution with “OnDocFormRender” works, because the event fires after the resource is loaded but before the TVs are loaded from the database. As far as I can tell this happens in the same PHP function and no ExtJS is used to load the TV data.
This part can be achieved with the event ‘OnResourceTVFormRender’. It’s the saving part (without writing data to modx_site_tmplvar_contentvalues) that I could never figure out.
I have tried that. The problem is, that at the time when the event “OnBeforeDocFormSave” fires, the new TV values are already copied from $_POST to the properties of the underlying processor. And from the plugin there is no way (that I can see) to change these processor-properties.
Keep in mind that a TV value for a resource that’s equal to the default value of the TV will not be stored in the TV table. You can use any default value, so you should be able to remove values from the table by setting the value to some arbitrary default value.
I know that default values are never saved initially, but I’ve never checked to make sure that existing values are removed when the TV is set to the default.
.