[Resolved] Complex Many to Many/ Parent/ Child relationship query

[Author]

Posts

[Ying Zhang]

Hi,

I'm trying to establish a complex many to many relationship between custom posts.

I will use your example of Musicians and Events of your Many to Many tutorial.

For example, Each musicians will have a list of songs they sing, which means we have a Parent - > Child relationship between musician and songs.

Now, musicians play at events, and this is the many to many relationships in your example.

What I'm trying to achieve, is to expand on that, Each musician will have a list of songs, but they won't play ALL their songs for any given event.

I want to be able to list the songs by each musician that is PLAYED at a given event.

So far, I established the following relationships:
Musician / Song : Parent to Child
Musician / Event : many to many using an intermediate type called "Featuring"
Songs / Event: many to many relationship using another intermediate type: "Program"

So I can list:
- All Songs sing by a Musician
- The Musician's current Events
- The Event's list of Musicians
- The Events playing any given song
- Any given Event's complete song list.

But I need help trying to list: Given any Event, The Songs played by a Musician AT this Event.

I looked up child/ parent / grand parent tutorials, but I am still confused,
What I got so far is:
- For an Event post, insert a view that loops the intermediate post "Featuring"
- List Featuring's Parent with id+musician. So I get a list of Musician's playing at this event.
- What should I do to delve deeper to get what I needed?

Thanks!

[Ying Zhang]

Hi I understand there is a queue in getting assistance, I was just wondering if you could give me an eta so i can inform my clients?

Thank you!

[Beda]

Let me see if I can help you here.

1. I'm trying to establish a complex many to many relationship between custom posts.
I will use your example of Musicians and Events of your Many to Many tutorial.

This is a good start.
We also have this, for your reference:
https://toolset.com/documentation/user-guides/many-to-many-post-relationship/
https://toolset.com/documentation/toolset-training-course/ (this is a whole tutorial)
https://toolset.com/faq/how-do-i-associate-one-child-with-several-parents-of-the-same-type/

2. For example, Each musician will have a list of songs they sing, which means we have a Parent - > Child relationship between musician and songs.

Correct, the songs would be the Child post and the Singer/Band the parent Post.

3. Now, musicians play at events, and this is the many to many relationships in your example.

Exactly.
What I usually do here is to choose wether to use a Taxonomy or a Child/parent relation.
Sometimes it's easier to use a Taxonomy.
I assume you fully understood how to set this up, if not, please let me know.

4. What I'm trying to achieve, is to expand on that, Each musician will have a list of songs, but they won't play ALL their songs for any given event.
I want to be able to list the songs by each musician that is PLAYED at a given event.

So you will have a bundle of specific musicians, playing specific music (songs) at a specific event.
In another event, the songs and artists might, but must not, change.

5. Looking at what you have set up so far and how to finish it, you should now be able to create a View that queries Events.
Then, you create a View that queries the Child of events (the intermediate "Program").
This View must have a Query Filter as this:
"Select posts that are a children of the current post in the loop."
Then, you insert this View to the first View where you query Events.

What this allow you to do is to have a list of Events and along with it, a list of each Program associated with an event. So far so good.

Now, in the View where you query the Program, you should call the Parent Post in it's Loop, by using the GUI when inserting Fields.
You can choose the Post Type of the data you insert, so you can also choose if it should be the current post or the parent of the current post.
You will choose the parent Post Type "Songs".

This now produces a list of Events, and along with it, a list of Songs played at each event.

With a Custom Search in the Events View you can now filter the Events and along with it only the songs of the Event will show up.

If you need this in a Single Event, simply create a View of Programs and display the Parent Post Songs, and add a Query Filter of "Select posts that are children of the Post where this View is shown."

If I wrapped my head around your setup correctly, this will help you to achieve the goal in a few clicks.

[Ying Zhang]

Thank you Beda, I really appreciate it.
I actually interdependently reached the same solution as you suggested. Which is great but not quite the *final* query.
What I want:
- The site list a number of events
- The visitor can select any of the event post and see a list of featured artist <--- This is what I achieved so far
- They can click on the featured artist, and see a lit of songs this featured artist play, at this event <--- This is what I want to do.

So I am just 1 step from my goal.
Thank you for any further assistance.

[Ying Zhang]

Looking at what I have made so far. The key is the "Feature" CTP.
From the event, I can query it's child list of "Features"
From the feature post view, I can get it's other parent "artist". From there, I can use a template to get the view to list all that artist's songs.
The problem is I don't know then how to filter through that list of songs, and only display the ones that's "programmed" for the event.
So the psudo-logic is:

loop (this Feature's parent Artist's list of Songs) { <-- I got here
if( the current Song in the loop's Child Program's other parent Event == this Feature's Event) {
display this Song
}
else just skip over this Song as it's not being played at the Event this Feature belong to
}

If you could help me on hoe to get to "the current Song in the loop's Child Program's other parent Event" and how to set up a custom filter to compare it to "this Feature's Event" that'd solve my problem.

Thank you!

[Ying Zhang]

I just had another thought and we can approach the chain of data from the other direction.
Again, we start from "Features" post

loop (this Feature's parent Event's list of Programs) {
from the Program we can get the Program's parent Song
if( the current Song's parent Artist == this Feature's Artist) {
display this Song
}
else just skip over this Song
}

However, I'm still new to Toolset and don;t know how to fetch this chain of data ie,
This Feature's parent's Event's child Exhibit's parent Song's parent Artist
and use that to compare with This Feature's parent Artist.

[Ying Zhang]

Hi,
I'm glad to report I have solved this problem myself.

Here is a recap of the original:
EVENT --- FEATURING --- ARTIST (many to many via intermediate CPT FEATURING)
EVENT --- PROGRAM --- SONG (many to many via intermediate CPT PROGRAM)
SONG ---> ARTIST (Child ---> Parent)

Through this structure I found it very difficult to have a custom query of
Display SONGs played By an ARTIST at an EVENT

I abandoned my original CPT relationship set up, and came up with this
EVENT --- FEATURING --- ARTIST (many to many via intermediate CPT FEATURING)
FEATURING --- PROGRAM --- SONGS (many to many via intermediate CPT PROGRAM, so basically PROGRAM is an intermediate type of a parent intermediate type)
SONG ---> ARTIST (Child ---> Parent)

Via this set up, I was able to easily list all SONGS by an ARTIST via PROGRAM and FEATURING.

It's a cool learning experience but as they say, keep it simple!

I do have another question about sorting child posts via a parent's field. I have added a new thread.

Thanks.

[Beda]

This is great!

I am sorry I could not chime in sooner, I was in my weekend yesterday. I see you solve the issue.

If you agree we might close this here as solved now?

[Ying Zhang]

Thanks.