Querying a database then removing the results from a dropdown.

Ask the community
,
1

Hi all,

I have a date picker on my site. When a user selects a date, I want to query a database to search for all submissions that were made with that date selected, then return an array that populates a time selector with every time but the ones already taken in the database.

The available times (default array) looks like this:

const defaultTimes =
[{“label”: “11am - 12pm”, “value”: “11am - 12pm”},
{“label”: “12pm - 1pm”, “value”: “12pm - 1pm”},
{“label”: “1pm - 2pm”, “value”: “1pm - 2pm”},
{“label”: “2pm - 3pm”, “value”: “2pm - 3pm”},
{“label”: “3pm - 4pm”, “value”: “3pm - 4pm”},
{“label”: “4pm - 5pm”, “value”: “4pm - 5pm”},
{“label”: “5pm - 6pm”, “value”: “5pm - 6pm”},
{“label”: “6pm - 7pm”, “value”: “6pm - 7pm”},
{“label”: “7pm - 8pm”, “value”: “7pm - 8pm”},
{“label”: “8pm - 9pm”, “value”: “8pm - 9pm”}];

The query looks like this:

wixData.query(“MyDatabase”)
.eq(“MyDatabase”, searchDate)
.find()
.then( (results) => {
if (results.length === 0) {

        $w('#timeInput').options = defaultTimes; 
        console.log("default"); 

    }

if (results.length > 0) {
let items = results.items;
let item = items[0];
let time = item.deliveryTime;
console.log(time);

    }

A couple of issues I need help with:

  1. How can I make the results return all of the query results for the column in my dataset labelled “deliveryTime”? Currently it will only return the first result.
  2. How can I then remove the returned times (“time”) from the “defaultTimes” array so that every default time is displayed but the ones returned by the query (the “taken” ones)?

Any help would be great!

Thanks
Anney

2

Hi Anney,

Javascript arrays are zero-based. In other words, the first item in the array is items[0], the second is items[1], the third is items[2] and so on. You could use a for loop to access all of those individual deliveryTime values.

You can’t change the defaultTimes array because if it’s a constant, but you don’t really need to. Store in an array the state of the options (array) of the timeInput element, find the item to remove and remove from the created array (Options), and then use the created array to re-set the options of timeInput.

export function RemoveTime(TimeToRemove){
 let Options = $w('#timeInput').options;
 let ItemLabel = "";
 for (var i = 0; i < Options.length; i++) {
        ItemLabel = Options[i].label;
        if (ItemLabel === TimeToRemove) {
            Options.splice(i, 1);
        }
    }
    $w('#timeInput').options = Options;
}
3

Hi Anthony,

Thank you! Just a quick question, how do I use to call on page load and have the database query?

Thanks,
Anney

4

Hi Anthony,

I got it to partially work, but it still only removes one item. Any way to make the results.items return all the results in the deliveryTime column?

Thanks,
Anney

5

@anneynorton7
https://www.wix.com/corvid/reference/$w.html#onReady

It should be returning all of the records for that match searchDate, but it’s in the form of an array. Insert a console.log(results) line, so you are not guessing about what is being returned:

.then( (results) => { 
  console.log(results);

In the console, you should see a three-dot button. Keep expanding the “nodes” to see how it is constructed. All of the numbered items are the various records (rows) in the array returned by the query.

336×611 4.72 KB

6

@tony-brunsman Hi Anthony,

I see that. The only issue is it is returning every single item, not limiting it to the deliveryTime column. So, when I call the RemoveTime function as:
RemoveTime(results)
it doesn’t remove anything from the dropdown.
I tried changing:

for ( var i = 0; i < Options.length; i++) {
ItemLabel = Options[i].label;
if (ItemLabel === TimeToRemove) {
Options.splice(i, 1);
}
}

to:

for ( var i = 0; i < Options.length; i++) {
ItemLabel = Options[i]. deliveryTime ;
if (ItemLabel === TimeToRemove) {
Options.splice(i, 1);
}
}

but it still doesn’t remove the items.

The only way it does is if I specify:

let time = results.items[0];
RemoveItem (time);

That works, but it will only remove the one entry not all the times that are connected with the searchDate .

Maybe I’m calling the function wrong?

7

@anneynorton7

It would be helpful if you could post a screen shot of the collection that you are using. Is deliveryTime a DateTime field?

8

@tony-brunsman deliveryTime is a text field

9

@tony-brunsman
maybe the issue is the calling the function?

I have the function as:

export function RemoveTime(TimeToRemove){
let Options = $w(‘#timeInput’).options;
let ItemLabel = “”;
for ( var i = 0; i < Options.length; i++) {
ItemLabel = Options[i]._deliveryTime;
if (ItemLabel === TimeToRemove) {
Options.splice(i, 1);
}
}
$w(‘#timeInput’).options = Options;
}

and I’m calling it as:

$w.onReady( function () {

$w(‘#dateInput’).value = date;

let searchDate = date.toLocaleDateString(“en-US”, options);

wixData.query(“Orders”)
.eq(“deliveryDate”, searchDate)
.find()
.then( (results) => {
if (results.length === 0) {

        $w('#timeInput').options = defaultTimes; 
        console.log("default"); 

    }  **else**  {

let items = results.items;
RemoveTime(items)
}

        console.log(results); 

});

});