Example: Remove duplicates from connected dropdown options using distinct() query

Show and tell
,
1

740×435 299 KB

Demonstrates

Links for this example

About this example
This example is a variation of the original Search a Database example. Instead of getting dropdown options from a table or dedicated collection of options, the options are retrieved from a regular data collection using the distinct() function of the query which removes the duplicates from the results. The results are then converted to objects and assigned to the dropdown list.

When a query uses the distinct() function, only the unique values are retrieved. Like the query’s find() function, distinct() runs the query and returns a Promise of the result. However, unlike find() which returns collection items as objects, distinct() returns a result with the distinct (no duplicates) field values.

Note: when using distinct() , there is no need for find() . In fact, using find() with distinct() will cause an error.

7 Likes
2

I hate it when I’m stupid. In my zeal to create “lean” code, I left out the “All continents” option in the dropdown.

The code has been fixed. sigh

3

Hello
I do not understand “loadContinents” to adapt it to my database
thank you in advance

4

Yisrael

As it would be if instead of using hasSome() we use ne() , I’m trying to make it work but I don’t know why it doesn’t work.

Your example:
newFilter = newFilter. hasSome (‘continent’, continents);

Change to:
newFilter = newFilter. ne (‘continent’, continents);

5

Hi, this example should be great for my website and works beautifully the majority of the time. However intermittently and seemingly randomly (despite extensive testing, no pattern is emerging), I get the default settings for the drop down filters - “strawberry”, “chocolate” etc. - appearing in one or more of the drop down filters (it often changes which filter or filters are affected), rather than the drop down options imported from the database. I have several pages, each using this code (adapted for each page) and displaying a different database, and using up to 10 drop down filters and this problem occurs on all of them, to a lesser or greater degree. If you can help, I would be very grateful. This is my code for one of the pages…

import wixData from “wix-data”;

$w.onReady( function () {
$w(‘#iTitle’)
$w(‘#iCounty’)
$w(‘#iGeog’)
$w(‘#dataset1’)
$w(‘#text102’)
});

let lastFilterTitle;
let lastFilterCounty;
let lastFilterGeog;

export function iTitle_change(event, $w) {
filter($w(‘#iTitle’).value, lastFilterCounty, lastFilterGeog);
}

export function iCounty_change(event, $w) {
filter(lastFilterTitle, $w(‘#iCounty’).value, lastFilterGeog);
}

export function iGeog_change(event, $w) {
filter(lastFilterTitle, lastFilterCounty, $w(‘#iGeog’).value);
}

function filter(title, county, geog) {
if (lastFilterTitle !== title || lastFilterCounty !== county || lastFilterGeog !== geog) {
let newFilter = wixData.filter();
if (title)
newFilter = newFilter.eq(‘title’, title);
if (county)
newFilter = newFilter.eq(‘county’, county);
if (geog)
newFilter = newFilter.eq(‘geog’, geog);
$w(‘#dataset1’).setFilter(newFilter)
.then(() => {
if ($w(‘#dataset1’).getTotalCount() ===0) {
$w(‘#text102’).show();
}
else {
$w(‘#text102’).hide();
}
})
. catch ((err) => {
console.log(err);
});
lastFilterTitle = title;
lastFilterCounty = county;
lastFilterGeog = geog;
}
}

// Run a query that returns all the items in the collection
wixData.query(“Psychologists”)
// Get the max possible results from the query
.limit(1000)
.ascending(“title”)
.distinct(“title”)
.then(results => {
let distinctList = buildOptions(results.items);
// unshift() is like push(), but it prepends an item at the beginning of an array
distinctList.unshift({ “value”: ‘’, “label”: ‘All Psychologists’});
//Call the function that builds the options list from the unique titles
$w(“#iTitle”).options = distinctList
});

function buildOptions(items) {
return items.map(curr => {
//Use the map method to build the options list in the format {label:uniqueTitle, valueuniqueTitle}
return { label: curr, value: curr };
})
}

// Run a query that returns all the items in the collection
wixData.query(“Psychologists”)
// Get the max possible results from the query
.limit(1000)
.ascending(“county”)
.distinct(“county”)
.then(results => {
let distinctList = buildOptions(results.items);
// unshift() is like push(), but it prepends an item at the beginning of an array
distinctList.unshift({ “value”: ‘’, “label”: ‘All Counties’});
//Call the function that builds the options list from the unique titles
$w(“#iCounty”).options = distinctList
});

function buildOptions1(items) {
return items.map(curr => {
//Use the map method to build the options list in the format {label:uniqueTitle1, valueuniqueTitle1}
return { label: curr, value: curr };
})
}

// Run a query that returns all the items in the collection
wixData.query(“Psychologists”)
// Get the max possible results from the query
.limit(1000)
.ascending(“geog”)
.distinct(“geog”)
.then(results => {
let distinctList = buildOptions(results.items);
// unshift() is like push(), but it prepends an item at the beginning of an array
distinctList.unshift({ “value”: ‘’, “label”: ‘All Regions’});
//Call the function that builds the options list from the unique titles
$w(“#iGeog”).options = distinctList
});

function buildOptions2(items) {
return items.map(curr => {
//Use the map method to build the options list in the format {label:uniqueTitle2, valueuniqueTitle2}
return { label: curr, value: curr };
})
}

export function button45_click(event, $w) {
//Add your code for this event here:
filter($w(‘#iTitle’).value=‘’, $w(‘#iCounty’).value=‘’, $w(‘#iGeog’).value=‘’);
}

6

Thank you so much for this - it’s worked perfectly as far as creating the drop down and eliminating duplicates goes. I do have a problem though. When I click on an option to select it, nothing happens. I feel like I’m missing something obvious, but I can’t figure it out.

The page is www.fineartprintfair.org/print-week
I’ve pasted my code below - I hope someone can help!

import wixData from ‘wix-data’;

$w.onReady( function () {
// Run a query that returns all the items in the collection
wixData.query(“PrintWeek”)
// Get the max possible results from the query
.limit(1000)
.find()
.then(results => {
// Call the function that creates a list of unique titles
const uniqueTitles = getUniqueTitles(results.items);
// Call the function that builds the options list from the unique titles
$w(“#dropdown1”).options = buildOptions(uniqueTitles);
});
// Builds an array from the “Title” field only from each item in
// the collection and then removes the duplicates
function getUniqueTitles(items) {
// Use the map method to create the titlesOnly object containing all the titles from the query results
const titlesOnly = items.map(item => item.date);
// Return an array with a list of unique titles
return [… new Set(titlesOnly)];
}
// Creates an array of objects in the form {label: “label”, value: “value”} from the array of titles
function buildOptions(uniqueList) {
return uniqueList.map(curr => {
// Use the map method to build the options list in the format {label:uniqueTitle, value:uniqueTitle}
return {label:curr, value:curr};
});
}
});

7

This example is really helpful, thanks Yisrael. How can it be modified to incorporate a filtered dataset? My dataset is filtered and when the “All Continent” option is selected, all the database collection items are displayed in the repeater, rather than only the filtered items.

8

Little remark
When querying a collection using distinct() on a field that has a reference to another collection, only gives the _id’s of the referenced collection and not the labels (primary key) of the referenced collection.