Design / Architecture - Where does Creatio filter data? - Application layer or database layer
Hello community,
There are numerous use cases where Creatio filters for some data before showing on the UI. Few examples below -
- Writing ESQ from JavaScript where we filter an object for only select records by adding filter groups.
- User applying of a filter in a section list page or a detail.
- When we add a detail to a Section record page, we map a column from the detail to a column in a section record. This is how Creatio filters for only the detail records belonging to that section record and displays the records subsequently.
Few questions below which will help us explain Creatio better with our customers-
- At which layer does Creatio execute these filters? Is it at the Application layer or at the Database layer? It is unlikely that it is at the App layer. Can you confirm that these happen at the DB layer and only the filtered records are fetched to the App layer and subsequently passed back to the UI?
- I am guessing that the Creatio ORM would aggregate all of the filters and frame a query which filters for the required data and then execute this at the DB layer? Excuse the over-simplification. Can you confirm and share a few more details on how this done? This would give us insight into the Product architecture and help us understand the product better.
If these insights are already available in the Documentation, Request you to share the link. Thanks in advance!
Like
Hello,
Yes, all filter conditions are added as conditions for queries and are performed at the database layer. You can see this if you install Creatio locally and run SQL profiler. You'll notice as an ESQ gets executed that ultimately this is translated to a SQL query with the filter conditions in the where clause.
Additionally, as a related side topic to mention to your original question which might be of interest, you can also create SQL views in the database containing aggregates/joins/unions/ranks/partions/etc and create an entity/object for that view. These objects can then be used normally in section lists, details, dashboards, reports/printables, and even read via OData/DataService APIs etc. This is a fantastic way to create what would be a complex or time consuming query and make the results readily available in the client.
Hope this helps with your question.
Ryan
Hello Shrikanth,
We can confirm that filtering conditions are configured as conditions for queries and they are being performed on the DB layer, but not the application layer.
We will ask the responsible team to add more information relateable to this kind of topic to the Academy. At the same time, please check the articles included in this section, they should be useful for further development operations:
https://academy.creatio.com/docs/developer/back-end_development
Thank you Ryan for your help!
Best regards,
Roman
Ryan Farley,
Thank you Ryan. Yes, I was aware of the views and similarly noticed usage of Stored procedures in the community. This prompted my question.
Ryan Farley,
If I can quote:
you can also create SQL views in the database containing aggregates/joins/unions/ranks/partions/etc and create an entity/object for that view
If I were to create a SQL view, how would I create an entity/object of the view? Thanks,
[Edit]
I think I have the answer here Creating View Type Object in Creatio | Community Creatio