I am converting a traditional web/db app to Evo. All data will be added/updated through the manager. The main record table in the web app makes sense set up as individual page resources however there are a lot of records and it will become unweildy to locate a record in the manager tree.
The app db also contains 2 tables with a many to many relationship to the main records and this is what I'm struggling with. What is the best way to accomplish this in modx? I do not want duplication of this data.
Thanks!
It's not really a MODX question, but you need a third table with the intersection information. Something like this:
Books table
ID Title Author (other book fields)
1 Book1 3
2 Book2 1
3 Book3 2
Authors table
ID Name (other author fields)
1 Author1
2 Author2
3 Author3
BooksAuthors table
ID BookId AuthorId
1 3 1
2 2 3
3 1 2
In this example:
Book1 was written by Author2
Book2 was written by Author3
Book3 was written by Author1
Hope that helps.
------------------------------------------------------------------------------------------
PLEASE, PLEASE specify the version of MODX you are using.
MODX info for everyone:
http://bobsguides.com/modx.html
Using MODX Evo 1.0.6
The original database app is set up exactly the way you describe BobRay. Now I am moving it all to MODX and one of the primary goals is for all data entry/updates to be done through the manager.
Using your book example:
I set up a resource (published page/show in menu) for each book record in the "Book Table" with TVs for the properties. I set up ManagerManager to make this nicer/easier. Book records also link to other Book records based on some specific book TV values and ditto handles this all nicely on the front end.
Now I need to set up something in MODX to take the place of the "Authors Table". The Author records will never be pages, never show in the menu, and never be displayed outside of a book record (page). Similarly I need the Author records to show up in the Manager for a Book record so they can be updated in context of a book. This is the part I can't get a handle on and hoping there is a mechanism or best practice for doing this.
I could see setting up thousands of page resources to act as the Authors table, and hide them from the menus, and pull them into the book pages using Ditto on the front end. But I don't know how to use ditto in the Manager to bring the linked Author records to the Book page for editing.
In this case the Author is really an event with no identifier other than a unique id - would be a nightmare to manage as thousands of unnamed seperate resources in MODX. Hoping there is a much better, cleaner way to do all this.
Hope this makes sense! Thank you!
[ed. note: cottonhills last edited this post 14 years, 5 months ago.]
I think you can tap into a hidden feature of managermanager for this. Note that you'll have to redo the queries but this should give a good example of what can be done.
Add these rules to your managermanager rules:
// id of the template your documents use
$doc_tpl_id = '3';
// document id of the top level parent where docs are stored
$doc_parent_folder_id ='21';
$uparent = $modx->runSnippet('UltimateParent', array('id' => $id));
if($uparent == $doc_parent_folder_id){
// create the author settings tab
// read dbapi http://wiki.modxcms.com
// add your author table name
$author_table_name ='';
// $id is the id of the current document
$where ='book_id="'.$id.'"';
$author = $modx->db->getRow($modx->db->select("*",$modx->getFullTablename($author_table_name), $where, "", "1"));
$author_bio = $author['author_bio'] !='' ? $author['author_bio'] : '';
$author_settings_form ='
<div class="gridHeader" style="height:14px; padding:4px;">Author Info</div>
<table width="100%">
<tr>
<td align="left" nowrap="nowrap">Author Bio</td>
<td align="left">
<textarea id="author_bio" name="author_bio" cols="30" rows="4">'.$author_bio.'</textarea>
</td>
</tr>
<tr>
<td colspan="2"><div class="split"></div></td>
</tr>
</table>
</div>';
mm_createTab('Author Settings', 'authortab', '', $doc_tpl_id, $author_settings_form, '100%');
}
Then create a plugin with the code below and check the OnDocFormSave event:
$author_table_name ='';
// check if author exists
$where ='book_id="'.$id.'"';
$numrows = $modx->db->getValue( $modx->db->select( 'count(*)', $modx->getFullTablename($author_table_name), $where ) );
$fields = array(
'author_bio' => $_POST['author_bio']
);
if($numrows ==1){
// here we update
$where ="book_id='" $id."'";
$update = $modx->db->update($fields, $modx->getFullTablename($author_table_name),$where);
}else{
// add a new entry
$insert = $modx->db->insert($fields, $modx->getFullTablename($author_table_name));
}
If it were me (and I know it's not), I would use Revolution, put all three tables in the MODX DB, and use xPDO to deal with them. The CRUD tasks could be done with forms in the front end, or with a Custom Manage Page (CMP).
In Evo, eForm2DB might be helpful (if you can find it).
I think in either system you may have serious performance problems if you try to store data in resources, which carry a ton of unnecessary overhead.
------------------------------------------------------------------------------------------
PLEASE, PLEASE specify the version of MODX you are using.
MODX info for everyone:
http://bobsguides.com/modx.html
