Hi,
I have SQL written in below in SQL Widget.How should i do it in Aggregate?
--open by meSELECT {SRJournal}.* ,{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],{SRStatus}.[Label],{User}.[Name],Pickup.[Name]from {SRJournal}inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]and {SRStatus}.[Label] in ('Pending Approval','Request For Info')inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]where {SRJournal}.[ModifiedBy]=@PUserIdunion----Pending my approvalSELECT {SRJournal}.* ,{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],{SRStatus}.[Label],{User}.[Name],Pickup.[Name]from {SRJournal}inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]and {SRStatus}.[Label] in ('Pending Approval')inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]where {SRJournal}.[ApproverId]=@PUserIdunion-------pending for my processSELECT {SRJournal}.* ,{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],{SRStatus}.[Label],{User}.[Name],Pickup.[Name]from {SRJournal}inner join {SRStatus} on {SRStatus}.[Id]={SRJournal}.[StatusId]and {SRStatus}.[Label] in ('Approved','In Process','Info Ready')inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]inner join {User} Pickup on Pickup.[Id]={SRJournal}.[PickUpBy]inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]where {SRJournal}.[EntityGroupId] in (select {SRUserProfileDtl}.[EntityGroupId]from {SRUserProfileDtl} where {SRUserProfileDtl}.[UserId]=@PUserId)
Help please !!
Hi Johnson,
Based on your query I wrote an article, which you can find here: https://itnext.io/outsystems-101-aggregates-vs-sql-queries-ec7223f4c496.
In this article I point out which steps you need to take in order to create an aggregate from your SQL.
Hope this explanation helps you to learn the OutSystems platform.
Kind regards,
Remco Dekkinga
Remco Dekkinga wrote:
Good article but you are forgetting that a Union removes duplicates and ListAppendAll does not. And there is no way a ListAppendAll makes a Union obsolete, not meationing that you get a better performance running 1 Advanced with N Unions then N Agregates with N-1 ListAppendAll
Aggregates don't support the UNION operator nor the IN operator. You should use the AdvancedSQL widget instead.
João Pedro Abreu wrote:
Hi João,
All the SELECTs are fetching data from the same entity, with the same joins, with the exception of the SRStatus (that he is doing through label...).
This one should be pretty easy to do with an aggregate...
No?
Cheers,Eduardo Jauch
Yes, using a union for this is nonsense, even if using SQL. Also, since the IN is just a few values, OR could be used as well, but the most worrisome is the check on Label. One should never ever ever check on a label. If the table is defined in Service Studio, it should be a Static Entity, and in the Aggregate you should use Entities.SRStatus.Attribute. If it isn't a Static Entity, and you really can't make it into one, you should at least have some code, instead of a label.
Hi Eduardo, can you guys share some simple example?
An example of what? Aggregates are pretty basic Outsystems Platform stuff, thoroughly explained in the training videos. We love to help people that are stuck, but it seems you are not as much stuck as trying to use the Platform without the proper preperation. We're problem solvers, not unpaid teachers!
Very good article Remco.
Luís,
I'm sure Remco is aware of what you're saying, and I don't think he implies anywhere that a ListAppendAll is always a good replacement for a UNION.
It doesn't matter for the OP though, as the four queries are mutually exclusive (and can be written as a single Aggregate).
Kind of does though
But you are right, the four queries in the OP can be writen in a single Aggregate
Hi Luis,
I wrote that it can be done with a ListAppendAll and not that this is a best practice in all cases.
I do see very often that people go for the SQL query when they need just one union and they want to build that query quick and dirty without thinking about optimization and including all kinds of entities in the output. I wrote the article to show other possibilities for those people.
@kilian, thanks!
All of your Unions appear to be the same with the exception of the WHERE clause. Why not update the WHERE filter using OR instead of a UNION of 4 queries? Doing some fine tuning on your query, you should be able to get it to a single query where you do not need a UNION at all, similar to what Remco points out in his article. From there, it should be relatively straightforward to convert the query into an Aggregate.
For others that fall on this thread - when I need the equivalent of a UNION aka multiple queries in a single result, then there are two possibilities that I consider. Which route to go depends on whether the UNION may result in duplicates and whether that is acceptable to your situation.
If you know that the results of the UNION will not have duplicate rows and that duplicates are not a concern, you can create s Structure to hold the results, run separate Aggregates for each piece of the UNION and place the results into a variable of the Structure. Then use that structure for your page bindings.
If you are unable to fine tune your UNION to a single query and duplicate rows are a concern, at that point you should probably use a SQL query.
Best practices recommend using Aggregates whenever possible, Aggregates are optimized for the OutSystems platform and are far easier to maintain. Using a SQL statement should be considered a fallback when other options have been explored.
This generally includes running multiple Aggregates versus a single SQL query. The performance difference is usually negligible. On very high traffic sites where performance is a concern, it is recommended to real world test and make a determination for your situation.
Here are some great videos on Aggregates. They are specific to Traditional Web Applications but the information applies to all application types:
https://www.outsystems.com/learn/lesson/1781/basic-data-queries
https://www.outsystems.com/learn/lesson/1764/advanced-queries-aggregates
Hi Robert,
You are replying to a two year old topic! Please check the date before replying, thanks. I'm going to close this topic now.