Why aggregate and not SQL?
Discussion

Just wanted to know why we should use Aggregates and not SQL in general. I guess databases do not understand anything except the SQL so internally OutSystems might be converting aggregates into SQL which will create an overhead time.

  1. It claims that Aggregates optimize the query and fetches only the columns/attributes needed but this seems just to hide the developers silly mistake which can be resolved in SQL query manually if written properly.
  2. It claims that Aggregates get converted to respective SQL depending on the database type used but it does not happens most of the times that projects are using two different type of databases relying on different SQL's. Moreover, we have to write few complex queries in projects as it is not so straight forward always, so in those cases with multiple databases we will have to write SQL manually anyways, so why aggregates and not sql?
  3. While using aggregates and displaying the data in a search list, we have to convert the date and datetime in human readable format like 18 Aug 2021 which can be easily achieved with SQL which do work with common search. With Aggregates, we will have to manipulate the format on UI level but then it does not work with the common search given.
  4. Aggregates can be developed faster than SQL but I guess it would not take more than 5 minutes extra to write an SQL which can be easily maintained, manipulated and can be easily converted to complex one if needed while working in agile development where the system changes and keeps on growing.
  5. One plus of aggregate can be that it can be used at multiple places where different attributes from same sources and conditions are required without any modification but this alone does not convinces me to use aggregate instead of SQL.


So the question remains the same, why Aggregates and not the SQL?

Hi Vaibhav,

i think this is similar to the question 'why lowcode and not traditional code', if you choose lowcode, I would say embrace it as fully as possible, you are paying a price for the platform, so don't use it as an empty shell to carry your traditional javascript .net or sql code, only use those where the platform doesn't offer a low code option.

As to this specific question of aggregates vs sql :

0.  yes the platform turns your aggregates into sql, but this is at compile time, not at runtime, so there is no question of 'overhead time'

1. what you call developers silly mistake, is actually developers wasting less time on matching the query exactly on what is needed for a given UI

2. it's not about using different dbms's in same factory, it's about again developers not wasting energy even having to think about what dmbs is used, and what specific non-standard sql might or might not be used.  

2. And yes, you will still have some complex queries and also bulk updates and stuff like that, for which you will use an sql widget. 

3. I don't fully understand this, can you elaborate, I don't understand what you mean by 'common search'.  If it's just a matter of not liking data type conversions in the ui, you can add calculated columns to your aggregates to do it at time of retrieval.

4. Yes aggregates are faster to make, but I think also more easily maintained than sql widgets.  But more importantly, imagine adding a column to a list in a UI (from an entity already retrieved), in an sql widget that you have optimized manually (by limiting retrieved columns) you'll have to add that column to the select clause and will have to add it to the output structure of the widget, with aggregates you have to do NOTHING, the platform takes care of it.  No matter how easy you find maintaining SQL widgets (and remember, low code is aimed at bringing in developers with a non-developer background), you can't beat not having to maintain at all.

4. An aggregate can be converted to an sql when requirement becomes too complex.

5.  Actually, this is one thing I would not do with aggregates (i.e. reuse them), if they are reused for different use cases, a lot of the benefit of optimizing for serving a single use case gets lost.


Dorine

Thanks a lot for the explanation. I have learned a lot from this one.

When we show data in tablular listing, we usually have a common search, an illustrative example of which I am attaching. With reference to the the image, lets consider the start date, usually we keep it in database as YYYY-MM-DD, that is 2021-08-19 but what if I have to show it as 19 Aug 2021 (easier human readable). 

Here I can do two things either use sql and manipulate it directly with given sql functions or convert the date format at UI level with OutSystems function. In the former case if I type "Aug" in search box, the search will give me correct result but the latter would not. This is just one of the case which is quite common and is used very frequently while displaying data in tabular format.

search.png

Ah Ok, 

I see, so yes, for this possibly using an sql widget is more straightforward than trying to build some clever calculated column in your aggregate.

I'm not sure though, how well suited such a common search is for using with date or time information.  I.e. as a user, i would have either a rough idea of when something happened, and might want to give a rough date range, like 'the last week', 'the last year', 'between that and that month', or I have very good idea of when something happended, because maybe I'm holding a piece of information like an invoice, and then i just want to select a single date, or maybe a small range from a date picker.  

I don't think there is a lot of use cases where as a user, I am looking for something that happend in some date matching 'Jul'.  But I also understand how you prefer a common search to keep the UI simple, so there's options for you to consider and weigh.

Dorine

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.