Return-Path: Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: (qmail 13251 invoked from network); 14 May 2007 00:40:09 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 May 2007 00:40:09 -0000 Received: (qmail 56970 invoked by uid 500); 14 May 2007 00:40:16 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 56950 invoked by uid 500); 14 May 2007 00:40:15 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 56939 invoked by uid 99); 14 May 2007 00:40:15 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 13 May 2007 17:40:15 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of marcin@ish.com.au designates 59.167.240.32 as permitted sender) Received: from [59.167.240.32] (HELO fish.ish.com.au) (59.167.240.32) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 13 May 2007 17:40:07 -0700 Received: from [10.194.9.2] (helo=[10.194.9.2]) by fish.ish.com.au with esmtp (Exim 4.43) id 1HnObR-0007QF-Lq for user@cayenne.apache.org; Mon, 14 May 2007 10:39:57 +1000 Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: References: Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <8F3096A6-6CF8-4018-9E46-A902B328EC54@ish.com.au> Content-Transfer-Encoding: 7bit From: Marcin Skladaniec Subject: Re: complex query and SQLtemplate Date: Mon, 14 May 2007 10:39:39 +1000 To: user@cayenne.apache.org X-Mailer: Apple Mail (2.752.2) X-Spam-Score: -1.4 (-) X-Spam-Report: -1.4 ALL_TRUSTED Passed through trusted hosts only via SMTP X-Virus-Checked: Checked by ClamAV on apache.org Hi I did some more debugging and I found that SQLTemplate on client : SELECT #result('Room.name' 'java.lang.String'), #result('site.name' 'java.lang.String'), #result('Room.seatedCapacity' 'java.lang.Integer') FROM ROOM INNER JOIN SITE on ROOM.siteid = SITE.id ORDER BY Room.name is loosing the join part when executed on server: [java] 10:24:24,003 [SocketListener0-0] INFO org.apache.cayenne.access.QueryLogger :276 - SELECT Room.name, site.name, Room.seatedCapacity FROM ROOM ORDER BY Room.name Why is that? Is there something I'm doing wrong ? Marcin On 13/05/2007, at 8:29 PM, Marcin Skladaniec wrote: > 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.prepareStatem > ent(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. > (IncrementalFaultList.java:156) > at > org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQue > ry(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 > > > Marcin