Views - Columns aggregated using Sum and grouped by objectId of user is producing strange results

[James_Greff]

I have a number of tables where each object is related to users 1:1. Each table contains financial data and uses generated columns to sort and summarize the financial data. The generated columns are working well in each individual table. Ultimately I would like to aggregate (sum) these generated columns by user.

However, the views that I have created are behaving in unexpected ways. The first column in the view is the objectId of the user. Each additional column is a generated column from related tables, aggregated using SUM. The aggregated columns are grouped by objectId. The values produced for each user are often 100X what I would expect.

I would like each object in the view to contain summed data that relates only to the individual user.

Am I doing it wrong?

Thanks

[olhadanylova]

Hello @James_Greff,

could you please provide your Application ID?

Regards,
Olha

[James_Greff]

Hi Olha, Thanks for responding. My application ID is 2B3D5B89-DC72-CF6E-FFA2-9A32C8857800

Thanks

[Volodymyr_Ialovyi]

Hello @James_Greff

Let’s take the FinancialExpense table as an example:

Users   CurrentMonthFinancial 
user1   100
user1   100
user1   100
user2   50

After you group by Users, you get two groups:
First group

user1   100
user1   100
user1   100

Second group

user2   50

Then you summarize CurrentMonthFinancial in groups

Users   Sum CurrentMonthFinancial 
user1    300
user2    50

That is, you get the expected result

You can choose not to group or summarize. You can change the relays to not from the CurrentMonthFinancial table to the Users tables, but from the Users table to the CurrentMonthFinancial table. It all depends on what kind of result you want to get.

Perhaps our documentation will be useful to you:
Data filtering
Aggregate Functions Overview
SUM

[James_Greff]

Thanks for your response. I am able to group and sum fine according to the documentation using the rest api. This has so far required me to make multiple calls to multiple tables to get the data, and then I am using the client side to manipulate the data and to make calculations with the data. It is my understanding that one of the main advantages of using views is that they can help avoid the need to make multiple api requests and then avoid messy client-side data manipulation. For convenience and efficiency I would I like to summarize columns from multiple tables in a single view in backendless and then GET a single object with all the user data from the view using the api. You’ve done a great job explaining what summing by group does, but unfortunately does not answer the question about how to implement the solution in a view. Is it possible, and if so how?

[mark-piller]

Hi James,

I just reread the thread to see if Vladimir’s response addresses the original question. Did you try the approach he described using a view?

Regards,
Mark

[James_Greff]

I did try the approach suggested and have discovered another clue about what might be happening with the views. I’ll do my best to describe the view behavior that is confusing me.

So long as I only include properties from a single source table, the sum aggregation works as expected when grouped by user objectId. In the picture below, the 910, 192, 1102, and 0 numbers are all what I expect based on the source data from the Transactions table.

However, if I add a property to the view from a second source table and aggregate that property with sum, the summed numbers from the Transactions table change. The summed number in the CurrentMonthFinancial property is also wrong. I don’t understand why the summed Transactions property data would change as a result of adding a property from the FinancialExpense table.

[mark-piller]

Hi James,

What is the name of the View shown in the screenshots? There is an explanation for the data you’re seeing and we’d like to look into the data itself to be able to rationally describe it.

Regards,
Mark

[James_Greff]

The view is called CurrentMonthBudgets. Thanks.

[vladimir-upirov]

Hello @James_Greff

Sorry for the delay with the response and thanks for your patience.
We have investigated the issue and let me explain why you get the result:

1. first of all, let’s minimize the input data
   a) you have Users / Transactions / FinancialExpense tables
   b) Transactions.Users column is 1:1 relation to the Users table
   c) FinancialExpense.Users column is 1:1 relation to the Users table

2. then you create a view with 3 columns (without grouping by objectId):
   a) objectId - is objectId in the Users table
   b) SpentThisMonth - is SpentThisMonth in the Transactions table
   c) add filter for the specific userId = E3B1DE8A-B935-4618-93DA-BA71C24AE35E in order to concentrate only on the record
   d) as a result you’ve got the correct data set
   e) and if you aggregate it you receive 910 - which is right

   

3. now, let’s see what happens when you add a column from the 3rd table FinancialExpense
   a) add CurrentMonthFinancial from the FinancialExpense table
   

   

   b) do not add any aggregation at this moment
   c) you can see that SpentThisMonth column now has duplicated values

   

   d) it happens because JOIN in the database works in this way

4. and finally when you aggregate it you receive “wrong” values, when
   

   

So, I do not see any solution to get what you want in a single API Call, even using DataViews.

However, I can propose you split the view into two (for each table: Transactions / FinancialExpense) and as result, you can retrieve correct values.
Then, create an API Service and do these API calls in parallel, and once they both are ready, combine them and return them to the client.
What do you think?

Regards, Vlad

[vladimir-upirov]

btw, I forget to mention that I’ve created a test view with name testView in your app to show you how it works