cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Skladaniec <mar...@ish.com.au>
Subject complex query and SQLtemplate
Date Sun, 13 May 2007 10:29:27 GMT
Hi

In our project we needed a generic way to relate many different  
entities. Cayenne does not allow vertical inheritance, therefore I  
had to make my way around, and create a fake relationship. I'm using  
custom superclass for entities, in which accessing methods, like the  
one in cayenne are implemented (getTags/addToTags/removeFromTags).  
Those methods execute SelectQuery(). I had to expose the pk's , but  
it works well.
The application is using derby and ROP.

the problem we have is with creating a query like:

select
$attributes
from $entity
join $joinClause
where ((id in
(
    select
    TAGRELATION.ENTITYRECORDID
    from TAGRELATION join tag on TAGRELATION.tagid = tag.id
    where ((TAGRELATION.ENTITYIDENTIFIER = $entityCode) AND (id =  
$entityId))
)) AND ($qualifier))
order by $orderby

org.apache.cayenne.exp.Expression does not support that complexity,  
therefore I started to use SQLTemplate, but when query like this is  
executed:

SELECT #result('Room.name' 'java.lang.String'), #result('site.name'  
'java.lang.String'), #result('Room.seatedCapacity'  
'java.lang.Integer') FROM ROOM JOIN SITE on ROOM.siteid = SITE.id
exception is thrown:

org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT Mar 29  
2007 11:34:53] Remote error. URL - http://localhost:8181/angel-server- 
cayenne; CAUSE - [v.3.0-SNAPSHOT Mar 29 2007 11:34:53] [v.$ 
{project.version} ${project.build.date} ${project.build.time}]  
Exception processing message org.apache.cayenne.remote.QueryMessage.  
Root cause: [v.${project.version} ${project.build.date} $ 
{project.build.time}] Error getting ResultIterator: Query Exception:
java.sql.SQLException: Column 'SITE.NAME' is either not in any table  
in the FROM list or appears within a join specification and is  
outside the scope of the join specification or appears in a HAVING  
clause and is not in the GROUP BY list. If this is a CREATE or ALTER  
TABLE  statement then 'SITE.NAME' is not a column in the target table.
	at org.apache.derby.client.am.SQLExceptionFactory.getSQLException 
(Unknown Source)
	at org.apache.derby.client.am.SqlException.getSQLException(Unknown  
Source)
	at org.apache.derby.client.am.Connection.prepareStatement(Unknown  
Source)
	at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement 
(ConnectionWrapper.java:274)
	at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement 
(ConnectionWrapper.java:280)
	at  
org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatemen 
t(TransactionConnectionDecorator.java:179)
	at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute 
(SQLTemplateAction.java:130)
	at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction 
(SQLTemplateAction.java:107)
	at org.apache.cayenne.access.DataNodeQueryAction.runQuery 
(DataNodeQueryAction.java:57)
	at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
	at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute 
(DataDomainLegacyQueryAction.java:82)
	at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java: 
704)
	at org.apache.cayenne.access.DataDomain.runInTransaction 
(DataDomain.java:802)
	at org.apache.cayenne.access.DataDomain.performQueries 
(DataDomain.java:698)
	at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery 
(DataContext.java:1261)
	at org.apache.cayenne.access.DataContext.performIteratedQuery 
(DataContext.java:1227)
	at org.apache.cayenne.access.IncrementalFaultList.fillIn 
(IncrementalFaultList.java:190)
	at org.apache.cayenne.access.IncrementalFaultList.<init> 
(IncrementalFaultList.java:156)
	at  
org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery 
(DataContextQueryAction.java:109)
	at org.apache.cayenne.access.DataContextQueryAction.execute 
(DataContextQueryAction.java:54)
	at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1321)
	at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery 
(ClientServerChannelQueryAction.java:105)
	at org.apache.cayenne.access.ClientServerChannelQueryAction.execute 
(ClientServerChannelQueryAction.java:66)
	at org.apache.cayenne.access.ClientServerChannel.onQuery 
(ClientServerChannel.java:79)
	at org.apache.cayenne.intercept.DataChannelDecorator.onQuery 
(DataChannelDecorator.java:57)
	at org.apache.cayenne.remote.service.DispatchHelper.dispatch 
(DispatchHelper.java:40)
	at org.apache.cayenne.remote.service.BaseRemoteService.processMessage 
(BaseRemoteService.java:151)

When the same query is executed directly in derby there are no  
problems, so either:
- cayenne does not handle joins in SQLTemplate correctly (is http:// 
issues.apache.org/cayenne/browse/CAY-552 related ?)
- I'm doing something wrong

Also, was my idea to use SQLTemplate right ? I have tried to use  
Expression, but I could not find a way to encode the query I need. Is  
there any better way to archive what I'm after ?


Cheers
Marcin




Mime
View raw message