Error after using .having() method of WixDataAggregation!!

Ask the community
,
1

I was doing some calculation on my dataset using WixDataAggregation:
This is my code -

import wixData from 'wix-data';

 var acc = ["","","","","","","",""];
 var apv = ["","","","","","","",""];
 var dis = ["","","","","","","",""];
 var dpv = ["","","","","","","",""];
 var add = ["","","","","","","",""];

export async function reportAcc(date1, date2, type) {
    await wixData.aggregate("SRFU18-19-20")
    .group("salesExecutive")
    .sum("accessoryRs")
    .avg("accessoryRs")
    .sum("discountRs")
    .avg("discountRs")
    .having(wixData.filter().contains("modelCategory", type)) // When I use this line the code throws error :
                                                            "Cannot read property 'accessoryRsSum' of undefined"
    .limit(1000)
    .run()
    .then(results => {
        acc[1] = results.items[0].accessoryRsSum;
        apv[1] = results.items[0].accessoryRsAvg;
        dis[1] = results.items[0].discountRsSum;
        dpv[1] = results.items[0].discountRsAvg;
        add[1] = apv[1] - dpv[1];
    });
    
    return [acc,apv,dis,dpv,add];
}

Please help its urgent (More explanation in the further reply)

Thanks in advance.

2

Try to console-log your results, then you will surely find your solution.
I have changed some code-lines (but not tested). You can test it ^^

import wixData from 'wix-data';

$w.onReady(function () {
 var acc = [];
 var apv = [];
 var dis = [];
 var dpv = [];
 var add = [];

 extern async function reportAcc(date1, date2, type) {
        console.log("DATE1 = " + date1)
        console.log("DATE2 = " + date2)
        console.log("TYPE = " + type)
 
 await wixData.aggregate("SRFU18-19-20")
        .group("salesExecutive")
        .sum("accessoryRs")
        .avg("accessoryRs")
        .sum("discountRs")
        .avg("discountRs")
        .having(wixData.filter().contains("modelCategory", type)) // When I use this line the code throws error :
 "Cannot read property 'accessoryRsSum' of undefined"
        .limit(1000)
        .run()
        .then(results => {
            console.log(results.items[0].accessoryRsSum)
            console.log(results.items[0].accessoryRsAvg)
            console.log(results.items[0].discountRsSum)
            console.log(results.items[0].discountRsAvg)

            acc.push(results.items[0].accessoryRsSum)
            apv.push(results.items[0].accessoryRsAvg)
            dis.push(results.items[0].discountRsSum)
            dpv.push(results.items[0].discountRsAvg)
            add[0] = apv[0] - dpv[0];
        });    
 return [acc,apv,dis,dpv,add];
    }
});

Why —> extern async function reportAcc(date1, date2, type) {…
Why EXTERN?

3

Long time no see, Nice to hear from you. Thanks for replying.

Actually, the code I posted was a back-end code;

Here is the complete story:

I hope you will understand and will make out a solution.

Site Code-

import {reportAcc} from 'backend/sale_mis';
import wixData from 'wix-data';

$w.onReady(function () {

});

export function button1_click(event) {
  let yearValue = $w("#datePicker1").value.getFullYear();
  let monthValue = $w("#datePicker1").value.getMonth();
  let dayValue = $w("#datePicker1").value.getDate();
  let date1 = new Date(yearValue,monthValue,dayValue,0,0,0);
  
  yearValue = $w("#datePicker2").value.getFullYear();
  monthValue = $w("#datePicker2").value.getMonth();
  dayValue = $w("#datePicker2").value.getDate();
  let date2 = new Date(yearValue,monthValue,dayValue,0,0,0)
  
  console.log(date1)
  console.log(date2)

  reportAcc(date1,date2, $w("#dropdown1").value)
  .then(results => {
      console.log(results)
  });
}

Backend Code -

import wixData from 'wix-data';

async function reportAcc(date1, date2, type) 
 console.log(type)
 console.log(date1)
 console.log(date2)

 // ---------------------------------------------------ARRAYS-------------------------

 var acc = ["","","","","","","",""];
 var apv = ["","","","","","","",""];
 var dis = ["","","","","","","",""];
 var dpv = ["","","","","","","",""];
 var add = ["","","","","","","",""];

 // ---------------------------------------------------FILTERS------------------------
 
 // let pnp21 = wixData.filter()
 // .between("estDate", date1, date2)
 // .and(wixData.filter()
 // .eq("modelCategory", type));
    
let having = wixData.filter().contains("modelCategory", type).between("estDate", date1, date2);

 // -----------------------AGGREGATION OF DIS & ACC------------------------

 await wixData.aggregate("SRFU18-19-20")
    // .filter(pnp1)     {First I tried this and also recieved the same error}
    .group("salesExecutive")
    .sum("accessoryRs")
    .avg("accessoryRs")
    .sum("discountRs")
    .avg("discountRs")
    .having(having) {Then I Tried this and recieved the same error}
    .limit(1000)
    .run()
    .then(results => {
        console.log(results.items)
        // acc[1] = results.items[0].accessoryRsSum; ---------
        // apv[1] = results.items[0].accessoryRsAvg;          |-----     Forget  
       // dis[1] = results.items[0].discountRsSum;            |-----    This for
       // dpv[1] = results.items[0].discountRsAvg;            |-----    a Moment
       // add[1] = apv[1] - dpv[1];----------------------------
    });

 // ------------------------------RETURN--------------------------------

 return [acc,apv,dis,dpv,add];
 
}

If I do not use any type of any type of filter (.filter() or .having()) then there is no such errors.

4

@rinshulgoel

Yeah nice to see you again, too. Yes i was for a long time not here, but i can not without coding :wink:

Not sure if i can solve this without seeing your project, but let us do it together.

You start from front-end with…

import {reportAcc} from 'backend/sale_mis';
import wixData from 'wix-data';

$w.onReady(function () {

});
/--------------------/till here without any comment

//here you have a Button called "button1" with an "onClick"-event which starts a function. 

export function button1_click(event) {
  let yearValue = $w("#datePicker1").value.getFullYear();
  let monthValue = $w("#datePicker1").value.getMonth();
  let dayValue = $w("#datePicker1").value.getDate();
  let date1 = new Date(yearValue,monthValue,dayValue,0,0,0);
  
//You select two dates with the help of a datapicker and you also get a value from //"dropdown1" (must be ---> type of something and you search it later in //"modelCategory" in your backend-code, right?)

  yearValue = $w("#datePicker2").value.getFullYear();
  monthValue = $w("#datePicker2").value.getMonth();
  dayValue = $w("#datePicker2").value.getDate();
  let date2 = new Date(yearValue,monthValue,dayValue,0,0,0)
  
  console.log(date1)
  console.log(date2)

//here you start your backend-function and waits for some returning results.....
//Now you are taking "date1" + "date2" + "type" and pushes it to the backend....

//here you should also check "$w("#dropdown1").value" first ....
console.log($w("#dropdown1").value)

//When till here everything is ok and all of 3-values are ok, then we switch to the //backend.

  reportAcc(date1,date2, $w("#dropdown1").value)
  .then(results => {
      console.log(results)
  });
}

continue with backende…
Backend Code -

import wixData from 'wix-data';

//here we recieve now the 3-values from frontend ("date1", "date2" and "type")

async function reportAcc(date1, date2, type) 
 console.log(type) <---------- good to check again
 console.log(date1) <---------- good to check again
 console.log(date2) <---------- good to check again

//Everything works till here? If YES, then we continue.....
 // ---------------------------------------------------ARRAYS-------------------------
 var acc = ["","","","","","","",""]; //<--- why you are using especially this 
 var apv = ["","","","","","","",""]; //<--- array-structure, i do not know, but no
 var dis = ["","","","","","","",""]; //<--- problem, we continue like that
 var dpv = ["","","","","","","",""]; //.........
 var add = ["","","","","","","",""]; //.....
 // ---------------------------------------------------FILTERS------------------------
 
 // let pnp21 = wixData.filter()
 // .between("estDate", date1, date2)
 // .and(wixData.filter()
 // .eq("modelCategory", type));
   
//here you look in your data-collection for "type"-value(s) in "modelCategory"
//related to the 2 selected dates from front-end ("date1" & "date2").

let having = wixData.filter().contains("modelCategory", type).between("estDate", date1, date2);

//How does ---> "having" look like? 
console.log(having) ----> RESULT ? Just one value, or can it also be an ARRAY?

//having[0] ??? , having[1] ??? 
//or just having ???

// Till here, everything works fine???? All given results till this point are ok?

 // -----------------------AGGREGATION OF DIS & ACC------------------------

 await wixData.aggregate("SRFU18-19-20")
    // .filter(pnp1)     {First I tried this and also recieved the same error}
    .group("salesExecutive")
    .sum("accessoryRs")
    .avg("accessoryRs")
    .sum("discountRs")
    .avg("discountRs")
    .having(having) {Then I Tried this and recieved the same error}
    .limit(1000)
    .run()
    .then(results => {
        console.log(results.items)
        // acc[1] = results.items[0].accessoryRsSum; ---------
        // apv[1] = results.items[0].accessoryRsAvg;          |-----     Forget  
       // dis[1] = results.items[0].discountRsSum;            |-----    This for
       // dpv[1] = results.items[0].discountRsAvg;            |-----    a Moment
       // add[1] = apv[1] - dpv[1];----------------------------
    });

 // ------------------------------RETURN--------------------------------

 return [acc,apv,dis,dpv,add];
 
}

Try to complete my analysing method with questions and some more of CONSOLE-LOGS, the same way, like i did it. And always check your RESULTS after every STEP.

This will make you understand your own code better and you should be able to find the reason of your problem faster.

5

@russian-dima
I don’t know why but now the code starts working. there is no such issue.

6

@rinshulgoel :grin: really?
So the problem is solved already?
Must be magic :wink:

7

Haha…