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
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:
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.
