One-to-one property and EagerFetch

[GOMEZEL_DINO]

Hello!

I have a class that have 4 one-to-one propeties. Is possible to EagerFetch them? I want to display a list of the class, but it takes an eternity.

I know that there is no EagerFetch for ManyValuedAssociations , but this is a one-to-one relationship, so I know there will be max 1 row for each property.

Regards!

[White_Dave1 (White Dave)]

If you look at the Aurelius manual section under Association, you will see that Eager loading is the default mode unless Lazy is specifically asked for.

Regarding many values associations, that link you provided is from 2016 - an old version of Aurelius. Current versions do support both Eager and Lazy, with Eager again being the default.

[wlandgraf (Wagner Landgraf)]

Also, in one-to-one relationship, there is always a side that is an Association (instead of Many-Valued Association). You can simply query from that side and then data from both tables will be retrieved in a single SQL statement.

[GOMEZEL_DINO]

Hello!

First - thank you both for helping. I attach the class definitions, because I'm a little confused. Also Wagner's suggestion - how to do it, because there are 4 one-to-one relations?

  // Price
  [Entity]
  [Table('product_prices')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TStockItemPrice = class(TBaseObject)
  private
    [Column('ID')]
    FId: integer;

    [Association([TAssociationProp.Lazy], CascadeTypeAllButRemove)]
    [JoinColumn('PROD_ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.NoUpdate], 'ID')]
    FProduct: Proxy<TStockItem>;

    [Column('PRICE_RETAIL')]
    FRetail: currency;
    ...

    function GetId: TStockItem;
    procedure SetId(const Value: TStockItem);
  public
  	property Id: integer read FId write FId;
  	property Product: TStockItem read GetId write SetId;

  	property RetailNoTax: currency read FRetail write FRetail;                         
  	property Retail: currency read FRetail write FRetail;                              
        ...

  end;

  // Dim
  [Entity]
  [Table('product_dim')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TStockItemDim = class(TBaseObject)
  private
    [Column('ID')]
    FId: integer;

    [Association([TAssociationProp.Lazy], CascadeTypeAllButRemove)]
    [JoinColumn('PROD_ID', [], 'ID')]
    FProduct: Proxy<TStockItem>;

    [Column('WEIGHT')]
    FWeight: double;
  public
  	property Id: integer read FId write FId;
  	property Product: TStockItem read GetId write SetId;

  	property Weight: double read FWeight write FWeight;
       ...
  end;

  // Istat
  [Entity]
  [Table('product_istat')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TStockItemIstat = class(TBaseObject)
  private
    [Column('ID')]
    FId: integer;

    [Association([TAssociationProp.Lazy], CascadeTypeAllButRemove)]
    [JoinColumn('PROD_ID', [], 'ID')]
    FProduct: Proxy<TStockItem>;
    ...
  public
  	property Id: integer read FId write FId;
  	property Product: TStockItem read GetId write SetId;
  	...
  end;

  [Entity]
  [Table('product_stock')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TStockItemStock = class(TBaseObject)
  private
    [Column('ID')]
    FId: integer;

    [Association([TAssociationProp.Lazy], CascadeTypeAllButRemove)]
    [JoinColumn('PROD_ID', [], 'ID')]
    FProduct: Proxy<TStockItem>;

    [Column('STOCK')]
    FOnStock: double;
	...
  public
  	property Id: integer read FId write FId;
  	property Product: TStockItem read GetId write SetId;
  	property OnStock: double read FOnStock write FOnStock;
  end;

  [Entity]
  [Table('products')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TStockItem = class(TBaseObject)
  private
    [Column('ID')]
    FId: integer;

    [Column('DESCRIPTION')]
    FDescription: string;

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FProduct')]
    FPrices: Proxy<TList<TStockItemPrice>>;

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FProduct')]
    FIstat: Proxy<TList<TStockItemIstat>>;

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FProduct')]
    FDim: Proxy<TList<TStockItemDim>>;

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAllRemoveOrphan, 'FProduct')]
    FStocks: Proxy<TList<TStockItemStock>>;

  public
    property Id: integer read FId write FId;    
    property Description: string read FDescription write FDescription;
    property Stocks: TStockItemStock read GetStocks write SetStocks;
    property Prices: TStockItemPrice read GetPrices write SetPrices;
    property Dim: TStockItemDim read GetDim write SetDim;
    property Istat: TStockItemIstat read GetIstat write SetIstat;
  end;

I tried to fetch the list this way:

  Result := FManager.Find<TStockItem>
                    .FetchEager('ProductType')
                    .FetchEager('Supplier')
                    .FetchEager('Manufacturer')
                    .FetchEager('Prices')
                    .FetchEager('Stocks')
                    .FetchEager('Istat')
                    .FetchEager('Dim')
                    .Refreshing;

I would kindly ask for a more specific tip on how to do it.

[wlandgraf (Wagner Landgraf)]

In this specific case there is indeed not much to do.

But your one-to-one relationship is strange. First, it's not one-to-one because you don't have unique keys in your detail classes mapping. Maybe you have created manually in the database, I don't know.

Have you considered adding the FK references to the TStockItem? In other words, create Association (not Many-Valued Association)?

[GOMEZEL_DINO]

Hi!

The DB is an existing one. Thaks for the FK tip, I will add them.

About one-to-one relations, I foud the informations somewhere in this forum and reuse the know-how.

How to map one-to-one association

What is the correct way to map an one-to-one association? For example: association where each Person can have 0 or 1 Address associated and an each Address must be associated with only one Person. Classes: TAddress = classstrict private  FCity: String;   FStreetAddress: String;  FZipCode: String; public  property StreetAddress: String read FStreetAddress write FStreetAddress;  property City: String read FCity write FCity;  property: ZipCode: String FZipCode write FZipCode; end; TPerson = …

I didn't notice and FK. I will add them and see how it works out.

[GOMEZEL_DINO]

Hi!

I have a question - how would you link the classes if the database is like this (see below)?
I tought it's a one-to-one relationship and indeed it work, but viewing the products is very slow.

It's also nice that saving the TStockItem automatically saves all the subclasses (stock, dim, prices..).

CREATE TABLE `products` (
	`ID` INT(10) UNSIGNED NOT NULL,
	`IS_ACTIVE` CHAR(1) NOT NULL DEFAULT '1' COLLATE 'cp1250_croatian_ci',
	`NAME` VARCHAR(255) NULL DEFAULT NULL COLLATE 'cp1250_croatian_ci',
	PRIMARY KEY (`ID`) );

CREATE TABLE `product_stock` (
	`PROD_ID` INT(10) UNSIGNED NOT NULL,
	`STOCK` FLOAT(12,2) NOT NULL DEFAULT '0.00',
	PRIMARY KEY (`PROD_ID`) );

CREATE TABLE `product_prices` (
	`PROD_ID` INT(3) UNSIGNED NOT NULL DEFAULT '0',
	`PRICE_PURCHASE` FLOAT(15,4) UNSIGNED NOT NULL DEFAULT '0.0000',
	`PRICE_RETAIL` FLOAT(15,4) UNSIGNED NOT NULL DEFAULT '0.0000',
	PRIMARY KEY (`PROD_ID`) );

The SQL is quite simplified for the example purposes.

[wlandgraf (Wagner Landgraf)]

If it's slow I imagine you are retrieving thousands of products. In this specific case, I would create a database view that consolidate the products and their 1-1 additional info (stock, prices) and then create an Aurelius class mapped to that view to retrieve all data at once.

[GOMEZEL_DINO]

I used the view - thank you.