Cayenne Query trivia

[Andrus Adamchik]

Here is a small custom query that combines SQLTemplate and  
SelectQuery in one, allowing to fetch the root entity with raw SQL,  
and still prefetch related entities as if it were a SelectQuery. Note  
that qualifier is ignored for the root entity fetch, but is applied  
to the prefetched entities (most often than not qualifier is null  
though). Ordering is ignored too, and should be encoded in SQL.

Not sure how common this case is, but I have a real-life situation  
where this turned to be invaluable.

public class CustomSQLSelectQuery extends SelectQuery {

        protected String rootQuerySQL;

        public CustomSQLSelectQuery(Class objectClass, String rootQuerySQL) {
                super(objectClass);
                this.rootQuerySQL = rootQuerySQL;
        }

        public SQLAction createSQLAction(SQLActionVisitor visitor) {
                SQLTemplate replacement = new SQLTemplate();
                replacement.setRoot(getRoot());
                replacement.setDefaultTemplate(rootQuerySQL);
                return replacement.createSQLAction(visitor);
        }
}


SelectQuery query = new CustomSQLSelectQuery(Artist.class, "<SOME  
SQL....>");
query.addPrefetch(Artist.PAINTING_ARRAY);
List artists = dataService.getContext().performQuery(query);

Cheers,
Andrus