Where clause fails to get the right Table Alias when an ORM entity is referenced twice as an associate of another Entity

Issue #293 new
Todd Flora
created an issue

When the following entity class is queried and the second reference to the associate entity is used in a criteria statement, the ORM picks the wrong table alias for the join and fails to return data.

Entity Files

Controller Associate

  [Table('CONTROLLER')]
  [Entity]
  [Sequence('Select GetSid() as Sid from dual')]
  TController = class
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;

    [Column('CONTROLLER_NO',[],5,0)]
    FControllerNo: Nullable<Int16>;

    [Column('CONTROLLER_NAME',[],15)]
    FControllerName: Nullable<string>;

  protected
  public
    property Sid: Int64 read FSid write FSid;
    property ControllerNo: Nullable<Int16> read FControllerNo write FControllerNo;
    property ControllerName: Nullable<string> read FControllerName write FControllerName;
  end;

Remote Connection (References Controller Twice)

  [Table('REMOTE_CONNECTION')]
  [Entity]
  [Sequence('Select GetSid() as Sid from dual')]
  TRemoteConnection = class
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;


    [Column('REMOTE_CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    FRemoteControllerSid: Int64;


    [Column('LOCAL_CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    FLocalControllerSid: Int64;

    [Column('CONNECTION_TYPE',[cpRequired,cpNotNull],1,0)]
    FConnectionType: TServerMode;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FLocalControllerSid')]
    FLocalController: TController;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FRemoteControllerSid')]
    FRemoteController: TController;

  protected
    function GetLocalControllerName: Nullable<string>;
    procedure SetLocalControllerName(const Value: Nullable<string>);    
    function GetLocalAddress:Nullable<string>;
    function GetRemoteControllerName: Nullable<string>;
    procedure SetRemoteControllerName(const Value: Nullable<string>);
    function GetRemoteAddress:Nullable<string>;
    function GetConnectionName: string;
    procedure SetConnectionName(const Value: string);
  public
    property Sid: Int64 read FSid write FSid;
    property ConnectionName: string read GetConnectionName write SetConnectionName;
    property RemoteControllerSid: Int64 read FRemoteControllerSid write FRemoteControllerSid;
    property ConnectionType: TServerMode read FConnectionType write FConnectionType;
    property LocalControllerSid: Int64 read FLocalControllerSid write FLocalControllerSid;

    property LocalControllerName: Nullable<string> read GetLocalControllerName write SetLocalControllerName;

    property RemoteControllerName: Nullable<string> read GetRemoteControllerName write SetRemoteControllerName;

  end;

Criteria that fails

var
  RemoteController : IProperty;
  Column ColumnAttribute;
begin
  //Fails if issued against the Remote Controller Name (second controller referenced)  
  ...  //Not all code provided for brevity
  Fld := <Get ControllerName Field from Controller Entity using Rtti>
  if not Fld.TryGetCustomAttribute<ColumnAttribute>(Column) then
      raise Exception.Create('Field does not exist');
  RemoteController := TProperty.Create(Column.ColumnName, TController);  
  Criteria.Add(RemoteController.Eq('SomeName'));
  //At this point we have lost information that specifies the second controller table alias should be used
end;

Primary Issue

When the above code is used to construct a criteria to select a remote connection based on the RemoteControllerName we loose the fact that it is the second instance of the controller associate that is being queried and the FindTable method of the Spring.Persistence.Sql.Commands just looks for the first table referenced with the table name associated with TController.

Seems like there needs to be a way to link the table alias with the instance of the table referenced in the Entity.

Here is the offending code as it just picks the first table and hence the first alias that matches the table name.

function TSelectCommand.FindTable(entityClass: TClass): TSQLTable;
var
  tableName: string;
  currentTable: TSQLTable;
begin
  if entityClass = nil then
    Exit(fTable);

  tableName := TEntityCache.Get(entityClass).EntityTable.TableName;

  for currentTable in fTables do
    if SameText(currentTable.NameWithoutSchema, tableName) then
      Exit(currentTable);
  Result := fTable;
end;

Comments (11)

  1. Todd Flora reporter

    I have attached a test for this to show the issue. Stefan we need to resolve this soon as we are running into this issue on a regular basis. I will start on fixing it, but would appreciate some feedback from you.

    Currently the IProperty takes a propertyName (really the underlying field name) and a Class type (nil for the primary entity, or if the property is for a ManyToOne the ManyToOne class). I was thinking of rather than passing the TClass that we pass a TMemberType, That way we can associate the actual member with the ManyToOne, Another possibility is to pass the ManyToOne attribute.

    Let me know if this is something you can work on soon, or if not, then please give me some direction on how you see it being resolved and I will take a crack at it.

    Thanks.

  2. Todd Flora reporter

    BTW drop the above two files into the Spring4d/Marshmallow/Test folder and run the When_Two_ManyToOne_Instances_Of_Same_Associate test. Notice that the first part of the test succeeds (against the first instance of the TControllerAssociate) but the second test does not succeed as there is no actual way to specify which TControllerAssociate to choose.

  3. Todd Flora reporter

    Stefan,

    I have a possible solution for this issue passing the Member and getting the name from it. Using the Name in TSelectCommand to store each table in an IDictionary rather than an IList with the member name as the key. Then find table will look by member name when provided or otherwise it will still lookup by table name if not provided. I have kept the Property constructor for TClass so that it is backward compatible. I would like to provide a pull request if you think this is something you can incorporate into Spring4d Persistence.

    Let me know.

    Todd

  4. Todd Flora reporter

    I do have a solution for this one if you'll tell me how to login to the repo so I can create a Pull Request I would be glad to share the code with you for consideration to be merged into master at some point

  5. Todd Flora reporter

    I have created a new branch based on Develop called CriteriaMemberPath and have made my changes here. When I use Sourcetree and attempt create a pull request (which first attempts to push my changes) I get a unauthorized error

    fatal: Authentication failed for https://bitbucket.org/sglienke/spring4d.git/

    Do I need access rights to your repo in order to push this? Or maybe I am doing something wrong.

    Thanks,

  6. Log in to comment