Strugges with Associations

[Niels_Tjornhoj-Thomsen]

I really struggle with understanding how associations work in Aurelius.

I have a many-to-many relationship between two objects, using an intermediary object (thinking in SQL which could be part of my struggles).

  // User
  [Entity, Automapping]
  TUserAccount = class
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    FLastName: Nullable<string>;
    FFirstName: Nullable<string>;
  public
    property ID: int64 read FID write FID;
    property Firstname: Nullable<string> read FFirstName write FFirstName;
    property Lastname: Nullable<string> read FLastName write FLastName;
  end;

  // Tenant
  [Entity, Automapping]
  TTenant = class
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    // Tenant name must be unique across the system
    [Column( 'TENANTNAME', [TColumnProp.Unique], 64 )]
    FTenantname: string;
  public
    property ID: int64 read FID write FID;
    property TenantName: string read FTenantName write FTenantName;
  end;

  // Link user to tenancy - user can belong to multiple tenancies, tenancies have many users
  [Entity, Automapping]
  TTenantUser = class
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    [Association([], CascadeTypeAllRemoveOrphan)]
    [JoinColumn('USER_ID', [TColumnProp.Required])]
    FUseraccount: TUserAccount;
    [Association([], CascadeTypeAllRemoveOrphan)]
    [JoinColumn('TENANT_ID', [TColumnProp.Required])]
    FTenant: TTenant;
  public
    property ID: int64 read FID write FID;
    // Actual linkage
    property UserAccount: TUserAccount read FUserAccount write FUserAccount;
    property Tenant: TTenant read FTenant write FTenant;
  end;

What I expect to happen is that if I create a record joining a tenant to a user account, and then try to delete the user account, the TTenantUser link table record would get deleted too. Instead, I just get a foreign key violation (Postman):

{
    "error": {
        "code": "UniError",
        "message": "update or delete on table \"user_account\" violates foreign key constraint \"fk_tenant_user_user_account_user_id\" on table \"tenant_user\""
    }
}

I thought that using CascadeTypeAllRemoveOrphan would have the desired effect.

Where am I going wrong?

[wlandgraf (Wagner Landgraf)]

CascadeTypeRemoveOrphan applies to lists. For that to work, you have to create a list of TUserTenant in your TUserAccount class:

[ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FUseraccount')]
property TenantUsers: TList<TTenantUser> read GetTenantUsers;

More info: Many to many with Aurelius

[Niels_Tjornhoj-Thomsen]

Thanks Wagner,

I looked at your recommended post for many-to-many relationships, but that throws up another curve ball.

How do I structure my ORM units in order to make that compile in Delphi? I prefer to keep all my ORM classes in separate units but because the ORM class declarations have to be in the interface section of the unit, you end up with circular unit references (e.g. the TTenantUser unit has to refer to both the TUserAccount unit and the TTenant unit, which both have to refer to the TTenantUser unit).

Is the only way to drop all structure and put all the ORM units into one? Or is there a way to put the class definitions in the implementation part of the units?

[wlandgraf (Wagner Landgraf)]

That's unfortunately a Delphi limitation. Circular references must be kept in the same unit, and there isn't much we can do about it.

[Niels_Tjornhoj-Thomsen]

I understand. That'll have to do then. It's only code after all, not art!