How Can I Add a Prefetch to a NamedQuery/MappedSelect?

Question

I have a named query in my model that's set to fetch Data Objects.

<query name="artists" type="SQLTemplate" root="obj-entity" root-name="Artist">
    <property name="cayenne.GenericSelectQuery.fetchingDataRows" value="false"/>
    <sql><![CDATA[SELECT * FROM Artist]]></sql>
</query>

My actual query is more complicated and can't be done using ObjectSelect.

I'd like to add a prefetch, but I haven't come up with a solution I like. Here's what I've tried:

1. Faulting

   List<Artist> artists = MappedSelect.query(...).select(context);
   artists.forEach( artist -> artist.getPaintings.size() );
   

   This doesn't refresh from the database and requires a separate query for each artist.

2. Call RelationshipQuery directly

   List<Artist> artists = MappedSelect.query(...).select(context);
   for (Artist artist : artists) {
       Query query = new RelationshipQuery(artist.getObjectId(), Artist.PAINTINGS.getName());
       ((ToManyList) artist.getPaintings()).setObjectList(context.performQuery(query));
   }
   

   I don't think this does everything it's supposed to and it still requires a separate query for each artist.

3. Subclass MappedSelect to expose getReplacementQuery

   class MySelect<T> extends MappedSelect<T> {
       ...
       public SQLTemplate getReplacementQuery(...) {
           return (SQLTemplate) super.getReplacementQuery(...);
       }
   }
   MySelect query = new MySelect(...);
   SQLTemplate template = query.getReplacementQuery(context.getEntityResolver());
   template.addPrefetch(Artist.PAINTINGS.disjointById());
   template.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
   List<Artist> artists = query.select(context);
   

   I don't like having to access private API. I also think this solution might fail if I set any params after accessing the replacement query.

4. Change package to access BaseMetadata

   package org.apache.cayenne.query;
   ...
   MappedSelect<Artist> query = MappedSelect.query(...);
   BaseQueryMetadata metaData = (BaseQueryMetadata) query.getMetaData();
   metaData.mergePrefetch(Artist.PAINTINGS.disjointById());
   metaData.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
   Artist artist = query.select(context);
   

   This seems slightly better, but I'm still accessing private API and it requires writing code in a different package (or using reflection). It also still has a forced downcast.

5. Refetch objects

   List<Artist> artists = MappedSelect.query(...).select(context);
   ObjectSelect.query(Artist.class)
       .where(ExpressionFactory.matchAnyExp(artists))
       .prefetch(Artist.PAINTINGS.disjoint())
       .cacheStrategy(QueryCacheStrategy.NO_CACHE);
       .select(context);
   

   This is what I'm trying to avoid, since I'm refetching rows I just retrieved.

Is there a preferred way to add a prefetch to a named query, like a property I can set in the model? Or is there a good way to refresh a relationship?

Answer 1

Neither MappedSelect nor SQLSelect support prefetches, which is an unfortunate omission (I'll follow up to fill this gap in Cayenne). You may have to switch to the older, less pretty, but totally functional SQLTemplate query that does:

SQLTemplate q = new SQLTemplate(MyEntity.class, "SELECT * FROM...");
q.addPrefetch(MyEntity.SOME_RELATIONSHIP.getName());