Use WixData.Query to Get Data from multiple Datasets

Ask the community
, , ,
1

Hi all,
I am new to JS and have been trying to pick up as much as I can. I am getting somewhere, but I just can’t get the coding right for an issue I have.

I have some input fields that query a dataset and produce the results in a table, it works fine at the moment but it is not doing everything I need it to do. At the moment, all of my data displayed and queried comes from one dataset, but I need to query another dataset for the criteria and displayed data,

At the moment I query ‘Date’ and ‘No. of Nights’ which is stored in dataset ‘CaravanPricing’, but I also want to query other elements that are stored in a different dataset (‘Caravan’) which is linked by a reference field in CaravanPricing.
Criteria such as ‘Caravan Grade’, ‘No. of Guests’, ‘Pet Friendly’ and ‘Wheelchair Accessible’ all come from the ‘Caravan’ Dataset, and I need to link that dataset via the ID.

I then also want to use data from the ‘Caravan’ daaset to display in the columns (just the ‘Caravan Name’, and ‘Picture’ for now, but maybe more at a later date).

I have tried all types of different things and can just not get it right.
Any help would be amazing, thanks

Current Code

import wixData from ‘wix-data’;
import wixLocation from ‘wix-location’;

function convert(value){
return value.toFixed(2).replace(/(\d)(?=(\d{3})+(?!\d))/g, “1,”);
}

function addDays(days,sdate) {
var date = sdate; // today
date.setDate(date.getDate() + days);
return date;
}

$w.onReady( function () {

$w("#Availability").columns = [ 
  {

“id”: “QCaravan”,
“dataPath”: “caravanNameQ”,
“label”: “Caravan”,
“width”: 350,
“visible”: true ,
“type”: “String”,
“linkPath”: “link-Booking-_id”
},
{
“id”: “SDate”, // ID of the column for code purposes
// The field key in the collection whose data this column displays
“dataPath”: “startDate1”,
“label”: “Start Date”, // The column header
“width”: 100, // Column width
“visible”: true , // Column visibility
“type”: “Date”, // Data type for the column
// Path for the column if it contains a link
“linkPath”: “link-Booking-_id”
},
{
“id”: “EDate”, // ID of the column for code purposes
// The field key in the collection whose data this column displays
“dataPath”: “endDate1”,
“label”: “End Date”, // The column header
“width”: 100, // Column width
“visible”: true , // Column visibility
“type”: “Date”, // Data type for the column
// Path for the column if it contains a link
“linkPath”: “link-Booking-_id”
},
{
“id”: “QNights”,
“dataPath”: “noOfNights”,
“label”: “No. of Nights”,
“width”: 120,
“visible”: true ,
“type”: “Number”,
“linkPath”: “link-Booking-_id”
},
{
“id”: “BookingPrice”,
“dataPath”: “price2”,
“label”: “Price”,
“width”: 120,
“visible”: true ,
“type”: “String”,
“linkPath”: “link-Booking-_id”
}//,
//{
// “id”: “QBerth”,
// “dataPath”: “CBerth”,
// “label”: “Berth”,
// “width”: 120,
// “visible”: true,
// “type”: “Number”,
// “linkPath”: “link-Booking-_id”
//}
];
});

export function CheckAvail(event,$w) {
wixData.query(‘CaravanPricing’)
.eq(‘startDate’, $w(‘#StartDate’).value)
//.lt(‘startDate’, $w(‘#StartDate’).value)
.eq(‘noOfNights’, Number($w(‘#Nights’).value))
.eq(‘status’, ‘Available’)

//let CaravanID = res.items[i].noOfNights;
//.and(
//wixData.query(‘Caravan’)
//.eq(‘ID’, ‘caravan’)
//.ge(‘caravanBerth’,Number($w(‘#Berth’).value))

//wixData.query(‘Caravan’)
//.eq(‘ID’,)

.find() // Run the query
.then(res => {
let i = 0;
if (res.length > 0) {

//let BPrice = convert(res.items[0].price);
//let BookingPrice = “£” + BPrice;

// Set the table data to be the results of the query
//let qyCaravan = res.items[0].caravan;
//let qyDate = res.items[0].startDate;
//let qyNights = res.items[0].noOfNights;
//let qyPrice = res.items[0].price;

//res.items[0].caravan = qCaravan;
//res.items[0].startDate = qDate;
//res.items[0].noOfNights = qNights;
//res.items[0].price = qPrice;
while (i < res.length) {

const options = {
day: “numeric”,
month: “short”,
year: “numeric”
};

let nightsS = res.items[i].noOfNights;
let startDate0 = res.items[i].startDate;
let startDate1 = res.items[i].startDate.toLocaleDateString(“en-US”,options);
//let startDate2 = startDate1.toLocalDateString;
//let endDate1 = startDate0 + nightsS;
//let endDate2 = endDate1.toLocaleDateString();
let price1 = res.items[i].price;
let price2 = “£” + convert(price1);
let endDate1 = startDate0;
let caravanID = res.items[i].caravanId;
//let CBerth = res.items[i].caravanBerth;

  res.items[i].caravanID = caravanID;  
  res.items[i].price2 = price2;  
  res.items[i].startDate1 = startDate1; 
  res.items[i].endDate1 = addDays(nightsS,startDate0).toLocaleDateString("en-US",options); 

wixData.query('Caravan')  
.eq('_id', 'caravanID')

let caravanNameQ = res.items[i].caravanName;

  res.items[i].caravanName = caravanNameQ;

//res.items[i].CBerth = CBerth;
i++;
}

  $w('#Availability').rows = res.items; 

  $w('#Availability').show();  
  $w('#NoMatches').hide();

} else {
//
// We didn’t get what was expected from the Data Collection!
//
console.log("Unexpected number of records retrieved from data collection - "+String(res.length));
$w(‘#Availability’).hide();
$w(‘#NoMatches’).show();
}

});
}