mORMot Open Source

richard6688

Member

Registered: 2011-04-05

Posts: 31

TSQLRecord & aggregate function

Hi,
   I try  to implement TsqlRecord descendant which need use some aggregate function, i.e. Get Max(ID). After search the forum, I know I can use ExecuteList to Retrieve the data. But, in this thread http://synopse.info/forum/viewtopic.php?id=1099, ab said,
"More generally, aggregate functions are not available - they are not REST-ready, I suspect. "

The aggregate functions is offen used. Here, I have a idea may resolve this problem simply, but I do'nt know if it's possible to implement it.
The idea is to use view to Retrieve such data, so it mean we need to implement a base class which may call TSQLRecordView. This class can only operate with GET, and have a property to define the view creation.

Now, we may use TSQLRecord to bind some view created in server manually. Here, the problem is how to define view creation automatically.

How about that? Is't possible?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: TSQLRecord & aggregate function

In the REST methods which return information (the "R"etrieve of CRUD), you can specify an aggregate function for the fieldname.
You will have to test it, perhaps there is some security which does not permit to send e.g. to an external database such functions as fieldnames.
Perhaps worth a ticket for a feature request.

Otherwise, you can execute the SQL directly, without any problem - preferred as an interface-based or method-based service on the server side.
Remote execution of any SQL is disabled by default, for safety.
By encapsulating the statement within a remote service, you gain separation of concern (not putting the logic on the client side, but let it stay on the server side) and enhance security.

Views should work as any table, but they should already be created by SQL before the database is initialized by the ORM, otherwise CreateMissingTables method would try to create a regular table instead of a view.