Return most frequent value from a Query

Ask the community
,
1

Hey everyone. I’m pretty new to Corvid and can use some help with a program I’m trying to put together.

I have a database that I’ve set up with text values assigned to different columns. I’m trying to find some way to write a wixData.query command that can tell me the most common value in a particular column.

For example, in a column of pets, I might have the following entries:

dog
cat
bird
cat
snake
cat
dog

For this instance, I’d need the query to return the value “cat.”

Is there any way to do this?

2

Jack, you could use wixDataAggregate like the following. The having statement is only necessary if it’s possible that the pets field could have no value in some of the records. Doing a descending sort will place the one with most common value (“cat” in your example) in the first place in the returned array.

let having = wixData.filter().ne("_id",null); 
wixData.aggregate("collectionName")
  .group("pets")
  .count()
  .having(having)
  .descending("count")
  .run()
  .then( (results) => {
     if (results.items.length > 0) {
         console.log(results.items);
         console.log(results.items[0].pets);
      }
   });
3

That works perfectly. Thank you so much!