Database find MAX value

Ask the community
1

Hi,

here what I want to do :

I have a collection with some results of a sport competition. Some do 4 competitions a year, some do just one. So, my collection (colCompetitions) has those fields :
1- Index
2- CompetitorName
3- Result
4- Category
5- Season

So I want a form, that will be showing the best result of a competitor in a Specific Category in a Specific Season.

The collection could look like this :

1- Ariel - 5 - Champ - 2018-2019
2- Jasmine - 4.5 - Champ - 2018-2019
3- Tiana - 6 - Champ - 2018-2019
4- Ariel - 5.5 - Champ - 2018-2019
5- Pocahontas - 3 - Champ - 2018-2019
6- Jasmine - 4 - Champ - 2018-2019
7- Jasmine - 5 - Champ - 2017-2018
8- Ariel - 4 - Diamond - 2018-2019
9- Pocahontas - 4.2 - Champ - 2018-2019
10- Ariel - 5.4 - Champ - 2018-2019

So the result that I want in a list would be (With those filter = Champ, 2018-2019, sorted by Result):
1- Tiana - 6
2- Ariel - 5.5
3- Pocahontas - 4.2
4- Jasmine - 4

I think in SQL it would be a function called MAX. But can’t find this in wix java. I have some notion in coding, but like 20 years ago :), and did it only at school. I want it efficient, as the collection could be over 4K lines per season. I’ll see how it goes, but I’ll maybe seperate in season if its too big and too slow. But for the moment, if I can figure this code and if it is simple to do, would be apreciated.

2

Are you looking for the wix-data max function ?

3

Yes I think that’s it… I don’t know why I didn’t find it before :slight_smile: Thank you very much.

I’ll have a try and if need more information, I’ll get back to this thread :).

Thank you again!

4

Hi again,

ok that is working, but the problem I can’t get the result in a simple table ?

Let take for example the code in the MAX example :

import wixData from 'wix-data';

// ...

wixData.aggregate("PopulationData")
  .group("state", "year")
  .max("population")
  .run()
  .then( (results) => {
    let items = results.items;        // see below
    let numItems = results.length;    // 6
    let hasNext = results.hasNext();  // false
  } )
  .catch( (error) => {
    let errorMsg = error.message;
    let code = error.code;
  } );

/* Given the sample data above, items is:
 * [
 *   {
 *     "_id": {"state": "NY", "year": 2000},
 *     "populationMax": 8015000,
 *     "state": "NY",
 *     "year": 2000
 *   },
 *   {
 *     "_id": {"state": "FL", "year": 2000},
 *     "populationMax": 362000,
 *     "state": "FL",
 *     "year": 2000
 *   },
 *   {
 *     "_id": {"state": "CA", "year": 2000},
 *     "populationMax": 3703000,
 *     "state": "CA",
 *     "year": 2000
 *   },
 *   {
 *     "_id": {"state": "FL", "year": 2010},
 *     "populationMax": 401000,
 *     "state": "FL",
 *     "year": 2010
 *   },
 *   {
 *     "_id": {"state": "CA", "year": 2010},
 *     "populationMax": 3796000,
 *     "state": "CA",
 *     "year": 2010
 *   },
 *   {
 *     "_id": {"state": "NY", "year": 2010},
 *     "populationMax": 8192000,
 *     "state": "NY",
 *     "year": 2010
 *   }
 * ]
 */

If I do that
$w(‘#table1’).rows = results.items;
Why it won’t simply paste all the result in the table ?

Sorry, I am missing something I think. I tried to create table with something like this as well :

$w(“#table1”).columns = [
{
“id”: “col1”,
“dataPath”: “name”,
“label”: “Name”,
“type”: “string”,
}, {
“id”: “col2”,
“dataPath”: “score”,
“label”: “Score”,
“type”: “string”,
}];

But I have 18 records in the result, in

console.log(results.items);

But the table is just giving me 18 blanks.

Thank you for helping me.

5

Ok found it :slight_smile:

I have to create the table with the field names. So if it is doing a Max… the field name will be fieldMax. So I create the table and it worked.