Query many Collections with reference fields

Ask the community
,
1

Hello everyone, I summarize my problem, I do not know if it can be done.

I have 3 collections and a table

1 First Collection “RegoleCodeUtente” which has two reference columns

  • First reference “DB_regCode_UserID” from the “Infoutenti” collection with primary key DB_memberUserId
  • Second reference “DB_regCodeCode” of the “DataSetCode” collection with primary key DB_codeCode

2 Second Collection “DataSetCode” which has a reference column

  • Reference column “DB_codeCodeDataNameEvento” of the “DataSetCerimonie” collection with primary key DB_cerimonieCodeDataNomeEvento

3 Third Collection “DataSetCerimonie” where there is the column DB_cerimonieNome

What I’m trying to do, is knowing this data “DB_regCode_UserID” reference of the first collection, I have to find all the corresponding values ​​of “DB_cerimonieNome” of the third collection and insert them in a table

Is it possible to make another query from the results of a query?

If I try to add or copy the “DB_cerimonieNome” field in the “DataSetCode” collection as I do with .include, I will update the table with all the results, since I have always seen in the esmpi always find the corresponding value of item [0]

Is there a solution?

I hope I explained myself, I put an example of what I want to do but I think the road is wrong

wixData.query('RegoleCodeUtente')
	.eq('DB_regCode_UserID', member)
	.include("DB_regCodeCode")
	.find()
	.then(results => {
		let items = results.items;
		let item = items[0];  //with this I find only the first result. But I have more results?
		let Evento = items.DB_regCodeCode.DB_codeCode;//
		wixData.query('DataSetCerimonie')
		.eq('DB_codeCode', Evento)
		.include("DB_codeCodeDataNameEvento")
		.find()
		.then(results => { // not work
                    let items = results.items; 
                    let item = items[0]; //with this I find only the first result. But I have more results? 
                    let EventoTable = items.DB_codeCodeDataNameEvento.DB_cerimonieNome;
                    $w('#teamsTable').rows = EventoTable; 
		});
	}
});

I wrote a horrible code, but I hope to explain better what I need
Thank you all

2

Hi RedDany,
Try this:

wixData.query('RegoleCodeUtente')
	.eq('DB_regCode_UserID', member)
	.include("DB_regCodeCode")
	.find()
	.then(results => {
		let items = results.items;
		let item = items[0]; //with this I find only the first result. But I have more results?
		let Evento = items.DB_regCodeCode.DB_codeCode; //
		wixData.query('DataSetCerimonie')
			.eq('DB_codeCode', Evento)
			.include("DB_codeCodeDataNameEvento")
			.find()

	})
	.then(results => { // placed after the first .then scope
		let items = results.items;
		let item = items[0]; //with this I find only the first result. But I have more results? 
		let EventoTable = items.DB_codeCodeDataNameEvento.DB_cerimonieNome;
		$w('#teamsTable').rows = EventoTable;
	});

Good luck!
Roi.

3

I previously had a working solution where I used a referenced collection and when I submit the data to the parent collection it updates with the referenced data. I then changed to using two drop-downs and it needs to match one item with another in the child collection that is referenced into the parent and now data validation fails, well I didn’t think it would work as the child Species collection item must have an ID to know where it sits in the parent collection Avian_data.
So the below is the query that finds the data in the child collection, how do i find it in the parent as a referenced collection and still do a conditional comparison where it says well if you select this from dropdown1, choose this item from dropdown2 to submit to the parent collection.

wixData.query(“Species”)
.contains(“familyCommonName”, $w(" #dropdownFamCommonName ").value)
.limit(1000)
.find()
.then(results => {

export function dropdown1_change(event, $w) {

let index = $w(“#dropdownLocation”).selectedIndex; // Get the selected index
let itemOptions = $w(“#dropdownLocation”).options[index] // get the selected item
let itemLabel = itemOptions.label; // grab the label of selected object

wixData.query("Locations") 
    .eq("suburb", itemLabel) 
    .ascending("suburb") 
    .limit(1) 
    .find() 
    .then((results) => {

let items = results.items

        $w("#googleMaps1").location = {

“latitude”: parseFloat(items[0].lat),
“longitude”: parseFloat(items[0].lon),
“description”: “Birding Site”
};

    });

}

function uniqueDropDownFamCommonName() {

wixData.query("Species") 
    .limit(1000) 
    .find() 
    .then(results => {

const uniqueTitles = getUniqueTitles(results.items);
$w(“#dropdownFamCommonName”).options = buildOptions(uniqueTitles);

    });

function getUniqueTitles(items) {

const titlesOnly = items.map(item => item.familyCommonName);
return [… new Set(titlesOnly)];

}

function buildOptions(uniqueList) {

return uniqueList.map(curr => {
return { label: curr, value: curr };

    }); 

}

}

export function dropdownFamCommonName_change(event, $w) {
//Add your code for this event here:
uniqueDropDown2();
$w(“#dropdownTaxonSpecies”).enable();
}

function uniqueDropDown2() {

wixData.query("Species") 
    .contains("familyCommonName", $w("#dropdownFamCommonName").value) 
    .limit(1000) 
    .find() 
    .then(results => {

const uniqueTitles = getUniqueTitles(results.items);
$w(“#dropdownTaxonSpecies”).options = buildOptions(uniqueTitles);

    });

function getUniqueTitles(items) {

const titlesOnly = items.map(item => item.taxonName);
return [… new Set(titlesOnly)];

}

function buildOptions(uniqueList) {

return uniqueList.map(curr => {
return { label: curr, value: curr };

    }); 

}

}

4

I’m having a challenging with joining the multiple-references query with query of a regular field. I’m essentially using multiple dropdowns to perform a query. One of these dropdowns is of a multiple reference field for which i’ve used your (.hasSome), which works (shown below). Now i need to join this with the dropdown filter of the regular field in my database.

The problem is each of the dropdowns work separately. I’d like all the dropdowns to work together.

Here’s my code:

// Filter function for region, subCity & ownership dropdowns
function searchFilter() {
wixData.query(‘Hospitals’)
.eq(‘region’, $w(" #dropdownRegion “).value)
.and
(wixData.query(“Hospitals”)
.contains(“subCityZone”, $w(” #dropdownSubCity “).value))
.and
(wixData.query(‘Hospitals’)
.contains(“ownership”, $w(” #dropdownOwnership “).value))
.find()
.then(res => { $w(” #resultsTable2 ").rows = res.items;
let totalCount = res.items.length;
if (totalCount === 0) {
$w(’ #textCount ‘).text = “No result found!”;
} else {
$w(’ #textCount ').text = ${totalCount} result found.;
} })
. catch ( (error) => { let errorMsg = error.message; let code = error.code;
} ); }

Below is the multiple reference query.

//filter by radiology procedure dropdown
async function queryRadiology() {
let RadProcedure = $w(" #dropdownServices ").value;
let radiology = ( await wixData.query(‘Radiology’)
.eq(‘title’, RadProcedure)
.find()).items[0];
let res = await wixData.query(‘Hospitals’)
.hasSome(‘radiology’, [radiology._id])
.find();
console.log(res.items);
$w(’ #resultsTable2 ‘).rows = res.items;
let totalCount = res.items.length; if (totalCount === 0) {
$w(’ #textCount ‘).text = “No result found!”;
} else {
$w(’ #textCount ').text = ${totalCount} result found.;
} }

So, how can join these two function or ensure the queries run at the same time, if selected?

5

Roi,
I am using .include in my query in order to get referenced fields i want to sort my query results by before assigning it to table. I do see referenced fields in my results, but they are not sorted…
Can you help me what is wrong with my code?

function GetSelectedFields(){

let currentEventID = $w(‘#dynamicDataset’).getCurrentItem(“sk4UnifiedEvent”);

let query= wixData.query(“EventSelectedFields”)
.include(“selectedField”)
.descending(“selectedField.cefName”)
.find()
.then( (res) => {
if (res.items.length > 0) {
let items = res.items;
$w(‘#tableTest’).rows = items;

}  **else**  {

// handle case where no matching items found
console.log(“no items found”);
}
} )
. catch ( (error) => {
let errorMsg = error.message;
let code = error.code;
console.log(errorMsg);
} );