cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Meeks, Andrew" <>
Subject RE: Effective dated records
Date Wed, 22 Jun 2016 16:06:51 GMT
Thank you Hugi,

Yes, that resolves my problem.  This helps a lot!


-----Original Message-----
From: Hugi Thordarson [] 
Sent: Wednesday, June 22, 2016 11:54 AM
Subject: Re: Effective dated records

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(
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 );

- hugi

> On 22. jún. 2016, at 14:29, Meeks, Andrew <> 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

View raw message