Hi Andrew. If I understand your problem correctly you should be able to achieve this by setting a fetch limit of 1 on your query and sorting by date in descending order. It's the same as adding LIMIT to an SQL statement.
For example, let’s assume you record the movement of paintings in an entity called PaintingLocation (that links entities Painting and Location) and would like to know the current location of a certain painting on January 1st 2000: SelectQuery<PaintingLocation> query = new SelectQuery<>( PaintingLocation.class ); query.setQualifier( PaintingLocation.PAINTING.eq( somePainting ).andExp( PaintingLocation.DATE.lt( LocalDate.of( 2000, 1, 1 ) ) ); query.addOrdering( PaintingLocation.DATE.desc() ); query.setFetchLimit( 1 ); query.addPrefetch( PaintingLocation.LOCATION.joint() ); // this prefetch is optional, of course, but improves performance. Location locationOfPainting = objectContext.selectOne( query ); Cheers, - hugi > On 22. jún. 2016, at 14:29, Meeks, Andrew <andrew.me...@vt.edu> wrote: > > 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 > anme...@vt.edu<mailto:anme...@vt.edu> >
