Get unique-values of wished DB-fields (promiseAll)?

Ask the community
1

Hello, it’s not often that i write here, but it seems i have a problem, which makes me crazy!

What i am talking about?
I am trying to generate a specific code to get UNIQUE-DATA out of DATABASE using Wix-Data.

It works!

async function create_UniqueDropdown(items, DBFIELDS, dropdown) {console.log(items)
  const uniqueTitles = await getUniqueTitles(items); console.log("Unique-Titles: ", uniqueTitles);
  $w(`#${dropdown}`).options = buildOptions(uniqueTitles); //console.log("OPTIONS: ", buildOptions(uniqueTitles));
  
  async function getUniqueTitles(items) {
    let titlesOnly = await items.map(item => item[DBFIELDS]); //console.log("Titles-Only: ", titlesOnly);
    return [...new Set(titlesOnly)];
  }

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

But my aim is to modify it the way, that the user can choose the exact DB-field to be unified and get all results of the selected field(s).

At least DBFIELDS must be an ARRAY, or even an OBJECT.
The shown code runs good for just one DB-Field, but would quickly get very slow when working for several DB-fields.

So my idea was to use promiseAll(), but this is exactly my issue, no matter what i tried, i could not get a satisfying result.

Maybe one of you have a good idea?

Aim-1: → eleminating all → asynchronity (async-await)
Aim2: —> modifiying this function into a multi-DB-FIELD one.

So the Input for DBFIELDS would be an ARRAY for example…
DBFIELDS = [“dbField1”, “dbField2”, “dbField3”, …]

And as result would be expected all UNIQUE-ITEMS for each of the DB-FIELDS → compressed into an ARRAY or Object.

2

My first thought is not running a query each time for this and rather once you pull the data, allowing the user to filter on the data you have already queried during their session. Would something like that potentially work for your use case?

3

Also, something else to try potentially that could speed up your queries is adding a few index values to commonly searched columns, still thinking. Let me know what you have come up with

4

Yes this sounds good.

My first thought is not running a query each time…

Yes, it makes sense to reduce my query-calls.

So, i should use a filter-method, as soon as i got my results from query and then work with the results, thats right?

Also, something else to try potentially that could speed up your queries is adding a few index values to commonly searched columns

This one i did not understand completely.
Do you have an example for it?

5

For the first question, yes - I was thinking perhaps one query then allowing users to filter rather than trigger a new query each time would be faster.

For indexes, I’m workign on a video for that now but it will be based on this session that Justas created. Just after 18min he talks about indexes but you may find the entire session interesting

Support Article on Data Indexing

6

Tank you very much. I will surely take a closer look onto it. :blossom:

7

Before I answer, do you need the unicity to be on all fields combined or unique within each field?

In other words is an item unique if valueField1 or valueField2 is unique or if the combination of valueField1+valueField2 is unique?

If it’s the latter you can do something like

function getUniqueItemsForField(items, fields){
 const itemsByKey = {}

 items.map(item => {
  const key = fields.reduce((compoundKey, field) => compoundKey+field+":"+item[field]+"-", "");  
  
  if(!itemsByKey[key]) itemsByKey[key] = item
 })
 
 return Object.values(itemsByKey)
}
8

Thanks Quentin, i will reply later, when i have found my solution.
Thanks for your suggestion.

Before I answer, do you need the unicity to be on all fields combined or unique within each field?

And yes unique within each field (finding all unique items/values).

How it works?

I add a DB-Field-ID into my query, and get automatically unique results.
This already works.
Next step is to do it multiple times as much as you want.

  1. You do a query.
  2. You get query results including already all UNIQUE-VALUES for all of your (in the query) includet iniqueValue-parameters.

Query-example: (already working)…

dataQuery.database = DATABASE;
dataQuery.mode = "auto";
dataQuery.uniqueValues = { uniqueField:[DBFIELDS[2]] }

Query-example: (wished function)…

dataQuery.database = DATABASE;
dataQuery.mode = "auto";
dataQuery.uniqueValues0 = {uniqueField:[DBFIELDS[1]]}
dataQuery.uniqueValues1 = {uniqueField:[DBFIELDS[2]]}
dataQuery.uniqueValues2 = {uniqueField:[DBFIELDS[3]]}

Wished RESULT: ARRAY or OBJECT…

res.uniqueValues[0] = UNIQUE-VALUES of first selected/included DB-Field

res.uniqueValues[1] = UNIQUE-VALUES of second selected/included DB-Field

res.uniqueValues[2] = UNIQUE-VALUES of third selected/included DB-Field

I think i am already on the right track, will take some time…to be continued…

9

And yes, by the way, my wished function was possible to be generated.
Tanks for help everyone, who feeded me with ideas. :hugs: