JOINed query

Thomas-Acia · 2015-07-02 15:18:52

Hi,

I have to create a query between many tables in order to create a ticket (ticket like McDonald's for example). I must take a lot of informations in these tables. So I have to create joins. I read these parts of documentation : http://synopse.info/files/html/Synopse% … ml#TITL_70 and synopse.info/files/html/Synopse mORMot … l#TITL_129 but I didn't understand all.

Firstly, for the cardinalities, I'll have a number of articles on my tickets, but this number may change every ticket. So I have to declare a number of attributs in my virtual table ? Like this :

property FirstOne: TSQLMyFileInfo read FFirstOne write FFirstOne;
property SecondOne: TSQLMyFileInfo read FSecondOne write FSecondOne;

Or only one time ?

Secondly, the join must be created with the ID of the table I want to join or with the attribut ?

  MyFile := TSQLMyFile.CreateJoined(Client,aMyFileID);

I didn't find a sample with an example of joins.

For now, I have this :

 { Table cat_passself }
  TSQLcat_passself = class(TSQLRecord)
  private
    fid_passage: Integer;
    fdate_passself, flibelle_repas, fnumero_ticket, foperateur, fprix_repas, fprix_repas_recalcul,
    fsubvention_fixe, fsubvention_cent : RawUTF8;
  published
    property id_passage: Integer Read fid_passage Write fid_passage;
    property date_passself: RawUTF8 Read fdate_passself Write fdate_passself;
    property libelle_repas: RawUTF8 Read flibelle_repas Write flibelle_repas;
    property numero_ticket: RawUTF8 Read fnumero_ticket Write fnumero_ticket;
    property operateur: RawUTF8 Read foperateur Write foperateur;
    property prix_repas: RawUTF8 Read fprix_repas Write fprix_repas;
    property prix_repas_recalcul: RawUTF8 Read fprix_repas_recalcul Write fprix_repas_recalcul;
    property subvention_fixe: RawUTF8 Read fsubvention_fixe Write fsubvention_fixe;
    property subvention_cent: RawUTF8 Read fsubvention_cent Write fsubvention_cent;
  end;

And in few tables :

   { Table com_personne }
  TSQLcom_personne = class(TSQLRecord)
  private
    fid_entite: Integer;
    fnom, fprenom1: RawUTF8;
    fcat_passself: TSQLcat_passself;
  published
    property id_entite: Integer Read fid_entite Write fid_entite;
    property nom: RawUTF8 Read fnom Write fnom;
    property prenom1: RawUTF8 Read fprenom1 Write fprenom1;
    property cat_passself: TSQLcat_passself read fcat_passself write fcat_passself;
  public
  end;

with the join with "cat_passself"

I only declared attributs in my virtual tables. I didn't create joins for now.

Thank you !

Last edited by Thomas-Acia (2015-07-02 15:21:19)

ab · 2015-07-02 16:48:12

For such an information to write, I would not use the relational model, but a plain list of ticket entries, e.g. using a TCollection or a T*ObjArray of records.
See http://synopse.info/files/html/Synopse% … ml#TITL_29

The RDBMS concept is making thinks worse than necessary, IMHO.

Thomas-Acia · 2015-07-08 12:33:26

Hi,

I decided to use views with the ORM.

Now I have another question. Is possble to compare two values with a "IF" section in Mustache ??
(Example : {{#qte > 1}} {{qte}} {{/qte}} )

Thank you

ab · 2015-07-08 13:15:57

Try with the latest version of Mustache:

{{#if qte>1}}{{qte}}{{/if}}

See http://synopse.info/files/html/api-1.18 … ANDARDLIST

Thomas-Acia · 2015-07-09 07:04:49

ab wrote:

Try with the latest version of Mustache:
{{#if qte>1}}{{qte}}{{/if}}
See http://synopse.info/files/html/api-1.18 … ANDARDLIST

Ok, I don't have the latest version ! Thank you !

Thomas-Acia · 2015-07-09 09:01:06

I just updated my sources. I have a problem with this code :

STicket.GetRestServer.ServiceRegister(TServiceSignaling,[TypeInfo(ITicketSignaling)],sicShared);

http://www.noelshack.com/2015-28-143643 … -titre.png

ab · 2015-07-09 09:12:38

How is your ITicketSignaling.ServerSetDB() method defined?

The error is pretty clear IMHO: the "wrapper" parameter of this method is not known.
Or perhaps even not compatible with interface-based services, which transmit values per representation, so need to be serializable as JSON.
See http://synopse.info/files/html/Synopse% … l#TITL_154

Thomas-Acia · 2015-07-09 09:15:58

ab wrote:

How is your ITicketSignaling.ServerSetDB() method defined?
The error is pretty clear IMHO: the "wrapper" parameter of this method is not known.
Or perhaps even not compatible with interface-based services, which transmit values per representation, so need to be serializable as JSON.
See http://synopse.info/files/html/Synopse% … l#TITL_154

procedure TServiceSignaling.ServerSetDB(Wrapper: TACIASQLWrapper);
begin
  DB:=Wrapper.Rest;
  TSQLRestServerDB(DB).DB.LockingMode:=lmExclusive;
end;

Thomas-Acia · 2015-07-09 09:18:17

A compatiblity problem would be strange because it worked before my update

Thomas-Acia · 2015-07-09 09:55:00

I have no idea. I looked my code and the mORMot unit but I found nothing

ab · 2015-07-09 10:27:33

I do not understand how it may remotely work at all.

In the same process, it may work, but remotely, via JSON, transmitting a TACIASQLWrapper instance and accessing its TACIASQLWrapper.Rest property won't just work.

So it may be allowed before, but the new stronger policy is much better.

Thomas-Acia · 2015-07-09 13:58:30

ERROR FOUND !

Line 45518 in mORMot.pas, we have to add oTSQLRecord :

if ClassHasPublishedFields(ClassType) or
       (JSONObject(ClassType,IsObjCustomIndex,[cpRead,cpWrite]) in
         [{$ifndef LVCL}oCollection,{$endif}oObjectList,oUtfs,oStrings,
          [color=#FF0000]oSQLRecord[/color],oException,oCustom]) then

You deleted this the 18th of June at 11:03.

ab · 2015-07-09 20:18:06

This is not an error.

This is how it is required to work.

You need published fields for the serialization to work.

mORMot Open Source

#1 2015-07-02 15:18:52

JOINed query

#2 2015-07-02 16:48:12

Re: JOINed query

#3 2015-07-08 12:33:26

Re: JOINed query

#4 2015-07-08 13:15:57

Re: JOINed query

#5 2015-07-09 07:04:49

Re: JOINed query

#6 2015-07-09 09:01:06

Re: JOINed query

#7 2015-07-09 09:12:38

Re: JOINed query

#8 2015-07-09 09:15:58

Re: JOINed query

#9 2015-07-09 09:18:17

Re: JOINed query

#10 2015-07-09 09:55:00

Re: JOINed query

#11 2015-07-09 10:27:33

Re: JOINed query

#12 2015-07-09 13:58:30

Re: JOINed query

#13 2015-07-09 20:18:06

Re: JOINed query

Board footer