When association is ManyToOne relationship with both the primary class and also another associate FindAll fails with an exception

[Todd Flora]

If I have a Class that is a ManyToOne relationship with both the main class and also one of the other associated classes, the mapping of Resultset to class properties fails with

Exception Class EORMColumnNotFound with Message 'Column t2$sbs_no not found in the resultset'

It just so happens that in the primary result set the sbs_no field is associated with the t4 table alias, but in the other associate class sbs_no is associated with the t2 alias. When the main class is being mapped it should look for the sbs_no field as t4 not t2.

If I remove the Subsidiary associate containing sbs_no out of either the main class or the associate class the mapping works.

Here is the class definition

ASSOCIATE CLASS FOUND IN BOTH MAIN CLASS AND OTHER ASSOCIATE WHERE THE SBS_NO FIELD RESIDES

  [Entity]
  [Table('SUBSIDIARY')]
  TSubsidiaryAssociate = class
  private
    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;
    [Column('SBS_NO',[cpRequired,cpNotNull],5,0)]
    FSbsNo: Nullable<Int16>;
    [Column('SBS_NAME',[],15)]
    FSbsName: Nullable<string>;
  public
    property Sid: Int64 read FSid write FSid;
    property SbsNo: Nullable<Int16> read FSbsNo write FSbsNo;
    property SbsName: Nullable<string> read FSbsName write FSbsName;
  end;

OTHER ASSOCIATE CLASS

Notice there are only two ManyToOne relations of which subsidiary is numnber 2 and likely gets the t2 table alias.

[Entity]
  [NoInherited]
  [Sequence('Select GetSid() as Sid from dual')]
  [Table('TAX_CODE_RULE')]
  TTaxCodeRule = class(TModelBase)
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;
    .
    .
    .
    [Column('SBS_SID',[],19,0)]
    FSbsSid: Nullable<Int64>;

    [Column('TAX_AREA_SID',[],19,0)]
    FTaxAreaSid: Nullable<Int64>;

    [ManyToOne(false, [ckCascadeAll], 'fTaxAreaSid')]
    FTaxArea : TTaxAreaAssociate;

    [ManyToOne(false, [ckCascadeAll], 'FSbsSid')]
    FSubsidiary : TSubsidiaryAssociate;

  protected
    procedure SetSid( Value : Int64);
    procedure SetSbsSid( Value : Nullable<Int64>);
    procedure SetTaxAreaSid( Value : Nullable<Int64>);
    function GetTaxAreaName: Nullable<string>;
    procedure SetTaxAreaName(const Value: Nullable<string>);
    function GetSbsName: Nullable<String>;
    function GetSbsNo: Nullable<Int16>;
    procedure SetSbsName(const Value: Nullable<String>);
    procedure SetSbsNo(const Value: Nullable<Int16>);

  public
    property Sid: Int64 read FSid write SetSid;
    .
    .
    .
    property SbsSid: Nullable<Int64> read FSbsSid write SetSbsSid;
    property TaxAreaSid: Nullable<Int64> read FTaxAreaSid write SetTaxAreaSid;
    property SbsNo: Nullable<Int16> read GetSbsNo write SetSbsNo;
    property SbsName: Nullable<string> read GetSbsName write SetSbsName;
    property TaxAreaName: Nullable<string> read GetTaxAreaName write SetTaxAreaName;
  end;

MAIN CLASS

Notice here that the subsidiary associate is the 4th in the list of associates and in the main query is labeled with the t4 alias. but when the mapping occurs it thinks that sbs_no should be t2$sbs_no and fails to find it in the resultset.

 [Entity]
  [NoInherited]
  [Sequence('Select GetSid() as Sid from dual')]
  [Table('TAX')]
  TTax = class(TModelBase)
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;
    .
    .
    .
    [Column('SBS_SID',[],19,0)]
    FSbsSid: Nullable<Int64>;

    [Column('TAX_AREA_SID',[],19,0)]
    FTaxAreaSid: Nullable<Int64>;

    [Column('TAX_CODE_SID',[],19,0)]
    FTaxCodeSid: Nullable<Int64>;

    [Column('TAX_CODE_RULE_SID',[],19,0)]
    FTaxCodeRuleSid: Nullable<Int64>;

    [ManyToOne(false, [ckCascadeAll], 'fTaxAreaSid')]
    FTaxArea : TTaxAreaAssociate;

    [ManyToOne(false, [ckCascadeAll], 'fTaxCodeSid')]
    FTaxCode : TTaxCodeAssociate;

    [ManyToOne(false, [ckCascadeAll], 'fTaxCodeRuleSid')]
    FTaxCodeRule : TTaxCodeRule;

    [ManyToOne(false, [ckCascadeAll], 'FSbsSid')]
    FSubsidiary : TSubsidiaryAssociate;

  protected
    procedure SetSid( Value : Int64);
    procedure SetSbsSid( Value : Nullable<Int64>);
    procedure SetTaxAreaSid( Value : Nullable<Int64>);
    procedure SetTaxCodeSid( Value : Nullable<Int64>);
    procedure SetTaxCodeRuleSid( Value : Nullable<Int64>);

    function GetSbsName: Nullable<String>;
    function GetSbsNo: Nullable<Int16>;
    procedure SetSbsName(const Value: Nullable<String>);
    procedure SetSbsNo(const Value: Nullable<Int16>);
    function GetTaxAreaName: Nullable<string>;
    procedure SetTaxAreaName(const Value: Nullable<string>);
    function GetTaxCode: Nullable<string>;
    function GetTaxName: Nullable<string>;
    procedure SetTaxCode(const Value: Nullable<string>);
    procedure SetTaxName(const Value: Nullable<string>);
  public
    constructor Create; override;
    destructor Destroy; override;
    property Sid: Int64 read FSid write SetSid;
    .
    .
    .
    property SbsSid: Nullable<Int64> read FSbsSid write SetSbsSid;
    property TaxAreaSid: Nullable<Int64> read FTaxAreaSid write SetTaxAreaSid;
    property TaxCodeSid: Nullable<Int64> read FTaxCodeSid write SetTaxCodeSid;
    property TaxCodeRuleSid: Nullable<Int64> read FTaxCodeRuleSid write SetTaxCodeRuleSid;
    property SbsNo: Nullable<Int16> read GetSbsNo write SetSbsNo;
    property SbsName: Nullable<string> read GetSbsName write SetSbsName;
    property TaxAreaName: Nullable<string> read GetTaxAreaName write SetTaxAreaName;
    property TaxCode: Nullable<string> read GetTaxCode write SetTaxCode;
    property TaxName: Nullable<string> read GetTaxName write SetTaxName;
  end;

[Stefan Glienke]

I get the idea but how awesome would it be if you had provided a simple failing testcase that I could add to our integration tests.

[Todd Flora]

Let me try and do that.

[Todd Flora]

Stefan, I have a solution to this problem along with another issue we uncovered when an associate has an associate itself. What we are calling Recursive associates. In the issue above you will notice that TTax has an associate of TTaxRule, TTaxRule as an associate of TSubsidiary. Both the query and the mapping were failing on this situation because of the following conditions:

• The Sql Generation Code was not calling SetAssociations recursively.
• The TAbstractSessiohn.TRowMapperInternal.Create constructor was making a recursive call but not passing on the index, resetting it to 1 each time.

We have resolved these two issues in the attached files. Please consider integrating these changes into the latest Marshmallow branch. Please note changes to the following methods:

1. Spring.Persistence.Core.AbstractSession.pas ###

TRowMapperInternal Constructor
TAbstractSession.MapEntityFromResultSetRow
TAbstractSession.MapEntitiesFromResultSet

2. Spring.Persistence.Core.EntityWrapper.pas (Remove extra Set line)

TEntityWrapper.Create

3. Spring.Persistence.Sql.Commands

TSelectCommand.SetAssociations
TSelectCommand.Create

4. Spring.Persistence.Sql.Commands.Select

TSelectExecutor.Build

Thanks!

[Todd Flora]

Spring.Persistence.SQL.Commands files

[Todd Flora]

Core files

[Stefan Glienke]

fixed #134

→ <<cset 0a8d1b0c8876>>