mORMot Open Source

Leslie7

Member

Registered: 2015-06-25

Posts: 248

Recursive - ORM

Hi,

We have quite a few tables with resursive data. Sqlite handles recursive queries, but  have not found anything about it on the ORM level in the docs or in  the source . Does the framework  handle this? (Something like TSQLRecordRecursive ...)  If not is there any workaround?

Last edited by Leslie7 (2015-07-08 16:03:04)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,663

Website

Re: Recursive - ORM

You would have to run directly the SQL, bypassing the ORM.

Try to use the TSQLRest.ExecuteList() method.

Leslie7

Member

Registered: 2015-06-25

Posts: 248

Re: Recursive - ORM

I think there is a simple way to extend mORMot with the ability to use common tables,   which are much readable for complex queries and  would make recursive queries possible as well.  Should also work with the  NexusDB #temptable format.
Have not tested it yet, but it should only require a little change in the SQLFromSelect function,  plus a few  more method signature needs the extra parameters added with an empty string default value.

NEW PARAMS:

CommonTablesExpr selects the records to work with instead of the whole table, therefor it must have a table with IDs of the records.
IDTableName is the name of this table.

WITH RECURSIVE
  ...
  IDTable(ID) as (...)

function SQLFromSelect(const TableName, Select, Where, SimpleFields, CommonTablesExpr, IDTableName: RawUTF8): RawUTF8;
begin
  if Select='*' then
     // don't send BLOB values to query: retrieve all other fields
    result := 'SELECT '+SimpleFields  else
    result := 'SELECT '+Select;

  // << CODE CHANGE START >>
  if CommonTablesExpr<> ''   then
    result := CommonTablesExpr+' '+result+' FROM '+TableName+' JOIN '+ IDTableName + ' ON '+TableName+'.ID='+IDTableName+'.ID '+SQLFromWhere(Where)
  else
  // << CODE CHANGE END >>
    result := result+' FROM '+TableName+SQLFromWhere(Where);
end;

If having two ID fields in the query is a problem for some db engines it may need an other name in the IDTable (eg _ID, ID_ ... ), or an other parameter for the ID field name, or use the tablename.fieldname syntax.   There is no proper validation for the new params yet.

Last edited by Leslie7 (2015-07-09 10:55:29)

Leslie7

Member

Registered: 2015-06-25

Posts: 248

Re: Recursive - ORM

I mean no rushing at all, but I need to make a choice now how to proceed  and your opinion about this suggestion would be helpful.

One way is to  implement this how I see fit based on my  limited knowledge of mORMots inner workings, but it would mean branching the frameworks source. Which is not the preferable choice.

Or if it requires only so little change in the source as it seems you could commit the proposed changes the way you see fit.