Linking multiple resources together

[stream7]

I’m building a site to host films.

I have used collections to create a group of individual pages for each film, and also another collections container to host individual bio pages for each actor.

I need to link these together, so that on the film pages it will list the actors in that film, and you can then click on the link to visit that actors bio.
I would also like the reverse to work so links on the actor’s bio page will link to the films they are in.

Can anyone suggest the best way to do this?
I was wondering if getresources and tagger?

Thank you.

[halftrainedharry]

I would create a custom database table. The easiest way to do this is to use MIGXdb (part of the extra MIGX). There is an introduction video on youtube that you could watch. Then to enter the values instead of a CMP use a TV of type “migxdb”.
The schema of your database table could look like this:

<?xml version="1.0" encoding="UTF-8"?>
<model package="movies" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" version="1.1">
	<object class="MovieActor" table="movie_actor" extends="xPDOSimpleObject">
		<field key="resource_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
		<field key="actor_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0"/>
		
		<index alias="actor_resource" name="actor_resource" primary="false" unique="true" type="BTREE">
			<column key="resource_id" length="" collation="A" null="false" />
            <column key="actor_id" length="" collation="A" null="false" />
        </index>
		
		<aggregate alias="Movie" class="modResource" local="resource_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Actor" class="modResource" local="actor_id" foreign="id" cardinality="one" owner="foreign" />
	</object>
</model>

[bruno17]

what @halftrainedharry says.
Instead of a migxdb - TV you could also have a listbox-multiple TV and handle the storing/removing of relations with a plugin at OnDocFormSave. Maybe I have something lying around, which I could share, if needed.

[bobray]

Good advice. To give you an overview, because actors can be in more than one movie and movies can have more than one actor, you need a custom “intersect” DB table that connects the two and has just two fields (besides the ID field) actor_id and movie_id.

That way you can quickly find an actor’s movies or a movie’s actors. You could even create a way to search for movies where two actors worked together or where an actor worked with a particular director.

halftrainedharry’s schema uses the existing modResource table for both movies and actors, so you might have to create TVs for the extra fields unless you can get by with the existing resource fields.

If you will be searching or sorting by extra fields, (e.g., director, genre, awards, release year, etc.) though, you will get better performance if that information is in a custom table for an object that extends modResource rather than in TVs, which are cumbersome and slow for searching and sorting.

[stream7]

I have finally got back to working on this project and I’m working though your suggestions!

I would imagine that there would be a TV for the film resource, where I would select the resource IDs for the resources for each actor in the film.
Then in the front end, you would be able to see a list of the actors in that film and click on the link to take you to the actor’s resource.

This should also be reversed so on the actor’s page you would be able to see a list of the film resources that the actor is related to.

@bobray I think this is how you are suggesting using a ‘intersect DB’ but I’m struggling to see how this will work with @halftrainedharry suggestion?

I have seen this tutorial Multi-select TV for related pages - Tutorials | MODX Documentation but I’m not sure how this would work to link actors to films and films to actors?

[halftrainedharry]

The problem with using a TV of input type “Listbox (multi-select)” for your film-resources (as described in the tutorial you linked) is, that the values are then saved as a comma-separated list of actor-ids (in the database table site_tmplvar_contentvalues).

This works fine for displaying all the actors that belong to a movie. But it’s hard and inefficient to create a list of all the movies for a given actor. Therefore it’s probably better to save the data in a custom database table.

One way you can do this (as @bruno17 said) is to use a TV of input type “Listbox (multi-select)” (like in the tutorial) but then create an additional plugin that saves the data to your custom database table as well (so that it can be easily queried).

Another way is to use a TV of input type “migxdb” instead.

[stream7]

I have got the custom database setup, would there be a way to select the different resources from the migxdb TV on the film resource?

I have used the following

{
  "formtabs":[
    {
      "MIGX_id":7,
      "caption":"Links",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":13,
          "field":"resource_id",
          "caption":"resource_id",
          "pos":1
        },
        {
          "MIGX_id":14,
          "field":"actor_id",
          "caption":"actor_id",
          "pos":2
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||publish||unpublish||recall_remove_delete",
  "actionbuttons":"addItem||toggletrash",
  "columnbuttons":"",
  "filters":"",
  "extended":{
    "migx_add":"",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"movies",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":2,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":1,
    "packageName":"movies",
    "classname":"MovieActor",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":2,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"Resource",
    "has_jointable":"no",
    "getlistwhere":"",
    "joins":"",
    "hooksnippets":"",
    "cmpmaincaption":"",
    "cmptabcaption":"",
    "cmptabdescription":"",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "renderer":"",
      "sortable":"false",
      "show_in_grid":1
    },
    {
      "MIGX_id":8,
      "dataIndex":"actor_id",
      "header":"actor_id"
    },
    {
      "MIGX_id":9,
      "dataIndex":"resource_id",
      "header":"resource_id"
    }
  ],
  "category":""
}

But it doesn’t seem to be saving?

[halftrainedharry]

Try it with these settings in your MIGX-configuration:

Tab MIGXdb-Settings:

• Package = movies
• Classname = MovieActor
• Joins = [{"alias":"Actor","selectfields":"pagetitle"}]

Tab Actionbuttons:

• addItem

Tab Contextmenues:

• update
• remove

Tab Columns:

• Header = Id | Field = id | Show in Grid = no
• Header = Actor | Field = Actor_pagetitle

Tab Formtabs → Fields:

• Fieldname = actor_id | Caption = Actor | Input TV type = listbox | Configs = {"typeAhead": "true", "typeAheadDelay": "250"} | Input Option Values = @SELECT `pagetitle`,`id` FROM `modx_site_content` ORDER BY `pagetitle`,`id`

---

The value of the db column “resource_id” should be set automatically. You probably have to adjust the @SELECT statement in the listbox to only list your actor-resources (... WHERE `parent` = ...)

[stream7]

I think we are getting there!

I’m able to select the actors on the movie resource and I’m getting the following array output -

Array
(
    [id] => 11
    [resource_id] => 3
    [actor_id] => 20
    [_alt] => 0
    [_first] => 1
    [_last] => 1
    [_idx] => 1
    [idx] => 1
    [property.packageName] => movies
    [property.classname] => MovieActor
    [property.where] => {"resource_id":"3"}
    [_count] => 1
    [_total] => 1
)

Do you know how I could pull the pagetitle and also an image from the actor resource into the template?

[halftrainedharry]

Do you use the snippet migxLoopCollection to output the data?
In this case you should be able to use the joins property.

&joins=`[{"alias":"Actor"}]`

[bobray]

@stream7

@bobray I think this is how you are suggesting using a ‘intersect DB’ but I’m struggling to see how this will work with @halftrainedharry suggestion?

@halftrainedharry’s schema describes exactly the intersect table I was referring to.

[snowcreative]

I agree that the approaches above are the most ideal. But for anyone not quite up for creating custom tables and packages, here’s another approach, but how viable it would be depends on how many films and actors you have.

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 films to assign which actors they have. Then, on the page for an individual film, you just explode the TV value, which may contain multiple IDs, into individual IDs and run a loop to load each actor for that film. For the individual actor pages, you would query (using PDOtools or whatever) the list of films and search for associated TVs that contain the ID of the actor. That’s not as fast as using intersect tables, but unless you have thousands of films, you probably won’t notice that much of a difference. Plus, once the contents of each film page or actor 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 actor, you would have to go into each individual film and assign the new actor. 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 is similar to what Bruno was suggesting above, but without using a custom table.) 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! This is working well on a site with dozens of conferences and hundreds of speakers.

If you are going to have actors, directors, producers, shooting locations, etc. etc., then you really do need to create a custom DB and a custom CMP for the whole thing rather than using standard resources at all. If you just have films and actors, then the approach I described will work.

[bobray]

@snowcreative

If you are going to have actors, directors, producers, shooting locations, etc. etc., then you really do need to create a custom DB and a custom CMP for the whole thing rather than using standard resources at all. If you just have films and actors, then the approach I described will work.

I would amend this to say that you might still be able to use standard resources for each of your objects (actors, movies, directors, etc.) but use custom intersect tables for the relationships between them, though going all custom would probably make things faster and more flexible.

If you’re up to throwing in some JavaScript, you could also have dynamic content where, for example, on an actor’s page, you could click on a Movies or Directors button and have the list filled in right on that page, instead of hopping around from page to page.

For that, you’d create processors that you’d call with AJAX that would return the results (based on the appropriate intersect table) and then display it – all via JavaScript.