Post relationships

Hi Anh,

In my latest project, I have several requirements for relationships so I'll give you one example which involves events and bands. To simplify I will not include all the custom meta for each custom post type, but will highlight the key elements that form the structure in CAPS:

CPT EVENTS with custom meta LOCATION, DATE, BANDS
CPT BANDS with custom meta TYPE, AVAILABILITY, EVENTS

The above shows how a custom booking system could allow site users to create an EVENT at a particular LOCATION which is available on a given DATE. Then they could book a BAND by TYPE of music that has AVAILABILITY on the chosen DATE.

The repeatable booking form would allow adding multiple BANDS to one EVENT creating a one-many relationship. In addition, the site would allow users to create a TOUR containing multiple EVENTS with multiple BANDS at each EVENT thus creating many-many relationships.

If people wanted to purchase tickets, they could search for EVENTS in their LOCATION and see what BANDS are playing on a given DATE, or they could search for BANDS they like and see what DATE they are playing near their given LOCATION (using FacetWP integration)

There are of course many other uses for relationships in WordPress for example:

CARS and PARTS - Many PARTS could fit many CARS
BOOKS and AUTHORS - Many AUTHORS could co-write many BOOKS
PERSON and PEOPLE - Social relationships between one PERSON or many PEOPLE

Hopefully this has given a clearer insight into my thoughts. Due to the complex nature of post relationship SQL queries, a custom table is essential to avoid hundreds of self joins and ensure efficient indexing, scaling and flexibility.

I'm happy to help with this - it's something I previously developed as a bespoke solution, but will now be using extensively within the WordPress ecosystem. A good post relationship extension for MetaBox would certainly make it the best CMS framework plugin for WordPress.

David

Hi Anh,

The implementation was for a previous client using .NET and MsSQL so it's not an identical comparison, but I can explain the principles which remain the same.

The two relationship tables were relatively complex but allowed for all conceivable searches to be performed extremely quickly (with only one join) and covering indexes which enabled the system to scale past millions of records with no noticeable performance degradation.

Relationship Structure table:
tId - Unique table ID
relationshipType - Type of relationship (1-1,1-Many,Many-Many)
contentType - Type of content (event)
contentId - ID of the content
relatedContentType - Content type of related content (band)
relatedContentId - ID of the related content

Relationships table:
tId - Unique table ID
contentId - ID of the content
relatedContentId - ID of the related content
biDirectional - Whether relationship is bi-directional (boolean)

I hope the above is enough for you get a clearer understand of how we achieved content relationships. Posts2Posts is certainly the best relationships plugin for WordPress (so far) but it is no longer supported and is missing advanced relationship/front-end capabilities.

David