Find item in collection and return entire row

Ask the community
,
1

Hi,

I have the following problem:

I have a collection called “locationAvailability” with dates and a number of different identifier (here in this example 3, but the code should be flexible enough to run for any number of columns.

                                 ID000000001     ID000000002   ID000000003

01/01/2020 1 0 1
02/01/2020 0 1 0
03/01/2020 1 1 1

Based on the date selected by the user, I want to return the ID for which there is a “1” in the cells

I tried the following code, however I didn’t manage to solve it. Here is the code I am using

var myDate = $w(“#datePicker1”).value;
var date = myDate.getDate();
var month = myDate.getMonth() + 1;
var year = myDate.getFullYear();
var dateStr = date + “/” + month + “/” + year;

wixData.query(“locationAvailability”)
.eq(“availability”, dateStr)
.find()
.then( (results) => {
console.log(results.items[0]);
} );

Let’s say the user select 02/01/2020, then it should return “ID000000002”

2

Let’s say the column with the date is the “availability” field (if it’s another field change the code in accordance):

import wixData from 'wix-data';
//..other code...
//inside the input onChange event handler retrieve the input value then:
wixData.query("locationAvailability")
.eq("availability", dateStr) 
.find()
.then( (results) => { 
    let items = results.items;
    if(items.length > 0){
    let item = items[0];
    let dateIDs = Object.keys(item);
    dateIDs = dateIDs.filter(e => item[e] === 1);
    console.log(dateIDs);//an array with the relevant dateIDs
    }
} );

FIXED

3

This is the code if you want to get the date identifier (as you said at the end of your question). But if you want to return the entire row, as you stated in the post title, then it’s a little bit different.

4

Thanks a lot, this works fine.

I have one last thing where I am struggling. Based on the array of relevant IDs, I want to filter a table (dataset1 below). If I enter manually an ID, it filters it. However if I pass the array, the filter does not work. Any idea what I am missing?

Thanks a lot for the help!

export function datePicker1_change(event) {

var myDate = $w(“#datePicker1”).value;
var date = myDate.getDate();
var month = myDate.getMonth() + 1;
var year = myDate.getFullYear();
var dateStr = date + “/” + month + “/” + year;

wixData.query(“locationAvailability”)
.eq(“availability”, dateStr)
.find()
.then( (results) => {
let items = results.items;
if (items.length > 0){
let item = items[0];
let dateIDs = Object.keys(item);
dateIDs = dateIDs.filter(e => item[e] === 1);
console.log(dateIDs);//an array with the relevant dateIDs

$w(“#dataset1”).setFilter(wixData.filter()
.contains(“identifier”, dateIDs));

}
} );
}

5

instead of .contains , use .hasSome

6

doesn’t work unfortunately. The console.log works, the array has the required elements in, but instead of showing the element in the table, the table is blank. Any idea why that could be?

7

It should work with hasSome(). Can post your code and a screenshot of the values in the collection?

8

Sure, here is the code

export function datePicker1_change(event) {

var myDate = $w(“#datePicker1”).value;
var date = myDate.getDate();
var month = myDate.getMonth() + 1;
var year = myDate.getFullYear();
var dateStr = date + “/” + month + “/” + year;
console.log (dateStr)
// find all the locations that are available and save them in an array
wixData.query(“locationAvailability”)
.eq(“availability”, dateStr)
.find()
.then( (results) => {
let items = results.items;
if (items.length > 0){
let item = items[0];
let dateIDs = Object.keys(item);
dateIDs = dateIDs.filter(e => item[e] === 1);
console.log(dateIDs);//an array with the relevant dateIDs

//$w(“#dataset1”).setFilter(wixData.filter()
//.contains(“identifier”, “ID00000001”));
$w(“#dataset1”).setFilter(wixData.filter()
.hasSome(“identifier”, dateIDs));

}
}
)
}

this part here works:
//$w(“#dataset1”).setFilter(wixData.filter() //.contains(“identifier”, “ID00000001”));
→ If I manually define ID00000001 is the relevant one, only this one shows up. But for the code above, the table is blank.

9

I meant a screenshot of the database

10

This one is the database where the ffilter is applied. This is also a table shown on the website with about the same categories, only the identifier is not part of the table.

This is the database where the relevant identifiers are taken from (based on 0 and 1).

11

OK. Now I see the problem. in one collection you used id (in lower case) while in the other one you used ID (upper case). Change it from ID to id.

12

Or if you want, change the query results:

dateIDs = dateIDs.filter(e => item[e] === 1);
dateIDs = dateIDs.map(e => e.toUpperCase());
//then filter
13

I am a bit confused. You are right, the elements in the array are id instead of ID, however in the collection both are with capital letters. How come this is changed in the query?

I tried the query, however it is not working

export function datePicker1_change(event) {

var myDate = $w(“#datePicker1”).value;
var date = myDate.getDate();
var month = myDate.getMonth() + 1;
var year = myDate.getFullYear();
var dateStr = date + “/” + month + “/” + year;
console.log (dateStr)
// find all the locations that are available and save them in an array
wixData.query(“locationAvailability”)
.eq(“availability”, dateStr)
.find()
.then( (results) => {
let items = results.items;
if (items.length > 0){
let item = items[0];
let dateIDs = Object.keys(item);
dateIDs = dateIDs.filter(e => item[e] === 1);
dateIDs = dateIDs.map(e => e.toUpperCase);
console.log(dateIDs);//an array with the relevant dateIDs

//$w(“#dataset1”).setFilter(wixData.filter()
//.contains(“identifier”, “ID00000001”));
$w(“#dataset1”).setFilter(wixData.filter()
.hasSome(“identifier”, dateIDs));

}
}
)

I get the following:

And the filter is not applied. Any idea what is going on here?

14

In the collection there’s field name and field key. The field key always starts with a lower case letter no matter what. The query retrieves the field key only and that’s the reason.
Add () like this:
dateIDs = dateIDs.map(e => e.toUpperCase () );

15

that works!

thanks a lot, highly appreciated!!

16

You’re welcome :slight_smile: