group by of one aggregate used in other aggregate

group by of one aggregate used in other aggregate

  

i have an group by aggregate count and i have to use that count in other aggregate but problem is i am getting only current value for all records i.e. same value .

I have to done all this for dynamic sorting because dynamic sorting cant be applied on group by columns


Any better suggestion?

Hi Lovish.

As I understand your problem, the first aggregate will return multiple counts (one for each group). But if you try to use these counts in another aggregate, you will have to use the Current one. If you are outside of a foreach loop, this will be the first one.

If you want to use all counts returned from the first aggregate, you will need to use a foreach to iterate the first aggregate, and inside the foreach execute the second aggregate.

However, with this approach, you will have multiple executions of the second aggregate, and you will probably need to append them to a single list. An easier option would be to try to combine both aggregates into a single aggregate, or use a SQL query.

leonardo.fernandes wrote:

Hi Lovish.

As I understand your problem, the first aggregate will return multiple counts (one for each group). But if you try to use these counts in another aggregate, you will have to use the Current one. If you are outside of a foreach loop, this will be the first one.

If you want to use all counts returned from the first aggregate, you will need to use a foreach to iterate the first aggregate, and inside the foreach execute the second aggregate.

However, with this approach, you will have multiple executions of the second aggregate, and you will probably need to append them to a single list. An easier option would be to try to combine both aggregates into a single aggregate, or use a SQL query.

ok.. Just suggest me , if i apply ListSort action on column of a table and that table is bound to pagination. Then why List sort is not able to sort the entire data which have passed?


ListSort just sorts a list. It has nothing to do with pagination, so I'm not sure if you're using it properly. I'm also not sure how is this related to your original question.

Could you share more details on what are you trying to accomplish? Or maybe attach the module you're working on.

leonardo.fernandes wrote:

ListSort just sorts a list. It has nothing to do with pagination, so I'm not sure if you're using it properly. I'm also not sure how is this related to your original question.

Could you share more details on what are you trying to accomplish? Or maybe attach the module you're working on.


i am attaching you oml. i have applied a link to column which sort the values. 

i want

1. To sort the entire data, not just 5 records for 1 page

2. i want to persist those sorted data for that page even if i am navigating

for ex: 

first page has 

1,2,3,4,5 entries

second page has

6,7,8,9,10 enteries


so what i want is , whenever i sort them the first page contain 10,9,8,7,6

and second page contain 5,4,3,2,1.

but unfortunately it is not happening, i dont know what i am doing wrong.

and i want to do it through Listsort action only

Hello Lovish. As I've imagined, you are using the ListSort incorrectly, but it's not obvious so I'll try to explain why, before describing a few alternatives for solutions.

What's happening is that the platform tries to optimize the list that is stored between two requests. This data is stored in the viewstate, and the more data needs to be stored, the higher the impact on performance. So it's a good thing that the platform does that.

In your case, because you're only using the result of the query to display it in a table, the platform only stores the rows that are required to be displayed. And that results in the behavior you're seeing.


An easy solution for your case is to refresh the aggregate (like you did on the OnNotify action), before you do the sorting. That will fetch the data from the database, and not from the viewstate. And this will probably mean that the platform will be able to optimize the viewstate even further!


However, the best option would be to do the sorting on the aggregate itself, and not use the ListSort. For that, you should be using the List_SortColumn block from RichWidgets. To understand how to use it, I highly recommend you to watch this training material and do the exercise to make sure you understood the concepts. As a bonus, you'll have pagination and dynamic sorting in all columns, and following the best practices!

leonardo.fernandes wrote:

Hello Lovish. As I've imagined, you are using the ListSort incorrectly, but it's not obvious so I'll try to explain why, before describing a few alternatives for solutions.

What's happening is that the platform tries to optimize the list that is stored between two requests. This data is stored in the viewstate, and the more data needs to be stored, the higher the impact on performance. So it's a good thing that the platform does that.

In your case, because you're only using the result of the query to display it in a table, the platform only stores the rows that are required to be displayed. And that results in the behavior you're seeing.


An easy solution for your case is to refresh the aggregate (like you did on the OnNotify action), before you do the sorting. That will fetch the data from the database, and not from the viewstate. And this will probably mean that the platform will be able to optimize the viewstate even further!


However, the best option would be to do the sorting on the aggregate itself, and not use the ListSort. For that, you should be using the List_SortColumn block from RichWidgets. To understand how to use it, I highly recommend you to watch this training material and do the exercise to make sure you understood the concepts. As a bonus, you'll have pagination and dynamic sorting in all columns, and following the best practices!

Thanks for your information. Actually i have to show 1 column in the table which is not the part of any aggregate and that is part of group by. So because of that coulmn i have to do all these things. The other alternative i have used is taking that count column in 1 aggregate and that in other aggregate , but it will point to current record only and solve this problem i have to apply for loop but it will create performance issues. 


Hi Lovish. Are you sure you can't combine the two aggregates into a single one? It seems that you are doing a JOIN between two aggregates manually by iterating the first aggregate and executing the second one repeatedly.

Again, if you think you cannot do that with aggregates, then I would suggest you to write a SQL query, with which you can write subqueries and other advanced constructs. The List_SortColumn I've talked about also works with SQL queries, but you will need to wire it to the query manually. I can help you with that, once you rewritten your logic to use a single SQL query.

Hi Lovish,

Queries, both Aggregates and SQL, are executed by the database, the result set (partially) sent to the web server that creates the pages for your browser to render. Queries are "stand alone" in that the database cannot use the output of one query as input of another (unless you use in-memory temporary tables and the like, but OutSystems doesn't have an easy way to make use of that). So although your observation that "it will point to the current record only" is correct, it seems you don't quite understand why that is the case.

Another thing to point out is that dynamic sorting can be used with calculated columns (like a Group By) of an Aggregate. See e.g. here and here.