I have a many-to-many relationship (with an intermediate table) between two tables, and when a user queries one of the two tables, I want to restrict them to only seeing the records that relate to themselves.
[Entity, Automapping]
TUserAccount = class( TORMEntity )
private
[Id( 'FID', TIdGenerator.IdentityOrSequence )]
FID: int64;
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FUseraccount')]
FUserKlasses: TList<TKlasseUser>;
public
// fields
property ID: int64 read FID write FID;
property UserKlasses: TList<TKlasseUser> read FUserKlasses;
end;
[Entity, Automapping]
TKlasse = class( TORMEntity )
private
[Id( 'FID', TIdGenerator.IdentityOrSequence )]
FID: int64;
FKlassename: string;
[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FKlasse')]
FKlasseUsers: TList<TKlasseUser>;
public
// fields
property ID: int64 read FID write FID;
property KlasseName: string read FKlasseName write FKlasseName;
property KlasseUsers: TList<TKlasseUser> read FKlasseUsers write FKlasseUsers;
end;
[Entity, Automapping]
TKlasseUser = class( TORMEntity )
private
[Id( 'FID', TIdGenerator.IdentityOrSequence )]
FID: int64;
[Association([], CascadeTypeAllButRemove)]
[JoinColumn('USER_ID', [TColumnProp.Required])]
FUserAccount: TUserAccount;
[Association([], CascadeTypeAllButRemove)]
[JoinColumn('KLASSE_ID', [TColumnProp.Required])]
FKlasse: TKlasse;
public
property ID: int64 read FID write FID;
// Actual linkage
property Klasse: TKlasse read FKlasse write FKlasse;
property User: TUserAccount read FUserAccount write FUserAccount;
end;
Then in the authentication code (called by subscribing to events in TXDataModuleEvents) I've tried this (amongst many other attempts...)
var
iiID: int64;
begin
result := true;
if ( not AuthorizeAdminManager ) then begin
iiID := AUser.Claims[ JWT_CLAIM_USER_ID ].AsInt64;
// not a manager, only allowed to see own klasse, i.e. records which have a link through the klasseuser table
AArgs.Criteria
.CreateAlias( 'KlasseUsers', 'ku' )
.Where( TLinq.Eq( 'ku.UserID', iiID ) )
end;
end;
but it says it can't find the UserID field of KlasseUser.
I'd love to know how to do this properly. So, the objective is to ensure that if a user tries to list the Klasse table, they only get to see the items that have their User's ID in the link table...
Thanks!