mORMot Open Source

reddwarf

Member

Registered: 2010-06-28

Posts: 40

Website

many-to-many relation and retrieving a list of Dest objects

Hello Arnaud,

I am having trouble with many-to-many relation (TSQLUser = Source, TSQLApplication = Dest):

  TSQLUser = class;
  TSQLApplication = class;
  TSQLUserApplication = class;

  TSQLUser = class(TSQLRecord)
  private
    fFirstName: DBString;
    fLastName: DBString;

    fApplicationList: TSQLUserApplication;

  public type
    Consts = class(Cols)
    public const
      PluralLC = 'users';
      SingularLC = 'user';

      FirstName = 'FirstName';
      LastName = 'LastName';
    end;
  published
    property FirstName: DBString read fFirstName write fFirstName;
    property LastName: DBString read fLastName write fLastName;

    property ApplicationList: TSQLUserApplication read fApplicationList;
  end;

  TSQLApplication = class(TSQLRecord)
  private
    fCompanyName: DBString;

    fUserList: TSQLUserApplication;
  public type
    Consts = class(Cols)
    public const
      PluralLC = 'applications';
      SingularLC = 'application';

      CompanyName = 'CompanyName';
    end;
  published
    property CompanyName: DBString read fCompanyName write fCompanyName;

    property UserList: TSQLUserApplication read fUserList;
  end;

  TSQLUserApplication = class(TSQLRecordMany)
  public type
    Consts = class(Cols)
    public const
      SourceUser = 'Source';
      DestApplication = 'Dest';
    end;
  private
    fSource: TSQLUser;
    fDest: TSQLApplication;
  published
    property Source: TSQLUser read fSource write fSource;
    property Dest: TSQLApplication read fDest write fDest;
  end;

I want to get a list of applications linked with a user. I do it by this code:

  Result := TSQLApplication.CreateAndFillPrepareMany(
    fDBConnection,
    PUTF8Char('(%.%=?)'),
    [TSQLUserApplication.SQLTableName, TSQLUserApplication.Consts.SourceUser],
    [myUserId]
    );

However, I am getting an assertion failure in TSQLRecord.FillPrepareMany on this line:

      Assert((fRecordManySourceProp.ObjectClass=PClass(self)^)
         and (fRecordManyDestProp.ObjectClass<>nil));

Could you please tell me what I am doing wrong and point me to the correct direction?

BTW, I'll need also to get a list of users linked with an application.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: many-to-many relation and retrieving a list of Dest objects

BTW CreateAndFillPrepareMany() does not need the PUTF8Char(...) any more.
It expects a aFormatSQLJoin: RawUTF8 parameter, not a PUTF8Char.

What are the fRecordManySourceProp.ObjectClass and fRecordManyDestProp.ObjectClass contents, as shown by the debugger?
I guess that your are using the same TSQLUserApplication in both TSQLUser and TSQLApplication classes, but in fact, only TSQLUser would allow this use, since TSQLUserApplication.Source should be the class which uses it.

reddwarf

Member

Registered: 2010-06-28

Posts: 40

Website

Re: many-to-many relation and retrieving a list of Dest objects

Thank you very much for your fast answer!

You are right, when I try to get a list of users with a linked application, it works fine:

  Result := TSQLUser.CreateAndFillPrepareMany(
    fDBConnection,
    PUTF8Char('(%.%=?)'),
    [TSQLUserApplication.SQLTableName, TSQLUserApplication.Consts.DestApplication],
    [fApplicationId]
    );

But I need it the other way, too.

+ I don't need mORMot to handle it automatically, if it's too much work. It would be OK for me if I could add a custom table JOIN definition to CreateAndFillPrepare (like you have e.g. the custom WHERE clause).

(+ Regarding PUTF8Char: I am probably not using the latest version of mORMot. I'll update ASAP.)

The requested information:
fRecordManySourceProp.ObjectClass.ClassName = TSQLUser
fRecordManyDestProp.ObjectClass.ClassName = TSQLApplication
That means that fRecordManySourceProp.ObjectClass.ClassName=PClass(self)^ is false (self is TSQLApplication).

Last edited by reddwarf (2015-02-16 13:46:24)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: many-to-many relation and retrieving a list of Dest objects

Is not FillManyFromDest() what you need?

reddwarf

Member

Registered: 2010-06-28

Posts: 40

Website

Re: many-to-many relation and retrieving a list of Dest objects

Thanks a lot for your answers, Arnauld!

I read all the information around TSQLRecordMany, but I must be dumb

I use this code:

  xUserApp := TSQLUserApplication.Create;
  xUserApp.FillMany(
    DBConnection,
    UserId,
    MyWhere);

  while xUserApp.FillOne do
  begin
    xUserApp.Dest.DoSomething;
  end;

The xUserApp.Dest contains the Integer value of ID -> how do I get the Dest object quickly and easily?

Last edited by reddwarf (2015-02-18 17:36:20)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: many-to-many relation and retrieving a list of Dest objects

AFAIR there is no JOINed method for this.

Since I'm now using mostly denormalized data (in DDD aggregates), I'm not very familiar with TSQLRecordMany any more...

reddwarf

Member

Registered: 2010-06-28

Posts: 40

Website

Re: many-to-many relation and retrieving a list of Dest objects

Thanks Arnauld, I'll try to add this functionality by myself and if I succeed, I'll send you the modifications back!