Making a resource catalog table grouped by TV's compared against external data

I have a seemingly complex programming challenge in my first-ever Revo site. I have read the pertinent sections of the manual and also Bob Ray’s excellent material on MODx objects at http://bobsguides.com/revolution-objects.html. I have also researched getResources.

Here is an example illustrating my situation. I am cataloging books, with one MODx resource representing one book (probably not the best way to go about a large catalog, I know - but my actual project is less extensive). I have added a custom TV for "author". Here is an abbreviated look at the data in my resources so far:

book_title | tv_author |
Hamlet | William Shakespeare |
Richard II | William Shakespeare |
Ivanhoe | Sir Walter Scott |
David Copperfield | Charles Dickens |

In addition, I have a custom table - let’s call it modx_authors - where more information about each author is stored:

author | birth_year | death_year |
William Shakespeare | 1564 | 1616 |
Sir Walter Scott | 1771 | 1832 |
Charles Dickens | 1812 | 1870 |

And finally, a snippet, book_browser, which is to display the catalog tables alluded to in the subject of this post. It is to look something like this:

Group books by: Century of author’s birth

1500’s
Hamlet | William Shakespeare |
Richard II | William Shakespeare |

1700’s
Ivanhoe | Sir Walter Scott |

1800’s
David Copperfield | Charles Dickens |

With each group being its own table. Thus, we are grouping the books by comparing TV’s to external data. As far as I know, there is no way to accomplish this with getResources (please correct me if I’m wrong) - at least not entirely.

I think the question boils down to this: How can I select MODx objects based on a comparison of their TV’s to an external table?

Then again, perhaps I’m totally on the wrong track and someone can help me see that the question is something totally different

Thanks for bearing with me through this long post. Any help, suggestions, or opinions would be greatly appreciated!

It’s possible that you could do it with getResources() if you also made a resource for every author and put all the data in TVs in either the book or the author resources. That would carry a lot of unnecessary overhead, however.

If you’re going to have a custom DB table, you might as well have three.

Authors
ID
name
birth-date
death-date

Books
ID
Title

BooksAuthors
BookID
AuthorID
Century

(You could skip the third table and move the AuthorID field into the Books table if you’re positive that you’ll never catalog a book with more than one author.)

Then you could have just a few resources -- one for each "view" of the data. One for a book, one for an author, one for an author’s books, one for the books by century display, etc.

@BobRay - those are interesting ideas; thank you very much for the input!

In the interim between our posts, I did manager to hammer out a solution, indirectly inspired by this thread and referring to this documentation page, plus your own very helpful page on Revo objects.

Basically, here’s how my ultimate solution works to generate each group:

Create a query to select all content ID’s from modx_site_tmplvar_contentvalues that are associated with values matching a certain subquery, e.g.

Use getCollection to pull all resources matching the previous query:


Use the resulting object in conjunction with the template to generate the group.


I still have details to work out, but everything works so far.

Thanks again for stopping to help.

I’m glad you got it working.