I have a database with several nested associations. In principle, the structure is as follows:
Order -> OrderItem -> OrderItemPlaylist -> OrderPlaylistItem -> Track -> Artist
I need to create a report based on all orders sold for a certain date, which must go through all the specified associations in order to generate the necessary information.
An attempt to join all the tables together would be redundant, as this would lead to an extremely large Cartesian join with many redundant data, given that this would be a combination of six tables together. Code below:
q.Left.JoinQueryOver<OrderItem>(order => order.OrderItems)
.Left.JoinQueryOver<OrderItemPlaylist>(orderItem => orderItem.Playlist)
.Left.JoinQueryOver<OrderItemPlaylistItem>(orderItemPlaylist => orderItemPlaylist.PlaylistItems)
.Left.JoinQueryOver<Track>(orderItemPlaylistItem => orderItemPlaylistItem.Track)
.Left.JoinQueryOver<Artist>(track => track.Artist)
The above works, but even with several orders, each of which contains several order items, and a playlist, each of which consists of several tracks, the results will explode to thousands of records growing exponentially with each additional order.
Any idea what would be the best and most effective approach? I'm currently trying to enable batch loading, which significantly reduces the number of database queries, but still doesn't seem like a good approach to me, but more like a "simple way".
, SQL, . , SQL- . , , , .. ..
, QueryOver, .RootCriteria API .
!