A function that passes back the results of a simple query with multiple entities

A function that passes back the results of a simple query with multiple entities

  
Hi, this feels like it should be simple, but I'm struggling...

To improve code re-use, I want to create a function that passes back the results of a simple query, so I can then re-use that data in multiple places in the application. If there were just a single entity, it's easy - just use a record list parameter of record type same as the entity.

However, as soon as the simple query has two or more entities, I can't find a way to pass back the results.

There must be a way, hopefully one of you kind people can put me out of my misery and point me in the right direction :)

Thanks, Iain
Hi Iain,

To do what you pretend, you can create a Structure where each attribute is a Record of each Entity that you want to map in your function (and obviously the output of your Simple Query).
After that you just have to iterate your Query to fill a Record List of the structure I
mentioned before which will be the output of your function.
However, if you want to do this with a better performance you should create an Advanced Query instead of the Simple, to map the result directly to the output list.

Best Regards,
Gonçalo Martins
Hi, thanks a lot for that prompt answer. I'd not thought about iterating through the list again, I've just tried that and I've got a working model now.

It just feels a bit wrong to have to iterate a list just to get it into a structure, when that structure is identical to the list of entities in the Simple Query. Also, I take your point about the Advanced Query for performance, but again it's additional development work to achieve it.

I'm up and running now, so thanks for helping - hopefully the product can evolve in the future to incorporate this without the additional iteration.

Thanks!

You can move the Query into an action and at the end of the action, set a list with the query results (you can reference multiple entities).  Then you just use the action instead of the query whenever you need to access the query.  This is a beneficial way because you can also cache the results from inside of the action to use across your application to increase performance.  You also don't have to have a structure to store the data.

[Clarification] ... you can reference the action from inside a function to retrieve an individual value that you want.

Amazing! That was what I was missing - with the record list, I never realised you could add multiple entities - I thought you had to select just one, and if you needed multiple ones, I thought you had to use a structure.

Finding the record editor at the bottom of the Record Definition drop-down that lets me add as many as are in the query is exactly the solution I was hoping for!

Thanks so much Rebecca!
Iain Fogg wrote:
Hi, this feels like it should be simple, but I'm struggling...

To improve code re-use, I want to create a function that passes back the results of a simple query, so I can then re-use that data in multiple places in the application. If there were just a single entity, it's easy - just use a record list parameter of record type same as the entity.

However, as soon as the simple query has two or more entities, I can't find a way to pass back the results.

There must be a way, hopefully one of you kind people can put me out of my misery and point me in the right direction :)

Thanks, Iain
 
 Hi Iain,

First of all creating an action just to return the result of a simple query although it might promote re-use is not a good solution, but I'll get back to this later on and first explain how you'd solve your problem.

Let's assume you have action GetRecords and in that action you have an output parameter Records of type record list. You know when you choose as type Record or Record List you have to select the record definition.



Now in the Record Definition you can add multiple entities just select the option Record Editor from the drop down (alternatively you can double click the label Record Definition)
In the popup window you can add several entities and structures to create the desired record definition.


That should solve your problem.

Now for the more revelant part:
Why is this not a good idea?
At at first glance it makes sense to isolate the logic in an action to be able to re-use it and we should do this as a good pratice for every bit of business logic in our applications.The case gets different for the simple query though.

The simple query is actually a complex object that's optimized by the Agile Platform. Not only the platform infers the SQL syntax based on the target DBMS but it also infers based on context some optimizations. A simple query that is added to the preparation of a screen is optimized in terms of the columns that are fetched from the database. Only the attributes used in that "context" are fetched from the database. This is quite relevant when you have several entities in and entities with many attributes in the simple queries. You can confirm this using some profiling tool on the database



It also infers that a simple query that is associated with a widget is optimized in terms of number of rows fetched as you can read from the help below.



So if you encapsulate a simple query you loose all this:
  • The query is not bound to a widget so if Max Records is not filled all rows are fetched - of course you can work around this by passing an input parameter to your action for the max records.
  • The platform can't determine wich attributes are used so every attribute is fetched - you can work around this using an advanced query and structures but with a huge penalty in simplicity
Cheers,
André

Hi Andre, thanks for your comprehensive answer.

This does raise an interesting issue about OutSystems and architecture - I want to have a single definition for a query, to allow the definition of that rather complicated query to be in one place only. I want that so that query can be used in many different places (including a normal website and a mobile website), without the risk of developers making any errors in it.

And in fact, the reason I am concerned about errors being made by developers on this query is due to the way Service Studio guesses how to join entities together - if you try creating a simple query and add Group, then Group_User, then User, you'll see that Service Studio generates a join to User from Group, using the CreatedBy field, rather than joining User to Group_User, which is the proper join. Interestingly, if you add User then Group_User then Group, it comes up with the proper join.

Anyway, putting aside the issue with the odd join that Service Studio is creating, it's surely quite common to have a complicated query that you don't want to have to duplicate again and again. There are 9 entities involved in this query, to return the data about who's on what team, in what position. Most of the data returned by the Simple Query is in fact not needed - the entities are only in the query to provide the joins that actually help get the relevant info out.

Also, when looking at the four level architecture proposed this year at NextStep, I'd assume that the definition of a query to return a specific selection of data is something that should live in the data layer, or in the business layer (but coded in a single place to improve code re-use), rather than living in web interfaces?

So, my options seem to be:
  • Keep recoding the same query on every page where it's needed, risking errors, and also meaning many modifications if that query needs tweaking in the future
  • Code it once, with a Simple Query, but that pulls back every single field in every single entity and there's no way to stop that
  • Code it once in an Advanced Query, where I then need to code the SQL by hand, and I also need to create a structure for it
Assuming that I'm understanding it correctly, I'm probably going to opt for the third option - single definition of the query, reducing the number of fields being passed around, but more complexity.

But I do wish that there was a simpler way that still was efficient, so that we could have proper re-use of complicated queries.

Thanks, Iain
Hi Iain,

I agree with you that from the architecture point of view it's wise to encapsulate biz logic in actions for re-use.

Tell me what you think about this decision framework:



Cheers,
André
Hi Andre, thanks for the diagram.

By a 'list widget' I assume you mean the Table Records or List Records? Hadn't thought of a web block as a way to re-use the data query, but I think that's one approach.

Yes, I think the decision framework above is correct, based on the system as it currently works, although it obviously involves either denormalisation or hand-coded queries to achieve the desired result.

Going forward, I think it would be good for alterations to the platform to be done, so encapsulation can still provide optimisation against a single query. For example, behind the scenes, the calling web form could pass through a list of attributes that it will actually use, and the called action could prepare a query on the fly to only populate the required fields, and to leave the unused fields empty. I have no idea how easy that would be, but it would promote better architecture.

I think that sometimes, the system encourages design which is not modular, so good design sometimes means harder work. It would be great if the system were optimised so it was easiest (or certainly not harder) to use with good modular design.

Also, I know it's a side issue, but did you pick up on what I said about the incorrect joins being created when you add Group, then Group_User, then User in a Simple Query? It would be great if Service Studio asked you which join to use where there are multiple options, rather than assuming the wrong one.

Any thoughts?
Iain Fogg wrote:
Hi Andre, thanks for the diagram.

By a 'list widget' I assume you mean the Table Records or List Records? Hadn't thought of a web block as a way to re-use the data query, but I think that's one approach.
 

Yep that's it. 

Going forward, I think it would be good for alterations to the platform to be done, so encapsulation can still provide optimisation against a single query. For example, behind the scenes, the calling web form could pass through a list of attributes that it will actually use, and the called action could prepare a query on the fly to only populate the required fields, and to leave the unused fields empty. I have no idea how easy that would be, but it would promote better architecture.

I don't imagine this to be simpler to the developers...

I think that sometimes, the system encourages design which is not modular, so good design sometimes means harder work. It would be great if the system were optimised so it was easiest (or certainly not harder) to use with good modular design.

Can you give an example of this?

Also, I know it's a side issue, but did you pick up on what I said about the incorrect joins being created when you add Group, then Group_User, then User in a Simple Query? It would be great if Service Studio asked you which join to use where there are multiple options, rather than assuming the wrong one.

Any thoughts?

Has you might expect Service Studio is clever but not psychic :)
What happens is that when you add Group and then Group_User the first relationship that Service Studio finds in both entities, and only one in this case, is the Group_Id attribute in Group_User. When you add the User entity it repeats the algorithm and it finds the CreatedBy attribute in the Group entity that relates with User. You only have an option to revert the automatic condition created but no option to query the developer what relationship to assume. I think this needs a deeper analysis but I'm glad that you found a reasonable work around. Actually if you look to your example it makes more sense to start with the Group_User entity as you can see:
  • Starting with Group_User, drag it to the flow and you get a query named GetGroupUsers and if you add then Group and User you get the correct relationships
  • Starting with Group you get a query named GetGroups and if you add User you get the CreatedBy...

Cheers,
André
 
After reading this thread, please see my idea:

Define and use QUERIES (simple and advanced) like we define and use USER ACTIONS
http://www.outsystems.com/ideas/Idea_View.aspx?IdeaId=1470

Thank you.
André Vieira,
Regarding optimizations, that you above explained very good for simple queries, can you confirm if entiy actions also do that type of optimizations like simple queries?

As an example, imagine I have an entity EXAMPLE with an attibute Name and several other large attributes of type text. If I use it inside an user action like GetEXAMPLE(Id) and the user action only returns a text parameter which will be EXAMPLE.Name, the resulting database query generated by the Outsystems platform for the use of this entity action would be optimized? That is, only the attribute Name would get fetched from the database?

Thanks.
Hi Tiago,

Entity Actions do not have this optimization a Get<Entity> will return all the attributes of that entity.
André Vieira,
Thank you for you clarification.
Maybe this information could be added to the OutSystems Platform 9 Help in here:
   http://www.outsystems.com/help/servicestudio/9.0/default.htm#Using_Data/Entity_Actions.htm
I think it would be very usefull.
Thanks for the feedback!