¿Advanced? Query

[hisoka]

I have this database:

I want to query:

> people
    > name
    > assistMeeting
        > firstSpeaker
             > name
        > secondSpeaker
             > name

Is possible to have this query nested?

I tried this query:

Output:

Still need the nested info for the speakers, like name for example.

P.D.: Clarify that one person only can assist to 1 meeting (they are at the same hour)

[bpj]

Add a couple of left joins to the query pointing to the people table. You’ll need to give each an alias, perhaps s1 and s2.

You can then add the name field from each to the query (these too will need an alias, perhaps s1_name and s2_name)

Sorry, I can’t do screenshots as answering on mobile.

(Left join will show the person/meeting details even if the speaker1 or speaker2 aren’t set)

[hisoka]

Sorry @bpj sounds super complicated to me, been trying with no success.

Is wrong if I change “firstSpeaker” reference and “secondSpeaker” reference to multirefences?
It’s seems a lot easier this way obtain nested data. But maybe it’s a wrong practice… I have a feeling that is is something wrong using multireferences, cause i’m using a “multireference” to reference just one ID.

Or it is maybe that I have a bad logic in the database… I just want to query people, know in what meeting they will assist and then know all the information about that meeting (speaker1, speaker 2, name of the speakers, etc)

Sorry for being that bad at this!

[bpj]

Don’t worry, it’s not that complicated. Wappler makes it easy.

Just add the people table again, just like you added ‘meetings’ to the original query.

This time enter sp1 in the top field (alias) and choose type left.
In the table at the bottom, if there’s already links there use the dustbin to remove them all then:

click the + sign (you are saying which columns to link here) - add peopleID to ‘First’ and firstSpeaker to ‘Second’ (double click to get the list)

Click ok

Then repeat for the second speaker but make the alias sp2 and link the peopleID to the secondSpeaker field

Then look in the field list on the left and you should now have 2 more people tables (one under sp1 and the other under sp2)

I don’t have the same tables so the screenshot below won’t be exactly the same but just add the name from each and change the alias (right hand column) to speaker1_name and speaker2_name respectively

[hisoka]

Ok, let’s try this step by step, maybe it helps another person in the future too!

Added people table to query:

Added second people table, Allias set to SP1, join type to LEFT. But I can’t find peopleID equal firstSpeaker:

[bpj]

You need the meetings table on your query like you did:

then add the people for sp1 and sp2

If you want a list of people and then a sub list of meetings, rather than add the meetings as a join, add the table as a ‘field’ from the left (with the other columns)

Then double click the meetings column (that you added) to modify that subquery and add the joins to the meeting (exactly the same process as above)

This will create a list of people and then each person will have a list of their meetings. The previous one will produce a list of meetings with the person and speakers’ information

[hisoka]

Let’s try:

Output:

{
  "query": [
    {
      "peopleID": 1,
      "name": "Jon",
      "picture": "picture1.jpg",
      "assistMeeting": "",
      "meetings": [
        {
          "sp1_name": "Jon",
          "sp2_name": "Jon"
        }
      ]
    },
    {
      "peopleID": 2,
      "name": "Sandra",
      "picture": "picturesandra.jpg",
      "assistMeeting": "",
      "meetings": [
        {
          "sp1_name": "Jon",
          "sp2_name": "Sandra"
        },
        {
          "sp1_name": "Hideo",
          "sp2_name": "Sandra"
        }
      ]
    },
    {
      "peopleID": 3,
      "name": "Hideo",
      "picture": "hideo.jpg",
      "assistMeeting": "",
      "meetings": []
    },
    {
      "peopleID": 5,
      "name": "Assistant1",
      "picture": "12",
      "assistMeeting": 23,
      "meetings": []
    },
    {
      "peopleID": 6,
      "name": "Assistant2",
      "picture": null,
      "assistMeeting": 24,
      "meetings": []
    }
  ]
}

Is this correct? Cause if this is correct I have to apologize to you cause I was not able to explain what I’m trying to do

Do you se peopleID:5 and peopleID: 6?

They are the only ones that have “meetings” empty but “assistMeeting” filled. The reason being is they are not speakers in any meeting, just assistants. Im trying to retrieve the information of the meetings they will assist as assistants peopleID:5 will assist to meeting 23 and peopleID:6 will assist to meeting 24 .

This is the people table:

And this is the meetings table:

Is your solution still valid to achieve that?

Sorry very much for stealing your time.

[bpj]

You’re nearly there but I think your reference from assistMeeting to the meetings table may be incorrect

Can you show the setup for the assistMeeting column from DB manager?

[hisoka]

Of course!

[bpj]

Just to check you don’t have any database changes to apply in DB manager:

Bottom of your tables list

[bpj]

Looking at your DB structure, each person can only be associated with one meeting so all this can be achieved without subqueries, just joins.

1. Add the people table to your query (add the fields to the columns but not the meetings table)
2. Add the meetings table as a LEFT join (link assistMeeting to meetingID) (add the fields to the columns)
3. Add the two speakers with LEFT join and aliases (as described above and as you did in the sub query)

This will list all people (whether or not they have been linked to a meeting) and the meeting details including speaker names

If you want it ONLY where there is a match between person and meeting, make the meeting join an INNER one

[hisoka]

Lets try:

Output:

{
  "query": [
{
  "peopleID": 1,
  "name": "Jon",
  "picture": "picture1.jpg",
  "assistMeeting": "",
  "sp1_name": null,
  "sp2_name": null
},
{
  "peopleID": 2,
  "name": "Sandra",
  "picture": "picturesandra.jpg",
  "assistMeeting": "",
  "sp1_name": null,
  "sp2_name": null
},
{
  "peopleID": 3,
  "name": "Hideo",
  "picture": "hideo.jpg",
  "assistMeeting": "",
  "sp1_name": null,
  "sp2_name": null
},
{
  "peopleID": 5,
  "name": "Assistant1",
  "picture": "12",
  "assistMeeting": 23,
  "sp1_name": "Jon",
  "sp2_name": "Sandra"
},
{
  "peopleID": 6,
  "name": "Assistant2",
  "picture": null,
  "assistMeeting": 24,
  "sp1_name": "Hideo",
  "sp2_name": "Sandra"
}
  ]
}

Working this way! Thank you so much.

But I have questions :

1. Is possible to do it nested? Looks more nicer and is easier to use it later with server connect.
2. Is this approach better than the multirefences?

I don’t want to build bad habits since im learning now, I prefer to master the basics before continue building with wappler.

[bpj]

My main question is will each person be involved in more than one meeting? It maybe only one at a time, but can they be associated with another one scheduled later that day/week etc.?

If so, your current DB setup will need adjusting - and yes multi reference will help in this case

[hisoka]

No! One person only will be able to be in 1 meeting.

Now I face another doubt.

Let’s say I add a subtable for people table containing images for each people, how do I get the images for just speaker1 for example? Seems that If I add for example the subtable “picturesPeople” from left join people sp1 it adds the normal subtable and it retrieves the images from all people, not just from speaker1.

Thank you so much for your help, I feel I’m improving a lot!

[bpj]

I would import their picture either to the server or S3 (I’d suggest using an S3 store to save your server getting clogged with images) and save the image url/path in a field in the people table - no need for a sub table

[bpj]

What are you trying to achieve on your page? How do you want to display the info?

[hisoka]

Sorry I expressed myself badly. Images was a bad example.

Let’s say I want to retrieve information from a subtable called awards (like oscar, golden glove…etc) :

I want to retrieve all the awards of speaker1 and all the awards for speaker2 but separate, not all together.