Trouble getting distinct results (SOLVED)

I have a collection ‘tutorial_logs’ in which I am collecting a name and date. I want to load a repeater and display each name once then how many time that name appears in the collection. I have it mostly working. I have the number of times it appears loading correctly but I am still have trouble making the name only appear once. Distinct does not seem to work in this case.

export function logs_ready ( ) {
$w ( “#filter” ). onClick (( event ) => {
$w ( ‘#repeater’ ). onItemReady (( $item , itemData , index ) => {
const Query = itemData . name ;
wixData . query ( ‘tutorial_logs’ )
. lt ( ‘_createdDate’ , $w ( ‘#toDate’ ). value )
. gt ( ‘_createdDate’ , $w ( ‘#fromDate’ ). value )
. eq ( ‘name’ , Query )
. find ()
. then (( results ) => {
let numVotes = results . length + ’ Views’ ;
if ( results . length > 0 ) {
$item ( ‘#count’ ). text = numVotes ;
$item ( ‘#title’ ). text = Query ;
$w ( “#repeater” ). show ();
}
});

    }); 
}) 

}

@bill65358 This appears to be simple enough in terms of the data that you want to display that you could use an aggregate query to populate the repeater. You could put both functions in your onReady something like this:

$w.onReady(function () { 
    $w('#repeater').onItemReady(($item, itemData, index) => {
        $item('#count').text = itemData.count;
        $item('#title').text = itemData.name;     
    });
    
    $w("#filter").onClick((event) => {
        const from = $w('#fromDate').value;
        const to = $w('#toDate').value;
        const filter =  wixData.filter().between("_createdDate",from,to);
        wixData.aggregate('tutorial_logs')
            .filter(filter)
            .group("name")
            .count()
            .run()
            .then((results) => {
                console.log("results",results);
                $w("#repeater").data = results.items;
                $w("#repeater").show();
            });
    })
});

Be sure to check out the console log to inspect the array that the aggregate function returns. You may need to alter the code some. The aggregate function will return one record for each name as a result of the group function, and a count of the number of times that the name is in the collection.

For future reference, it’s not considered good practice to make multiple calls for data in repeater looping functions. It will cause the repeater to load a lot more slowly. Nor from a security standpoint, is it advisable to have queries in your page code but rather in backend functions. I’m doing it here for simplicity’s sake in order suggest a way to solve the the problem at hand.

@tony-brunsman Thanks for the reply. I tried your code as is and tried tweaking it a few ways. I either get the same list but with no counts or I get the same exact results I got with my own code. I did look at the console logs but I’m not really sure what to look for honestly.

The results console log, when the ellipsis is expanded, will show you the data in the form of an array of objects that the query returned. If you expand the items array, you will see all of the individual records returned by the query. This a screen shot of a recent one that I did:

@tony-brunsman Thanks again bud. I just can’t seem to figure out the syntax here.
I’ve changed it around a few times but nothing seems to work. Here is some of the console log.

This is my current code inside my onready function. Also, Should the repeater be connected to the collection? In my other code it had to not be connected.

$w ( "#filter" ). onClick (( event ) => { 

    $w ( "#filter" ). onClick (( event ) => { 
        **const from**  =  $w ( '#fromDate' ). value ; 
        const  to  =  $w ( '#toDate' ). value ; 
        const  filter  =  wixData . filter (). between ( "_createdDate" ,  from ,  to ); 
        wixData . aggregate ( 'tutorial_logs' ) 
            . filter ( filter ) 
            . group ( "name" ) 
            . count () 
            . run () 
            . then (( results ) => { 
                $w ( '#repeater' ). onItemReady (( $item ,  itemData ,  index ) => { 
                
                    $w ( "#repeater" ). data  =  results . items ; 
                    $w ( "#repeater" ). show (); 

                }); 
                console . log ( "results" ,  results ); 

            }); 
    }) 
})

@bill65358 It takes some time to understand how to best use the various repeater functions. In the case of onItemReady() , the key line in the documentation for me is the very first line: “Sets the function that runs when a new repeated item is created.” Having that in mind, I know that this function needs to be in place before the data is assigned to the repeater because it will need to run on every item of the repeater. You changed it from my suggested code above to assign the data in the onItemReady. You definitely don’t want to do that.

Also, be sure that the repeater is not connected to a dataset. You’re using the wixData aggregate function to populate the repeater, so there’s no need for a dataset.

@tony-brunsman

When I use your code exactly the way you have it and the repeater is not connected I just get a blank repeater.

[
{
“_id”: “The Training Hub”,
“count”: 1,
“name”: “The Training Hub”
},
{
“_id”: “Limit Volunteer Category Access”,
“count”: 1,
“name”: “Limit Volunteer Category Access”
},
{
“_id”: "Print an Event Roster ",
“count”: 1,
“name”: "Print an Event Roster "
},
{
“_id”: “The MSC Mobile App”,
“count”: 1,
“name”: “The MSC Mobile App”

},
{
“_id”: “Event Archiving”,
“count”: 1,
“name”: “Event Archiving”
},
{
“_id”: “Generate Individual Statistics”,
“count”: 1,
“name”: “Generate Individual Statistics”
},
{
“_id”: “MySeniorCenter and Zoom”,
“count”: 1,
“name”: “MySeniorCenter and Zoom”
},
{
“_id”: “Touch Screen Settings”,
“count”: 1,
“name”: “Touch Screen Settings”
},
{
“_id”: “Broadcasts”,
“count”: 4,
“name”: “Broadcasts”
},
{
“_id”: “Raffles in MySeniorCenter”,
“count”: 5,
“name”: “Raffles in MySeniorCenter”
},
{
“_id”: “The Signins List”,
“count”: 2,
“name”: “The Signins List”
},
{
“_id”: “Remote Check in with Mobile Scanners and Apps”,
“count”: 2,
“name”: “Remote Check in with Mobile Scanners and Apps”
},
{
“_id”: “Consolidate Events”,
“count”: 1,
“name”: “Consolidate Events”
},
{
“_id”: "Create Volunteer Remin

ders",
“count”: 1,
“name”: “Create Volunteer Reminders”
},
{
“_id”: “Outreach/Services Custom Fields”,
“count”: 2,
“name”: “Outreach/Services Custom Fields”
},
{
“_id”: “All About Voice Connect (Robo Calls)”,
“count”: 2,
“name”: “All About Voice Connect (Robo Calls)”
},
{
“_id”: “Primary Language & Check-in Station Translation”,
“count”: 1,
“name”: “Primary Language & Check-in Station Translation”
},

{
“_id”: “Using PayPal to pay in MyActiveCenter”,
“count”: 2,
“name”: “Using PayPal to pay in MyActiveCenter”
},
{
“_id”: “Schedule a Ride”,
“count”: 3,
“name”: “Schedule a Ride”
},
{
“_id”: “Create a Return Ride”,
“count”: 1,
“name”: “Create a Return Ride”
},
{
“_id”: “Add Passengers”,
“count”: 2,
“name”: “Add Passengers”
},
{
“_id”: "Birthday Li

st",
“count”: 2,
“name”: “Birthday List”
},
{
“_id”: “Manage Volunteer Hours”,
“count”: 2,
“name”: “Manage Volunteer Hours”
},
{
“_id”: “Calendar Settings”,
“count”: 6,
“name”: “Calendar Settings”
},
{
“_id”: “Meals on Wheels”,
“count”: 6,
“name”: “Meals on Wheels”
},
{
“_id”: “Extended Gender and Sexual Orientation lists”,
“count”: 1,
“name”: "Extended Ge

nder and Sexual Orientation lists"
},
{
“_id”: “Managing Mailing Lists and Newsletters”,
“count”: 1,
“name”: “Managing Mailing Lists and Newsletters”
},
{
“_id”: “Managing the Events Schedule”,
“count”: 2,
“name”: “Managing the Events Schedule”
},
{
“_id”: “Set up Volunteers Drivers and Instructors”,
“count”: 2,
“name”: “Set up Volunteers Drivers and Instructors”
},
{
“_id”: “Setting Up and Using MyActiveCenter”,
“count”: 5,
“name”: “Setting Up and Using MyActiveCenter”
},

{
“_id”: “Custom Fields”,
“count”: 2,
“name”: “Custom Fields”
},
{
“_id”: “Scheduling Appointments in MySeniorCenter”,
“count”: 2,
“name”: “Scheduling Appointments in MySeniorCenter”
},
{
“_id”: “Adding a New Username and Password”,
“count”: 2,
“name”: “Adding a New Username and Password”
},

{
“_id”: “Healthways (Silver Sneaker), Silver & Fit, Senior Dine”,
“count”: 3,
“name”: “Healthways (Silver Sneaker), Silver & Fit, Senior Dine”
},
{
“_id”: “Reminders”,
“count”: 1,
“name”: "R

eminders"
},
{
“_id”: “Outreach/Services”,
“count”: 9,
“name”: “Outreach/Services”
},
{
“_id”: “Reset Your Password”,
“count”: 3,
“name”: “Reset Your Password”
},
{
“_id”: “Event Series”,
“count”: 1,
“name”: “Event Series”
},

{
“_id”: “Introduction to Groups”,
“count”: 7,
“name”: “Introduction to Groups”
},
{
“_id”: “Setting Up Rides”,
“count”: 4,
“name”: “Setting Up Rides”
},
{
“_id”: “Duplicated VS Unduplicated”,
“count”: 3,
“name”: “Duplicated VS Unduplicated”
},
{
“_id”: “Calendar Views in Rides”,
“count”: 2,
“name”: “Calendar Views in Rides”
},
{
“_id”: "Payments in MySeniorCe

nter",
“count”: 3,
“name”: “Payments in MySeniorCenter”
},
{
“_id”: “Adding Notes for an Individual”,
“count”: 1,
“name”: “Adding Notes for an Individual”
},
{
“_id”: “People At Risk”,
“count”: 1,
“name”: “People At Risk”
},
{
“_id”: “Assign A Card”,
“count”: 2,
“name”: “Assign A Card”
},
{
“_id”: “Contact Tracing”,
“count”: 2,
“name”: “Contact Tracing”
},
{
“_id”: “The Equipment tab”,
“count”: 1,
“name”: “The Equipment tab”
},
{

"_id": "Statistics Overview", 
"count": 7, 
"name": "Statistics Overview" 

},
{
“_id”: “The Personnel Tab”,
“count”: 2,

"name": "The Personnel Tab" 

}
]

@bill65358 My apologies for not testing out the suggested code. The example that I have in one of my projects is a little different than this where I was merging the aggregate data with some other data.

The problem with the above code is that repeaters require a unique “_id” value that does not have spaces in any of the values. Almost every one of the items above does have spaces. What to do?

The Javascript map function can solve the problem. The idea is to create a new array that has a unique _id value along with the data that the repeater needs to display. This is the .then portion of the code above modified with the map function.

 .then((results)=>{                                     
    let index = 0;
    const newArray = results._items.map(item => {
        const newItem = {};
        index++;
        newItem._id = index.toString();
        newItem.name = item.name;
        newItem.count = item.count;

        return newItem;
     })
     // now assign the newly formed array to the repeater
     $w("#repeater").data newArray;
     $w("#repeater").show();
 }); 

I’m not sure why aggregate functions have an underscore before items with results._items. Straight wixData queries have it in the form of results.items.

It’s getting there …

@tony-brunsman Fantastic! I only had to make one small change. I changed newItem . count = item . count ; to newItem . count = ‘’ + item . count ; and it all works great now. Again thanks so much I really appreciate how you explained everything rather than just giving the answer. I learned a lot.

Final Code:

$w . onReady ( function () {

$w ( "#filter" ). onClick (( event ) => { 
    $w ( '#repeater' ). onItemReady (( $item ,  itemData ,  index ) => { 
        $item ( '#count' ). text  =  itemData . count ; 
        $item ( '#title' ). text  =  itemData . name ; 
    }); 
    **const from**  =  $w ( '#fromDate' ). value ; 
    **const**  to  =  $w ( '#toDate' ). value ; 
    **const**  filter  =  wixData . filter (). between ( "_createdDate" ,  **from** ,  to ); 
    wixData . aggregate ( 'tutorial_logs' ) 
        . filter ( filter ) 
        . group ( "name" ) 
        . count () 
        . descending ( 'count' ) 
        . run () 
        . then (( results ) => { 
            **let**  index  =  0 ; 
            **const**  newArray  =  results . _items . map ( item  => { 
                **const**  newItem  = {}; 
                index ++; 
                newItem . _id  =  index . toString (); 
                newItem . name  =  item . name ; 
                newItem . count  =  '' + item . count ; 

                **return**  newItem ; 
            }) 
            // now assign the newly formed array to the repeater 
            console . log ( newArray ) 
            $w ( "#repeater" ). data  =  newArray ; 
            $w ( "#repeater" ). show (); 
        }); 
}) 

});

1 Like

@bill65358 I haven’t tried the .and approach. There is probably a way to make that work.

However, you can add conditions onto the original filter statement like this:

const filter = wixData.filter().between("_createdDate",from,to).contains('staff_name',$w('#staff').value);

@tony-brunsman I actually figured it out with my first approach. I just had a dumb typo in there.