mORMot Open Source

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.