how to total collection results by user?

Ask the community
,
1

I have a collection of results that have multiple results per person and I would like the sum of all of the results per person. Can someone please assist with this? It appears that results1 shows empty. Once that is resolved, how do I make the foreach loop for each user instead of each item in the collection? Thanks in advance

This is what I have so far:

$w.onReady( function ()
{
wixData.query(“TxResults”)
.find()
.then( (results) => {
let resultCount = results.totalCount;
console.log(resultCount)
// get all of the items
results.getItems(0, resultCount)
.then((results1) => {
console.log(results1)
let sumTotal = 0; // declare sum
let items = results1.items;
items.forEach(item => {
// the amount is a string so convert
sumTotal = sumTotal + Number(item.totalpts);
});
$w(“#table1”).rows = items;
}). catch ((err) => {
console.log(err);
});
} ) ;
});

2

Hi,
Check out this article about creating a search for your site and displaying the results in a table.

Good luck :slight_smile:
Or

3

Thanks for the info Or! I understand what the article is saying, but I would like the totals of all results for each person, not a list of each person’s individual results. Is there a way you could assist in showing me how to do that? Thanks in advance

4

@poolshark314 You can use Aggregates. For more information, see the following:

5

@yisrael-wix Thanks. I tried to put this code together but I am still not getting the correct results. Here is my current code in OnReady. numItems is only returning 1 item so I think it is summing the totalpts column as one. I would like the totalpts (in the TxResults collection) column per reference field member (the title field of the Members collection). Could you please help?

const filter = wixData.filter().startsWith(“title”, “18-”);

wixData.aggregate("TxResults") 
    .filter(filter) 
    .group("member") 
    .sum('totalpts') 
    .descending("totalpts") 
    .run() 
    .then( (tresults) => {

let items = tresults.items;
let numItems = tresults.length;
let hasNext = tresults.hasNext();
console.log(numItems)
console.log(hasNext)
console.log(items)
$w(‘#dataset1’).setFilter(wixData.filter(items));
} )
. catch ( (error) => {
let errorMsg = error.message;
let code = error.code;
} );

6

@yisrael-wix I think I made progress, but I can’t seem to get the results into the table. When I check items , the values look promising. However, when I check aggregated , it shows as null. Here is what I have so far:

$w(“#table1”).columns = [
{
“id”: “col1”,
“dataPath”: “member”,
“label”: “Member”,
“type”: “string”
},
{
“id”: “col2”,
“dataPath”: “totalpts”,
“label”: “Total Points”,
“visible”: true ,
“type”: “number”
}
];

const filter = wixData.filter().startsWith(“title”, “18-”);

wixData.aggregate("TxResults") 
    .filter(filter) 
    .sum('totalpts','Total Pts') 
    .sum('caught','Caught') 
    .sum('alive','Alive') 
    .sum('totalLBs','Total LBs') 
    .sum('totalOz','Total Oz') 
    .group("member") 
    .descending("totalpts") 
    .run() 
    .then((results) => {

let items = results.items;
let numItems = results.length;
let hasNext = results.hasNext();
console.log(numItems)
console.log(hasNext)
console.log(items)
const members = results.items.map(x => x.member)
.filter((obj, index, self) => index === self.indexOf(obj));
const aggregated = members.map(x => {
return {
member: x,
totalpts: results.items.filter(obj => obj.member === x)
.map(z => z.totalpts)
.reduce((sum, current) => sum + current)
}})

    console.log(aggregated) 

    . **catch** ( (error) => {

let errorMsg = error.message;
let code = error.code;
} );

    $w("#table1").rows = aggregated;
7

@yisrael-wix For some reason my aggregated shows as null. Any thoughts?

8

@poolshark314 Since the results are returned in the Promise’s .then() function, you need to set the table rows inside the .then(). As you can see in the following lines of code, I moved it to right after the console.log() statement:

console.log(aggregated);
$w("#table1").rows = aggregated;

To get a better understanding of how Promises work, refer to the following articles:

  • Promises, Promises

  • Working with Promises in Wix Code

9

@yisrael-wix I took your suggestion and moved it up, but the results are still null. They are null in the console.log as well, but Items is populated. Here is what I have now:

$w.onReady( function ()
{
// Gets today’s date
const today = new Date();
// Get the current year
const year = Number(today.getFullYear());
//console.log(year)
// set the year on the text
$w(‘#yeartext’).text = String(year);

$w(“#table1”).columns = [
{
“id”: “col1”,
“dataPath”: “member”,
“label”: “Member”,
“type”: “string”
},
{
“id”: “col2”,
“dataPath”: “caught”,
“label”: “Caught”,
“type”: “number”
},
{
“id”: “col3”,
“dataPath”: “alive”,
“label”: “Alive”,
“type”: “number”
},
{
“id”: “col4”,
“dataPath”: “totalLBs”,
“label”: “Total LBs”,
“type”: “number”
},
{
“id”: “col5”,
“dataPath”: “totalOz”,
“label”: “Total Oz”,
“type”: “number”
},
{
“id”: “col6”,
“dataPath”: “totalpts”,
“label”: “Total Points”,
“visible”: true ,
“type”: “number”
}
];

const filter = wixData.filter().startsWith(“title”, “18-”);

wixData.aggregate(“TxResults”)
.filter(filter)
.sum(‘totalpts’,‘Total Pts’)
.sum(‘caught’,‘Caught’)
.sum(‘alive’,‘Alive’)
.sum(‘totalLBs’,‘Total LBs’)
.sum(‘totalOz’,‘Total Oz’)
.group(“member”)
.descending(“totalpts”)
.run()
.then((results) => {
let items = results.items;
let numItems = results.length;
let hasNext = results.hasNext();
console.log(numItems)
console.log(hasNext)
console.log(items)
const members = results.items.map(x => x.member)
.filter((obj, index, self) => index === self.indexOf(obj));
const aggregated = members.map(x => {
return {
member: x,
totalpts: results.items.filter(obj => obj.member === x)
.map(z => z.totalpts)
.reduce((sum, current) => sum + current)
}})

console.log(aggregated) 
$w("#table1").rows = aggregated;

})});

10

@yisrael-wix Do you see anything wrong with the code? My aggregated is still null

11

@poolshark314 You are using aggregated outside of the .then() function (the Promise’s fulfillment function) and the results have not yet returned.

I would suggest the following two articles: