mORMot Open Source

Celso

Member

Registered: 2013-05-14

Posts: 55

ExecuteList

Do I need to do a search in the database, on the client side.
I am using as well:

frmDM.fRestClient.ExecuteList([TSQLCPU_USER,TSQLCPU_GROUP],
                                             'SELECT CPU_USER.ROWID,CPU_USER.*,' +
                                             'CPU_GROUP.NAME AS GROUP_NAME ' +
                                             'FROM CPU_USER ' +
                                             'INNER JOIN CPU_GROUP ON (CPU_USER.IDGROUP = CPU_GROUP.ROWID) ' +
                                             'WHERE CPU_USER.IDUSER <> 9999 ORDER BY CPU_USER.IDUSER');

This is the best way or is there another way?
I am with this doubt because, in some other questioning, I have read that the ExecutList command should not be used on the client side.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ExecuteList

You could use the JOINed query at ORM level.
See TSQLRecord.CreateAndFillPrepareJoined() constructors.
It will create a joined query as defined in TSQLModelRecordPropertiesSQL.SelectAllJoined.
You can add additional WHERE clause using the aFormatSQLJoin parameter of this constructor.

If this auto-generated SQL is not enough, you may just use two ORM queries: one to retrieve  TSQLCPU_USER content, then another with TSQLCPU_GROUP to retrieve the group name.
You may even enable ORM cache for the TSQLCPU_GROUP table, so that getting the TSQLCPU_GROUP content by ID would be immediate.

If you want to write your own tuned SQL, you should better not use it on the client side.
Write a simple "repository" interface-based service on the server side, running your query, and returning the information.

Also consider using "data sharing" pattern, i.e. storing the group name within TSQLCPU_USER, as NoSQL databases would do.
If this request is to be run very often, it would be very beneficial.

Celso

Member

Registered: 2013-05-14

Posts: 55

Re: ExecuteList

Thanks.

Celso

Member

Registered: 2013-05-14

Posts: 55

Re: ExecuteList

I can't use the CreateAndFillPrepareJoined. You would have an example?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ExecuteList

Ensure you read "Automatic instantiation and JOINed query" paragraph in the latest 1.18 SAD pdf.

Celso

Member

Registered: 2013-05-14

Posts: 55

Re: ExecuteList

ab, I have two tables in my database. Country and states. A country can have multiple states.
I have the following TSQLRecord:

TSQLCPU_COUNTRY = class(TSQLRecord)
  private
    fIDCOUNTRY : Integer;
    fNAME      : RawUTF8;
  published
    property IDCOUNTRY : Integer read fIDCOUNTRY write fIDCOUNTRY;
    property NAME      : RawUTF8 read fNAME      write fNAME;
  end;

TSQLCPU_STATE = class(TSQLRecord)
  private
    fIDCOUNTRY   : TSQLCPU_COUNTRY;
    //fIDCOUNTRY : Integer;
    fIDUF      : RawUTF8;
    fNAME      : RawUTF8;
    fCODUF     : Integer;
  published
    property IDCOUNTRY   : TSQLCPU_COUNTRY read fIDCOUNTRY write fIDCOUNTRY;
    property IDUF      : RawUTF8 read fIDUF      write fIDUF;
    property NAME      : RawUTF8 read fNAME      write fNAME;
    property CODUF     : Integer read fCODUF     write fCODUF;
  end;

I used CreateAndFillPrepareJoined:

v_cpucountry := TSQLCPU_STATE.CreateAndFillPrepareJoined(frmDM.fRestClient,'',[],[]);
dsCountry.DataSet := TSynSQLTableDataSet.Create(Self,v_cpucountry.FillTable);

It's working.  All information is in a single dataset.

How can I have two dataset's? In my form first I present only the countries. Then each selected country, shows the states.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: ExecuteList

Why not just make two queries?