Updating Aggregate Query Results to match filter parameters on Change Event

Ask the community
,
1

Hello!

I am having a problem getting aggregation, total count, and distinct total count results to update to reflect filter parameters.

  • I am running a query to populate a repeater with item data…it runs when the page loads…and it runs gain for each export function on-change event of dropdown & checkboxes.

  • I have set up a filter function, which alters query results based on the on-change events of the dropdown and checkboxes. See image for reference:

985×313 151 KB

I have written 3 query export functions to get aggregation, total count, and distinct total count results.

const filter = wixData.filter().eq("showInListings", true)

export function Sum_units(){
  wixData.aggregate("Portfolio")
  .filter(filter)
  .sum("units","sumamount")
  .run()
  .then( (results) => {
      $w('#unitTotal').value = results.items[0].sumamount;
  } );
}

export function Sum_projects(){
  wixData.query("Portfolio")
  .eq("showInListings", true)
  .find()
  .then( (results) => {
      $w('#projectsTotal').value = results.totalCount;
  } );
}

export function Sum_states(){
  wixData.query("Portfolio")
  .eq("showInListings", true)
  .distinct("state")
  .then( (results) => {
      $w('#statesTotal').value = results.totalCount;
  } );
}

When I activate the filter…with a dropdown change or checkbox change…the repeater results filter no problem…but the totals produced by the export query functions I created do not update to reflect the new totals relative to the filtered results.

Here is my filter function code:

function filterResults(results){ 

 const state = $w('#dropdown1').value;  
 
 if (state && state !== 'All') {    
        results = results.filter(item => item.state === state);
    } 

 if  ($w('#checkbox1').checked !== false) {      
        results = results.filter(item => item.underConstruction === true);
    }
    
 if  ($w('#checkbox2').checked !== false) {      
        results = results.filter(item => item.nowLeasing === true);
    }

 if  ($w('#checkbox3').checked !== false) {      
        results = results.filter(item => item.managed === true);
    }

 if  ($w('#checkbox4').checked !== false) {      
        results = results.filter(item => item.disposition === true);
    }
 
 
 return results; 
 
}

Here is an example of the dropdown on-change event

export function dropdown1_change(event) {
 //filtering the results
 const filteredResults = filterResults(originalPropertiesInfo);

 //setting the data
        $w('#repeater1').data = filteredResults;
 
 //setting the repeater elements
        $w('#repeater1').onItemReady(($w, itemData) => {
        $w('#thumb').src = itemData.thumbnail;
        $w('#marketName').text = itemData.title;
        $w('#location').text = itemData.location;
        $w('#units').value = itemData.units;

I imagine that this is where I need to call the aggregation export functions…inside each onchange event.

This is the original query function that produces the results that are filtered


$w.onReady(async function () {

//Query to get the information from the database    
      wixData.query("Portfolio")
        .limit(100)
        .eq("showInListings", true)
        .find()
        .then((results) => {
 
            originalPropertiesInfo = results.items;
            $w('#repeater1').data = originalPropertiesInfo;
 
        })

        .catch((err) => {
 let errorMsg = err;
        });

// sums up total units, states and projects //
Sum_units();
Sum_projects();
Sum_states();

//Set the information to the repeater     
      $w('#repeater1').onItemReady(($w, itemData) => {
//add here all the relevant elements of the repeater  
            $w('#thumb').src = itemData.thumbnail;
            $w('#marketname').text = itemData.title;
                 $w('#location').text = itemData.location;
                 $w('#units').value = itemData.units;

Looking at my code, I understand why it is not working…as I currently only run the 3 export query functions at the very beginning on page load to produce the tally and populate the repeater.

Is there a clean way to get my export queries to always draw from the filter results?

Any help here is greatly appreciated!! I feel like the answer could be quite simple.

2

I see where you’re setting the filter, but I don’t see where you’re performing a query with the changed filter. Maybe you didn’t post that section of code?

When you apply your filter to the query for the repeater, you need to also apply the filter to the three functions that determine the counts. You will need to modify the code in the functions to apply the same filter as the query for the Repeater. You can call those three functions from from dropdown1_change() function.

You shouldn’t have the Repeater onItemReady() function in the dropdown1_change() function. The onItemReady() function is defined only once, in the onReady() function. The onItemReady() functions, is actually an event handler, which means once it’s defined (in the onReady function), it is triggered every time that the Repeater’s data changes.

You might want to look at this example which demonstrates changing the contents of a Repeater based on a dropdown and a search bar.

3

You say “you need to also apply the filter to the three functions that determine the counts”…that is the crux of my issue. How do I do that?

4

@jspilberg The same way that you use the filter for the Repeater contents. Just use the same data to build the same filter. Or, create the filter in one place, and then use it for all queries that need the “same” results.

5

@yisrael-wix I adapted my filter function and inserted to each of my query functions with partial success. I am able to get some of the totals to update when a unique combination of checkboxs are checked.

But I cannot get the total count results to update when I check a single checkbox on its own…only when I check off a combination. And the resulting totals that do change are not accurate.

Here is an example of a function that is producing updated results, but not accurately or consistently with each check box. The dropdown aspect produces zero changes in the query results…see code below. This function is called with each export onchange event I have set up…just so you are aware.

Can you advise on if my filter is set up properly here? I am seeking to calculate the total number of items that match the dropdown and checkbox conditions.

export function Sum_projects(){
wixData.query( “Portfolio” )
.eq( “showInTotals” , true )
.or(
wixData.query( “Portfolio” )
.eq( “state” , $w( “#dropdown1” ).value)
)
.or(
wixData.query( “Portfolio” )
.eq( “underConstruction” , $w( “#checkbox2” ).checked)
)
.or(
wixData.query( “Portfolio” )
.eq( “nowLeasing” , $w( “#checkbox2” ).checked)
)
.or(
wixData.query( “Portfolio” )
.eq( “garrettResidentialManaged” , $w( “#checkbox3” ).checked)
)
.or(
wixData.query( “Portfolio” )
.eq( “disposition” , $w( “#checkbox4” ).checked)
)
.find()
.then( (results) => {
let projectTotals = results.totalCount;
$w( ‘#projectsTotal’ ).value = projectTotals;
} );
}