Linking multiple resources together

General
#1

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.

#2

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>
1 Like
#3

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.

#4

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.