SOLVED-Display referenced collection, filtered by other referenced collection - Multi-Collection join.

Ask the community
,
1

I have three collections as follows:
EVENTS - List of events
EVENT_DETAIL - References EVENTS - List of event detail needs
VOLUNTEERS - References EVENT_DETAIL - List of people assigned to tasks/needs

I have a registration process working, filtering by EVENT, then EVENT_DETAIL along with code. What I need help with please is displaying the VOLUNTEERS collection in a table element by EVENTS, without displaying the specific EVENT_DETAILS. I want the user to be able to select an EVENT and see all the VOLUNTEERS associated.

Here’s an example of what I would like to accomplish if using sql:

select ed.descr, v.first_name, v.last_name, v.email
from events e,
event_detail ed,
volunteers v
where e.pe_title = ed.pe_title
and ed.ped_title = v.ped_title
and e.pe_title =

Thank you in advance!

2

Thanks to an esteemed colleague, the following code provided a solution.

import wixData from 'wix-data';

$w.onReady(() => {

});

export function tblEvent_rowSelect(event) {
  wixData.query("PFA_EVENT_DETAIL")
  .eq("pfa_event", $w("#tblEvent").rows[event.rowIndex]._id)
  .find()
  .then((results) => {
 var detailIds = results.items.map( det => det._id)
 //console.log(detailIds)
    $w("#dsVolunteers").setFilter(wixData.filter().hasSome("ped_title", detailIds))
    $w("#dsVolunteers").refresh()
 
  })

}

It would be nice to have a feature request for the equal function on find to allow you to pass in an array of values and the results should be anything listed in that array; like a SELECT IN.

3

Vicki

Thank you so much for this code!!!
I have been searching for the same solution for weeks now!!! Tried about everything I knew (as a novice to JS and Wix).

4

Great! I am glad it helps someone else. I was a little surprised I couldn’t find the answer on here so I felt I should contribute.

5

In deed searched for this answer already everywhere on the forum, but no answer. Not from the moderators, nor from more advanced programmers… And there are many questions about this problem on the forum.
Always sorry to see that solutions to questions aren’t really given as should be especially from moderators of the forum and advanced programmers. I see a lot of people struggle with a lot of coding and when help is asked, they mostly only give links to corvid examples or guidelines, but they forget that for someone who has already difficulty to understand English, people novice to JS and novice to Wix, it’s not always easy to understand the WIX way of coding nor their explanation… And then there’s people who just learn better from examples too.
So really a big thank you to people like you just sharing code as should be done.