Hi Community,
I need to have a validation on my edit page and this validation will involve complex sql query, now please help me how i can convert below sql query in esq client side.
The query below involves some joins and group by function
select a.Table2Id, b.Name, a.CreatedOn from Table1 a
inner join Table2 b on a.Table2Id = b.Id
join (
select b.Name as Name, Max(a.CreatedOn) as CreatedOn from Table1 a
inner join Table2 b on a.Table2Id = b.Id
group by b.Name )
c on c.Name = b.Name and c.CreatedOn = a.CreatedOn
Like
If you're not able to come up with an ESQ solution for this (I've never tried a groupby with ESQ, not sure if it can do those - nothing mentioned in the docs), often when I have a complex SQL statement, I instead put it in an configuration web service and do the query using the Select class or a direct SQL statement, then call/consume the service from the client side code.
If you go that route, here's some articles on those topics:
Select class: https://academy.creatio.com/documents/technic-sdk/7-15/retrieving-infor…
Direct SQL: https://customerfx.com/article/executing-direct-sql-statements-in-a-pro…
Creating and calling configuration services: https://customerfx.com/article/creating-a-web-service-and-consuming-it-…
Ryan
I'm not 100% on that, but I'm fairly sure that you can use an aggregration column for the nested SQL statement, which handles the group by for you. You could then run the first 2 lines as a separate ESQ inside the first ESQ callback, and then do the rest of the logic separately in JS.
Dear Fulgen,
Unfortunately, there is no way to create a complex query with subqueries via client ESQ.
You can create a business process that will perform the request to the database, fetch all needed data, process it and display it on the page.
Hi Ryan Farley,
When using CustomQuery, what library should I declare? I am getting "The type or namespace name CustomQuery could not be fould".
Thank you
Fulgen Ninofranco,
CustomQuery is located in Terrasoft.Core.DB
Ryan