A problem with the Many-valued Associations

BIZ TMS Aurelius
1

Hi


I playing around with this orm and experiences a problem with the Many-valued Associations.  I log the queries.

The entity is Members and has a member collection Websites.  I generated a data model from the database via the TMS Data Modeller.

Without a Many-valued Association its all good.  The sql is pretty plain and looks fine.

The problem is when I use a Many-valued Association on Websites then the generated sql is the same plus for every record a query to retrieve all websites per member!  This is killing my database.

I tried everything, lazy, eager. I'm a bit out of options here.

Code generated in the Member class:
[ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge, TCascadeType.Remove], 'FMemberId')]
    FWebsitesList: Proxy<TList<TWebsites>>;
    function GetWebsitesList: TList<TWebsites>;


Code generated in the Website class:
  [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('MemberId', [TColumnProp.Required], 'Id')]
    FMemberId: Proxy<TMembers>;
    function GetMemberId: TMembers;
 

Hope someone can help cause this is kind of a deal breaker.

2

Could you please provide more details illustrating with code and results? What is the Aurelius code you are using? What is the result you get? 
3

Hi, 


Thanks for the reply.

Basically i'm doing a standard get all:

FManager := TObjectManager.Create(FConnection);
AureliusDataset1.Manager := FManager;
AureliusDataset1.SetSourceCriteria(FManager.Find<TMembers>());
AureliusDataset1.Open();

I get this result:

SELECT A.Id AS A_Id, A.Name AS A_Name, A.Type AS A_Type, A.ServerPropertiesId AS A_ServerPropertiesId, A.BoxOfficeId AS A_BoxOfficeId, A.CreatedOn AS A_CreatedOn, A.Description AS A_Description, A.SourceIPs AS A_SourceIPs, A.MemberId AS A_MemberId, A.OrderFromEmailAddress AS A_OrderFromEmailAddress, A.OrderBccEmailAddresses AS A_OrderBccEmailAddresses, A.ActiveMailerUserName AS A_ActiveMailerUserName, A.ActiveMailerPassword AS A_ActiveMailerPassword, A.CollectedStatusAfterPayment AS A_CollectedStatusAfterPayment, A.Payment_BankOrderDescription AS A_Payment_BankOrderDescription, A.Payment_TransactionIdPrefix AS A_Payment_TransactionIdPrefix, A.Payment_UserName AS A_Payment_UserName, A.Payment_Salt AS A_Payment_Salt, A.Payment_Url AS A_Payment_Url, A.Payment_TestUserName AS A_Payment_TestUserName, A.Payment_TestSalt AS A_Payment_TestSalt, A.Payment_TestUrl AS A_Payment_TestUrl, A.Payment_LogoUrl AS A_Payment_LogoUrl, A.Payment_ParamVar AS A_Payment_ParamVar, A.Payment_IsLive AS A_Payment_IsLive, A.HasMaccsbox AS A_HasMaccsbox, A.HashAlgorithmId AS A_HashAlgorithmId, B.Id AS B_Id, B.Name AS B_Name, A.PaymentProviderId AS A_PaymentProviderId, C.Id AS C_Id, C.Name AS C_Name
FROM Members A
  LEFT JOIN HashAlgorithms B ON (B.Id = A.HashAlgorithmId)
  LEFT JOIN PaymentProviders C ON (C.Id = A.PaymentProviderId)
---------------
SELECT A.Id AS A_Id, A.HostName AS A_HostName, A.CssUrl AS A_CssUrl, A.JavascriptUrl AS A_JavascriptUrl, A.ContinueShoppingUrl AS A_ContinueShoppingUrl, A.BoxOfficeId AS A_BoxOfficeId, A.PaymentAcceptedUrl AS A_PaymentAcceptedUrl, A.PaymentCanceledUrl AS A_PaymentCanceledUrl, A.PaymentUncertainUrl AS A_PaymentUncertainUrl, A.MemberId AS A_MemberId
FROM Websites A
WHERE  A.MemberId = :p_0
p_0 = "6" (ftInteger)
---------------
SELECT A.Id AS A_Id, A.HostName AS A_HostName, A.CssUrl AS A_CssUrl, A.JavascriptUrl AS A_JavascriptUrl, A.ContinueShoppingUrl AS A_ContinueShoppingUrl, A.BoxOfficeId AS A_BoxOfficeId, A.PaymentAcceptedUrl AS A_PaymentAcceptedUrl, A.PaymentCanceledUrl AS A_PaymentCanceledUrl, A.PaymentUncertainUrl AS A_PaymentUncertainUrl, A.MemberId AS A_MemberId
FROM Websites A
WHERE  A.MemberId = :p_0
p_0 = "14" (ftInteger)
---------------
SELECT A.Id AS A_Id, A.HostName AS A_HostName, A.CssUrl AS A_CssUrl, A.JavascriptUrl AS A_JavascriptUrl, A.ContinueShoppingUrl AS A_ContinueShoppingUrl, A.BoxOfficeId AS A_BoxOfficeId, A.PaymentAcceptedUrl AS A_PaymentAcceptedUrl, A.PaymentCanceledUrl AS A_PaymentCanceledUrl, A.PaymentUncertainUrl AS A_PaymentUncertainUrl, A.MemberId AS A_MemberId
FROM Websites A
WHERE  A.MemberId = :p_0
p_0 = "15" (ftInteger)
---------------
etc, etc, etc.

4

That is expected if you access the property "WebSites", it will fire an SQL to retrieve the list of WebSites for that Member. 

5

Ok !


Yes indeed I used the websites property in the dataset grid.

It is solved! Thanks!
6

Another problem.


The query works now but only when the WebsiteList field is removed from the TFieldList from the TAureliusDataset. Problem is now that I cant access the WebsiteList field for master-detail scenario Im working in Firemonkey.


7

Yes, if you want a master-detail scenario, you would have to have the list of the detail of course.

I'm not sure how you would expect this to be done differently, since it will have to retrieve the detail list to show the detail list.
Maybe you are experiencing an issue with Firemonkey lists/grids that it doesn't fetch data on demand? For example, if you have a list with 100k items, Firemonkey visual controls will iterate over the 100k records in the database to build the visual stuff, instead of reading only the records being currently displayed. So it will load the detail lists for the 100k items without needing it in first place.
8

Yes I think thats the case.  In vcl it seems to work. Im looking for a work arround now.