mORMot Open Source

Martin Sedgewick

Member

Registered: 2013-01-23

Posts: 12

JOIN questions

Hi Arnaud,

Great work on the HTML documentation, it is a great resource.

I am trying to figure out how to do some things in the framework and I am not sure what is the best approach.

1) Multi table joins

In this example I have 3 tables

TSQLCountry = class( TSQLRecord )
  private
    fname: RawUTF8;
  published
    property Name: RawUTF8 read fname write fname;
  end;

  TSQLContactDetails = class( TSQLRecord )
  private
    fCountry: TSQLCountry;
  published
    property Country: TSQLCountry read fCountry write fCOuntry;
  end;

  TSQLStudent = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fContactDetails: TSQLContactDetails;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property ContactDetails: TSQLContactDetails read fContactDetails write fContactDetails;
  end;

  What I want to do is get all students where the country is X. I can write SQL as follows: (SQL MAY BE WRONG, but you get the point )

  SELECT S.*
  FROM 
    STUDENTS S 
    JOIN CONTACTDETAILS CD ON (CD.ID = S.CONTACTDETAILSID)
    JOIN COUNTRYS C ON (C.ID = CD.COUNTRYID)
  WHERE
    C.Name = 'Scotland'

  I looked at something like CreateAndFillPrepareMany where it allows multi-table joins, but this is only for use in many-to-many relationships.

  Something like

  student.CreateAndFillPrepareJoined( Client, 'ContactDetails.Country.Name = ?', [], ['Scotland'] );

  Also, I would then want to put in where clauses for each table, so S.firstname LIKE ? AND C.Name = ?.

2) Please note that the code does have not a check in CreateAndFillPrepareMany if you dont have a many relationship, it writes incorrect SQL. I think it should write correct SQL or raise an exception "No many-to-many relationships defined on this record". To try this, call the method with a TSQLRecord with no TSQLRecordMany, like my TSQLStudent. The SQL has a where statement which starts " and (...)". This is because of the following line:

aSQL := aSQL+' and ('+FormatUTF8(pointer(aSQLWhere),aParamsSQLJoin,aBoundsSQLJoin)+')';

3) I then want to start getting students from a join across both One-To-One relationships AND Many-To-Many relationships. As far as I can tell, I am restricted to one or the other in either CreateAndFillPrepareMany or CreateAndFillPrepareJoined. Is this correct?

4) What is your opinion on the best way to retrieve data across multiple tables? I could not see anything in the documentation or examples for this scenario.

If, from the example above (Students, ContactDetails, Countries) I wanted to write the following SQL:

  SELECT S.FirstName, C.Name    <---returning fields across tables
  FROM 
    STUDENTS S 
    JOIN CONTACTDETAILS CD ON (CD.ID = S.CONTACTDETAILSID)
    JOIN COUNTRYS C ON (C.ID = CD.COUNTRYID)
  WHERE
    C.Name = 'Scotland'

  I have no idea how I would ask for this in code.

  Would I loop through all students, look at their contactdetails and then look at their countries? This may be ok for a couple of objects, but as soon as more table joins are added and the number of records increases, this would not be efficient.

  Most of the SQL in my application consists of querying data across many tables and while I can see the ORM being useful for some things, reading data like this doesnt seem supported.

  Am I best, in this case, to use a SQL query and bypass the ORM? 

 

  An approach I would think might work: it might be a list which shows all students who live in Scotland. I would possibly write a new TSQLStudentWithCountry, which is not a real table, but one which is purely a read-only mapping. You would not really want to write this back to the database.

TSQLStudentWithCountry = class(TSQLReadOnlyRecord)  <--- Not sure if you have a way of making a record read only. But something like this...
  private
    fFirstName: RawUTF8;
    fContactDetails: TSQLContactDetails;
    fCountryName: RawUTF8;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property ContactDetails: TSQLContactDetails read fContactDetails write fContactDetails;
    property CountryName: RawUTF8 read fCountryName write fCountryName;
  end;


  model := TSQLModel.Create( [ TSQLCountry, TSQLContactDetails, TSQLStudent, TSQLStudentWithCountry ] );

  Model.Props[TSQLStudentsWithCountry].ExternalDB.
  MapField('CountryName','ContactDetails.Country.Name');

  I have intentionally made this across multiple tables, as that is what I would use it for, and not just a StudentAndAddress class, which would be similar, and can currently be done with the CreateAndFillPrepareJoined method.
  I appreciate this is maybe not possible. I used Entity Framework many years ago and I remember you could do something like this in the mappings.
 

I hope these make sense. Thanks for any help.

Last edited by Martin Sedgewick (2014-11-10 01:58:54)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: JOIN questions

1) JOINed query is supported only with one level yet.

2) I've added a check, and an exception in such cases.
See http://synopse.info/fossil/info/9c5dca38c1

3) Yes it is either the first, or the 2nd option.

4) You are re-inventinng data sharding. But why use a "read-only" fake table?
Just store the data twice!
For instance, a Country is better stored as TSQLContactDetails.Country and as TSQLContactDetails.CountryName.

Why not use data sharding, as explained by the doc and shown in the "30 MVC Server" sample?
See http://synopse.info/files/html/Synopse% … ml#TITL_29
Complex joined query are still too RDBMS related.
Do not think SQL, but objects!
http://synopse.info/files/html/Synopse% … #TITLE_111

IMHO the best is to balance objects with un-normalization of data, and guess how it will work on a RDBMS.
One of the issue with ORM is that when you start to create complex queries with multiple JOINed and such, the SQL starts to be complicated, difficult to test and optimize.
We put the bare minimum syntax, to help switching the database structure mind of the user, and start to think about un-normalization.

Of course, we may add other methods and features to the ORM!
The API is increasing little by little.
Your proposal of a specific "read only" table used for complex JOINed queries, with proper mapping, could be a good idea.
But IMHO you can get almost all thinks done with our basic set of commands, just by using data sharding, and a proper data model.
Later on, you could switch to a non RDBMS backend (like MongoDB), or a remote DB (i.e. store your data in a separate shared mORMot server), with ease.