max() option with WixDataAggregate()

Ask the community
,
1

Hello, is there a way to use the WixDataAggregate() function with the max() option, so that all the other fields of the found rows are displayed in a table?

Example, I have a “myHouse” table and I want to aggregate the following records by “state” field and find the maximum on the “age” field:

Table: myHouse
name ; age ; state

“Claudio”;“34”,“Italy”
“Mario”;“56”,“Italy”
“Claudio”;“37”,“Italy”
“Anna”;“14”,“France”
“John”;“62”,“England”
“Mary”;“65”,“England”

The final result I would like in the table is:

“Mario”;“56”,“Italy”
“Anna”;“14”,“France”
“Mary”;“65”,“England”

Instead using wixData.aggregate (“myHouse”). group (“state”). max (“age”)…etc
the result is only:

“Italy”
“France”
“England”

and I don’t get the other information

Thank you very much
Claudio

2

From what you’ve provided, you may have encountered a bug as you should get back the full items according to the documentation. Compare what you have done with the information in this article:
https://support.wix.com/en/article/velo-working-with-aggregations-in-the-data-api

1582×908 133 KB

If you believe your implementation is correct, you may open a ticket with customer care so they can investigate https://support.wix.com/en/article/contacting-wix-customer-care-for-support

3

Thank you Amanda, I read the article you suggested and also in the example shown the information of the " city " is not displayed.
The fields that are displayed, in the case of a max() , are only " population " and " state ".
My question is: Is there a way to get the " city " in the result, with the max() of the population and the group() of the " state "?

For convenience, the details of the example table and the result I would like to get out:

City,          Population, State,  Year
Buffalo,       292000,     NY,     2000
Buffalo,       261000,     NY,     2010
Los Angeles,   3703000,    CA,     2000
Los Angeles,   3796000,    CA,     2010
Miami,         362000,     FL,     2000
Miami,         401000,     FL,     2010
New York,      8015000,    NY,     2000
New York,      8192000,    NY,     2010
Orlando,       195000,     FL,     2000
Orlando,       240000,     FL,     2010
San Diego      1228000,    CA,     2000      
San Diego      1306000,    CA,     2010
San Francisco, 777000,     CA,     2000
San Francisco, 805000,     CA,     2010

wixData.aggregate("PopulationData")
.group("state")
.max("population")
.run()
.then( (results) => {let items = results.items;}); 
/* 
items is (I wish it was): 
{"_id": "FL", "populationMax": 401000, "city": 'Miami},
{"_id": "CA", "populationMax": 3796000, "city": 'Los Angeles},
{"_id": "NY", "populationMax": 8192000, "city": 'New York}
*/

Thanks
Claudio

4

Apologies, I missed that detail.

Edit (again - sorry):

In the article, to get both state and city you need 2 groups

See group max(multiple fields) https://support.wix.com/en/article/velo-working-with-aggregations-in-the-data-api

wixData.aggregate("PopulationData")   
.group("state", "year") 
.max("population")   
.run()  
.then( (results) => { 
let items = results.items; 
  } );
5

In the example above they are using state and year (instead of city like you asked) but the concept is the same. You need 2 groups if you want both returned.

6

Hi Amanda, thanks always for the support.
If I put two groups I radically change the result because it changes the key of aggregation.
if grouped by two fields (" state " and " year ") I get as a result all the rows of the table, because for key " state “/” year " it shows me all the rows, you agree?

It’s not exactly what I want.

The result I would like is the following, and that is to add to the aggregation key (" state ") the information of the " city " (in this example)

wixData.aggregate("PopulationData")
.group("state")
.max("population")
.run()
.then((results)=>
{let items = results.items;});
/*  items is (I wish it was):  
{"_id": "FL", "populationMax": 401000, "city": 'Miami'}, {"_id": "CA", "populationMax": 3796000, "city": 'Los Angeles'}, 
{"_id": "NY", "populationMax": 8192000, "city": 'New York'} 
*/

Thanks and have a nice day
Claudio

7

I don’t think what you want is possible given this methodology because the code would not know which state to place there given that snippet of code. You are sort of asking it to do 2 separate operations and combine them
groupBy “State” with an aggregate of “population”
AND
SELECT city with highest population by State.

8

Exactly. I have to implement two separate operations, I thought to do it with the aggregate() , but it’s not possible.

Thanks anyway for the support, you were very kind.

9

I’m asking someone on the data team to make sure I am correct here, but I was searching (outside of Wix) simply the ability to perform some kind of subquery like this within an aggregate function and I only found mixed results.

I think you will have to perform the queries separately, but I know this will be a more costly operation. If you don’t need to perform these queries all the time, could consider caching or storing the result and updating on a schedule.

I will let you know if I think of anything more elegant.

10

@c-dambrosio67 okay confirmed - what I have suggested will be your best possible route (performing the queries separately, then combining for your needs)