Implementing many to many relationships

[Jeff C]

Hey,

I haven't actually started with ProcessWire yet. I came across it via a Smashing Magazine article and it has piqued my interest. 

I've been working in WordPress for about a 2 years. I have intermediate HTML5, CSS3 skills, and have been furiously teaching myself PHP/Javascript for the past couple of months, since I want to dig more into wordpress and develop skills on other platforms.

At the moment I have a project involving a niche movie/TV database, with associated review system. I have already built a custom solution in WordPress involving custom post types, custom fields, custom categories, and user access levels. 

The site will be highly relational, as it has Movies, TV shows, Actors, Directors, Producers, Cinematographers, Studios, Production Companies etc.

The complexity of the relations comes in when you consider that an Actor can be a director and producer too, and can be attached to movies in multiple ways. Movies have multiple producers and sometimes multiple directors.

So using wordpress custom post types and custom fields I have implemented many to many relationships via intermediaries, given the limitation that each child can only have one parent of each type, but a parent can have many children of a given type. I used a custom types and display plugin for this.

Just looking at the Movies and Actors/Directors/Producers situation.

I created Movies and People as Parent post types. I created groupings of custom intermediaries to join the parent post types: Actors-In-Movies, Directors-of-Movies, Producers-of-Movies.

So now a Person can be linked to a movie in multiple ways, and to multiple movies in the same way etc.

Which was great but complicated, but it allows me to display the required fields via their many to many relationships.

An example of a problem I came up against that required digging deep was:

On a page(post) showing an actor, listing all the movies that he has been involved in is easy. I can list them by role, and display information from those linked posts, such as the title of the move, year etc.

But because this a wp_query of the child post, displaying some information from it's other parent, the problem comes about when trying to order that information by a field pulled from another parent e.g:

Person1

Acting Roles

• Movie 1 - Character Name -  1972 (movie release date - from other parent)
• Movie 2 - Character Name - 1971
• Movie 3 - Character Name - 1973

Production Roles

• Movie x - Producer - 1995
• Movie y - Executive Producer - 1983

The information being displayed is from querying the intermediary post, some directly (such as charcter name) while other information is being pulled from the other parent (movie name, movie release date).

The table of information is easily sorted by fields from the linked child, ie it would be easy to sort them, alphabetically, by character name or production role. However in such a table it is more usual to sort them by the release date of the movie, either chronologically or reverse chronologically.

But in Wordpress this was difficult, because I am trying to order the information of a query of a child post by information not included on that child post, but by information queried from another parent of that post.

A solution was to duplicate inputting of information from the movie release date field in the movie parent post, into a movie release date field in the child intermediaries...  An anathema of  process. 

I identified 2 solutions (with help).

1. Since users won't be accessing the WP Admin directly as I have implemented a user account system that keeps them in a designed user area front end, with forms to submit data. On those forms I have a system that writes data to both posts. So when a user submits a movie, the year of that movie is written into movie year fields in both the movie post movie year db entry and the intermediate (actor-in-movie/director-in-movie/producer-in-movie) post movie year db entry upon submission. It leads to complications when people don't already exist in the DB etc

2. When the DB is queried, use custom code to manipulate the list of intermediate posts after it has been retrieved and change the sort order. Not writing to the DB as above, but doing it on the fly before display by looping through the posts, retrieving the id of the corresponding movie, retrieving the year custom field from the post meta of the associated movie, adding that as an entry in the posts array for the current post, and then after having looped through each of the posts so that they now also have a year entry, sorting the posts array based on the year field, and then replacing the posts array which is part of $query with the sorted posts array.

... I do enjoy overcoming these hurdles, but since I have been reading great things about ProcessWire I am interested in tackling this same problem. 

Does ProcessWire deal with relationships in a similar way, I have come across mention of one-to-one and one-to-many relationships. I would therefore surmise that you can create many-to-many in much the same way, or is there an easier way? 

Does anyone have any pointers?

Thanks for any advice in advance.

[kongondo]

@Jeff C,

Welcome to the forums and ProcessWire .

27 minutes ago, Jeff C said:

The complexity of the relations comes in when you consider that an Actor can be a director and producer too, and can be attached to movies in multiple ways. Movies have multiple producers and sometimes multiple directors.

The usual way is to use page fields (page reference fields). Please see this content:

 

[Robin S]

Hi @Jeff C,

I'm not familiar with WordPress and its limitations, but everything you have described sounds easily doable in ProcessWire.

1 hour ago, Jeff C said:

But because this a wp_query of the child post, displaying some information from it's other parent, the problem comes about when trying to order that information by a field pulled from another parent

In PW you can use a parent-child structure to define a relationship, but you don't have to. For more complex relationships a Page Reference field provides a lot of flexibility.

For example, seeing as a person may initially be an actor, but then later direct or produce a movie, you might decide not to use parents such as "Actors", "Directors", etc, but rather place all your Person pages under a "People" parent. Then they would take on the status of being an actor or director by virtue of having been selected as an actor or director in one or more Movie pages.

So on a Person page you could easily list their directing and acting credits with selectors such as:

// the $page API variable represents the current page, i.e. this Person

// find Movie pages where this person was selected as a director
$directed_movies = $pages->find("template=movie, directors=$page, sort=-release_date");

// find Movie pages where this person was selected as an actor
$acted_movies = $pages->find("template=movie, roles.actor=$page, sort=-release_date");

In the example the selector for $acted_movies is a little different to $directed_movies because for acting roles in a movie you want to not only select the actor but also name the character the actor played. Here I am assuming the use of a Table field, with a Page Reference column for actor and a Text column for character. There are also other fields such as Repeater or PageTable that could be used instead of Table.

The above may not make complete sense now but give PW a go and you'll find things are much easier than you have been used to.

[Jeff C]

@kongondo

Thank you for the welcome and tutorial link, much appreciated.

@Robin S

Re: People parents, that is the approach I used with WP. I created the Custom Post Type People, then created intermediaries to link to movies as different roles, though these are using said parent-child structure.

The actual plugin I used, toolset, is updating soon and negating the need for intermediaries - it will link directly via a relationship field (similar solutions exist in other plugins ACF etc)... but that isn't scheduled to occur until April. This problem was the original seed that lead me to learning PHP, now that I have I want to explore different platforms and be able to offer a variety of options to clients.

The page reference field looks perfect, and the language doesn't seem problematic. I will go through the tutorials @kongondo provided above and get my bearings.

Thanks guys

Jeff

[webhoes]

there is a wp module that does all relationships. WP movie library. I have created this site with that for a friend.

www.totf.nl

As for PW use the page to page reference module. It works like a charm and you can make overviews of everything like @Robin S said. 

Similar example with use of page to page and higly clickable is here... chrysemys.nl (still in construction).

I find PW to be much faster than WP and once you get the hang of it, it is much simpler to work with than to work around the wp_loop.

[Jeff C]

Hey @webhoes

Thanks for the pointer to that site. I have seen WPMovieLibrary before, it is great for focusing on movies and reviews, and being able to list the movies that Actors have appeared in, but I am actually needing something a bit more. 

The database and review site will be more like IMDB in depth of detail, but for a niche film sector. It won't just have a database of Movies & TV Shows, but also of Actors, Directors, Choreographers, Locations, Studios, Production companies etc. Each with their full details, bios, image galleries etc. An artist/film-maker friend of mine has spent the last 10 years reviewing and collating all the information of a specific genre, I am putting it online for him. I believe WPMovieLibrary focuses on Movies as a CPT and Actors, Directors etc are more like indexable custom fields, but without any depth to their archives. 

I chose to work with Toolset/Types/Views over ACF as I'd used it on some artists sites before. It has done the trick, but I am keenly interested in exploring other platforms apart from WP. 

I have PW installed locally and am going through the tutorials, I like what I've seen so far. I am also quite excited about being able to practice my PHP developing with this. It seems perfect, each of those aforementioned CPTs can be it's own page linked with page reference fields, with none of the messiness of intermediaries.

Thanks again

 

Jeff

 

 

 

[webhoes]

Your right WP movie libray is a bit more focues on the movies. You can add all other info with ACF if you want and output it in the theme. I first build that site in Drupal and was awesome but my friend wanted to have WP because he can do more with it himself. Check out the wp wrangler module that is basicly views for WP. I found this more flexible than toolset. 

Now my choise would be PW. You can build anything on your wishlist. If there is no significant need for WP I would go for PW.

totf is also a niche movie site  although sometimes going mainstream as distributors let them review films before they come out.

[Jeff C]

I did think of using ACF, but since I already had Toolset, knew my way around Views and it gave me the Access, Form and Layout tools I could use too. For pure custom fields, I think ACF is more powerful, but Toolset provides an integrated suite that can make prototyping sites incredibly fast. Having said all of that, I am wanting to get more into using OOP PHP7 correctly, and relying less on tools and plugins.

When you talk about wp-wrangler, is that Query wrangler for WP, or is that a module here?

I am quite looking forward to getting stuck in with PW, it seems intuitive and clear... This site I am doing is more an art project between me and a friend, so no client asking for WP, something to cut my teeth on till I can get proficient.

[webhoes]

Yes, Query Wrangler for WP. Great for quickly making lists.

I don't have toolsets because it was too limited for me (especially in many to many relationships). I fixed that with ACF.

Dive into PW, I just started 3 months ago with programming and 2 months ago with PW. It's great to learn PHP and get some shit done

[Jeff C]

I'll look into Query Wrangler. Toolset is about to release an update with direct Many to Many relationships, along the lines of the defunct Post-2-Post. Since I have it already I am waiting to see how they implement their solution, if it isn't satisfactory I will probably switch to an ACF/CPT-UI plugin combination when using WP.

Anyway, digressing... I am liking the look of PW.

Cheers