Is there a formal Cayenne approach to retrieving an effective dated record?
At the moment my approach with SelectQuery is to request all records dated prior to a given date, then on the returned entities, loop through and find the most recent one. Alternatively, the correlated subquery could be written with an SQLSelect and the datarows converted to entity instances. For entities which have already been retrieved, there appears to be no native method to ask for a most effective dated relation more efficiently than just asking for all records and then looping through looking for the most recent one. Is there a pattern recommended for obtaining such records? To clarify the latter scenario using the Artist, Painting, Gallery paradigm: Assuming a painting is located somewhere at some time. When it changes location, I could record the new gallery with the date that painting arrived. If I want see where a certain artist was on display last year, assuming I had retrieved the artist entity, I would ask for the artist's paintings. Then from each painting I would get all of the locations where the painting had been. I would then loop through each location record looking for the last place where that painting had arrived prior or equal to the given year. This process seems expensive the longer data is collected and the more mobile the paintings are. Oracle appears to execute correlated subqueries relatively efficiently and I think I would rather allow it to handle such work. Thank you, Andrew Meeks [email protected]<mailto:[email protected]>
