Hi, some time ago, i asked this:
How to load a detail table, on a master-detail (Invoice-Items), with just one query? Now i have to do a for cicle, like Invoice.ItemsList.count, this will execute one query for every item of the invoice, if the invoice have Hundreds of items we have hundreds of querys. I think this is a very intresting feature for all..
thanks
That will not execute one query for every item of the invoice, but one query for each invoice.
It doesn't matter if the invoice has one or hundreds of items, all the lists are retrieved once.
Now, if you mean that having many invoices will fire many queries to retrieve the item list for every invoice, then that's correct. The only workaround would be to query all the items at once (Find<TInvoiceItem>) and then manually group the items and invoices client-side.
Hi, Wagner
I'm using Data Modeler to generate the schema, by default Many Valid Association are configured as Lazy. What i'm asking is how to eager load when needed without a for cicle? As in associations, by default, are configured as Lazy, when nedeed i can Eager Load with CreateAlias. thanks
Hi, this is the case
[Entity]
[Table('DocSct')]
TDocSct = class
private
[Column('Id', [TColumnProp.Required])]
FId: Int64;
[Column('Description', [TColumnProp.Required], 30)]
FDescription: string;
[ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FDocSctId')]
FDocScStList: Proxy<TList<TDocScSt>>;
function GetDocScStList: TList<TDocScSt>;
public
constructor Create;
destructor Destroy; override;
property Id: Int64 read FId write FId;
property Description: string read FDescription write FDescription;
property DocScStList: TList read GetDocScStList;
end;
[Entity]
[Table('DocScSt')]
TDocScSt = class
private
[Column('Id', [TColumnProp.Required])]
FId: Int64;
[Column('Enabled', [TColumnProp.Required])]
FEnabled: Integer;
[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('DocSettId', [TColumnProp.Required], 'Id')]
FDocSettId: Proxy<TDocSett>;
function GetDocSettId: TDocSett;
procedure SetDocSettId(const Value: TDocSett);
public
property Id: Int64 read FId write FId;
property Enabled: Integer read FEnabled write FEnabled;
property DocSettId: TDocSett read GetDocSettId write SetDocSettId;
end;
[Entity]
[Table('DocSett')]
TDocSett = class
private
[Column('Id', [TColumnProp.Required])]
FId: Int64;
[Column('TypeCode', [TColumnProp.Required], 10)]
FTypeCode: string;
public
property Id: Int64 read FId write FId;
property TypeCode: string read FTypeCode write FTypeCode;
end;
...
Result := Manager.Find.Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id))
.UniqueResult;
if Result <> nil then
begin
//How to Eager Load, Result.DocScStList[i].DocSettId.Id, without the "for"
for i := 0 to Result.DocScStList.Count-1 do
begin
Result.DocScStList[i].Id := Result.DocScStList[i].Id;
Result.DocScStList[i].DocSettId.Id := Result.DocScStList[i].DocSettId.Id;// MULTIPLE QUERYS
end;
end;
...
thanks
Have you tried
Result := Manager.Find<TDocSct>
.CreateAlias('DocScStList', 'd', TFetchMode.Eager)
.CreateAlias('d.DocSettId', 'set', TFetchMode.Eager)
.Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id))
.UniqueResult;
Hi, Wagner
I have try without success. "DocSct" master table, "DocScSt" detail table with "DocSett" association(Lookup) table, just to make it clear.
Tanks
Try mngr.find<tdocsct>.FetchEager('DocScStList.DocSettId').etc.etc.etc
Hi Ferrari, thanks
but what's the difference between what you said and Wagner?
thanks again
Have you really tested any of these options?
Wagner gave you an example of FetchEager, but with CreateAlias parameters instead. It should have raised compilation errors.
The example I sent you, with your entity names, use the method .FetchEager with the full path you need, and that's enough to force it to generate just one joined query.
You would just use it as
Manager.find<tdocsct>.FetchEager('DocScStList.DocSettId')
.CreateAlias('DocScStList', 'd')
.CreateAlias('d.DocSettId', 'set')
.Add(TLinq.Eq('set.id', Id)).UniqueResult;
or so.
Hi, thanks Ferrari
Yes, many times, simply does not work, the list "DocSct.DocScStList" still empty, just when do "DocSct.DocScStList.count" an extra query is executed (but this is not the issue), then i need to do eagerload DocSct.DocScStList.DocSett one by one, with the for cicle.
Correction:
Result := Manager.Find<TDocSct>.Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id)) .UniqueResult;
...
Thanks again
Hi, again, copy json to "https://jsoneditoronline.org" and easily understand the structure.
{"$type":"DB.Schema.TDocSct","$id":1,"FDescription":"Some Description","FDocScStList":[{"$type":"DB.Schema.TDocScSt","$id":5,"FId":-1,"FEnabled":1,"FDocSettId":{"$type":"DB.Schema.TDocSett","$id":6,"FTypeCode":"FT","FId":-1},"FDocSctId":{"$proxy":"single","key":-1,"class":"TDocScSt","member":"FDocSctId"}},{"$type":"DB.Schema.TDocScSt","$id":7,"FId":-2,"FEnabled":1,"FDocSettId":{"$type":"DB.Schema.TDocSett","$id":8,"FTypeCode":"NC","FId":-3},"FDocSctId":{"$proxy":"single","key":-1,"class":"TDocScSt","member":"FDocSctId"}}]}
Thanks, thanks, thanks
That's unfortunately how it is. The many-valued associations are always retrieved separately, because they are a different SELECT statement. And at that point, the fetch eager information about sub associations is not available.
The workaround is the one I mentioned here: How to load optimized Master-Detail - #2 by wlandgraf
hi Wagner
As a suggestion if somehow aurelius execute one query for the master table and one query for the detail table with eager load to the association, we just don't need the for cicle. But that's what it's
Thanks for your patience :)
1 Like
Hi,
I consider it important to reopen this post, because latest version of aurelius has a fix, but i can make this work
"Fixed: TFetchMode.Eager now forces eager loading of many-valued associations as well"
Consider the above sample, Master-Detail-Association. I need to Load the Association without a for cicle,
...
fCriteria.CreateAlias('DocScStList','AliasDocScStList', TFetchMode.Eager);
fCriteria.CreateAlias('AliasDocScStList.DocSettId','AliasDocSettId', TFetchMode.Eager);//This does not work
..
Please, check JSON to easily understand the schema
Thanks
The fix refers to loading the many-valued association at the time they are loaded from the database. But it sill will execute one SQL statement for each list.
Ok, Thanks