Cayenne Query trivia

[Andrus Adamchik <an...@objectstyle.org>]

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

Re: Cayenne Query trivia

[Andrus Adamchik <an...@objectstyle.org>]

You are free to extend the query in any way you want, as long as it  
produces something understood by Cayenne in either 'route' or  
'createSQLAction' method. So yeah, you can store parameters in your  
custom query, and later bind them via the existing API. Although by  
convention query doesn't change its internal state (only the user can  
do that), so I would usually clone the query and modify the clone  
properties as needed. SelectQuery already has 'queryWithParameters'  
that does cloning for you.

Andrus


On Jul 17, 2007, at 6:43 PM, Bryan Lewis wrote:

> Awesome.  I can use that.
>
> Ummm... could it be made to take a Map of parameters?  I could do  
> it by
> adding a statement involving expWithParameters(), right?
>
>
> Andrus Adamchik wrote:
>> 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
>>
>
>

Re: Cayenne Query trivia

[Bryan Lewis <br...@maine.rr.com>]

Awesome.  I can use that.

Ummm... could it be made to take a Map of parameters?  I could do it by
adding a statement involving expWithParameters(), right?


Andrus Adamchik wrote:
> 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
>