From torque-user-return-7505-apmail-db-torque-user-archive=db.apache.org@db.apache.org Wed Apr 18 17:31:26 2007 Return-Path: Delivered-To: apmail-db-torque-user-archive@www.apache.org Received: (qmail 92933 invoked from network); 18 Apr 2007 17:31:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 18 Apr 2007 17:31:26 -0000 Received: (qmail 73123 invoked by uid 500); 18 Apr 2007 17:31:32 -0000 Delivered-To: apmail-db-torque-user-archive@db.apache.org Received: (qmail 72983 invoked by uid 500); 18 Apr 2007 17:31:31 -0000 Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Users List" Reply-To: "Apache Torque Users List" Delivered-To: mailing list torque-user@db.apache.org Received: (qmail 72972 invoked by uid 99); 18 Apr 2007 17:31:31 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Apr 2007 10:31:31 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_HELO_PASS,SPF_PASS,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of greg.monroe@dukece.com designates 152.3.166.24 as permitted sender) Received: from [152.3.166.24] (HELO dukece.com) (152.3.166.24) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Apr 2007 10:31:23 -0700 Received: from ([10.123.20.205]) by smtpgw1.dukece.com with ESMTP id 4440076.815082; Wed, 18 Apr 2007 13:30:45 -0400 Received: from dukece-mail3.dukece.com ([10.123.20.204]) by mail.dukece.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 18 Apr 2007 13:30:34 -0400 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: RE: Wrong SQL generation from Criteria Date: Wed, 18 Apr 2007 13:30:45 -0400 Message-ID: <8F5843B903F59D4C8C6806BB49A3911902F75E48@dukece-mail3.dukece.com> In-Reply-To: <20070418153123.GA18982@localhost.localdomain> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Wrong SQL generation from Criteria Thread-Index: AceBzr4xnjvNXYO+SnaGxTBOiUbuHwABupcw References: <20070418153123.GA18982@localhost.localdomain> From: "Greg Monroe" To: "Apache Torque Users List" X-OriginalArrivalTime: 18 Apr 2007 17:30:34.0796 (UTC) FILETIME=[458DCEC0:01C781DF] X-Virus-Checked: Checked by ClamAV on apache.org OK, pardon me if you know some of this... just being=20 complete... The SQL Group By clause is defined for use with SQL=20 summary functions. E.g.: Select category, product, count(product) as in_stock from products=20 group by category, product If you use a GROUP BY clause, the only valid non-summary fields on the select are the ones listed in the=20 group by clause. Torque is for the most part a record retrieval based OM. E.g., when you ask for something, you get a record object that is fully populated. This means that when you use the RecordPeer.doSelect(c) methods, Torque needs to retreive all field values to fully=20 populate the record object. So, it HAS to add all the table fields to the select. If you want to use Criteria.groupBy() and the normal Peer methods, you need to include ALL the fields. However, by the time you do that, it's pretty much the same as doing an AddAscendingOrderBy. If you want to do a summary type function, you can do this a couple of ways. If it's just a count, you can use the CountHelper function. If it's more than that you can use BasePeer.doSelect(c) and get the data from the Village Record objects returned. E.g. Criteria c =3D new Criteria(); c.addSelectColumn(ProductsPeer.CATEGORY); c.addSelectColumn(ProductsPeer.PRODUCT); c.addAsColumn("in_stock", "COUNT("=20 +ProductsPeer.PRODUCT+")"); c.addGroupByColumn(ProductsPeer.CATEGORY); c.addGroupByColumn(ProductsPeer.PRODUCT); List results =3D BasePeer.doSelect(c); if ( results.size() > 0 ) { Record rec =3D (Record) results.get(0); category =3D rec.getValue(1).asString(); product =3D rec.getValue(2).asString(); in_stock =3D rec.getValue(3).asInt(); } This is almost the same as going back to standard results sets with the exception that you get=20 protection against schema changes. Eg., if a=20 column or table is dropped, your code won't compile=20 so you know somethings wrong. > -----Original Message----- > From: Brendan Miller [mailto:bmiller@dotster.com]=20 > Sent: Wednesday, April 18, 2007 11:31 AM > To: torque-user@db.apache.org > Subject: Wrong SQL generation from Criteria >=20 >=20 > Maybe I'm doing something very poor, but I thought I had used=20 > these constructs successfully before. (This is Oracle, BTW.) >=20 > My RESOURCE_LOCK table schema: >=20 > > javaName=3D"ItemID"/> > required=3D"true" size=3D"100" type=3D"VARCHAR"=20 > javaName=3D"ResourceDescriptor"/> > javaName=3D"EntryTimestamp"/> > javaName=3D"LockerID"/> > javaName=3D"LockerType"/> > javaName=3D"ResourceTypeID"/> >
>=20 > My code: >=20 > Criteria crit =3D new Criteria(); > crit.addGroupByColumn(ResourceLockPeer.LOCKER_TYPE); > List locks =3D ResourceLockPeer.doSelect(crit); >=20 > The observed SQL from log4j when turned on: >=20 > SELECT RESOURCE_LOCK.ITEM_ID,=20 > RESOURCE_LOCK.RESOURCE_DESCRIPTOR,=20 > RESOURCE_LOCK.ENTRY_TIMESTAMP, RESOURCE_LOCK.LOCKER_ID,=20 > RESOURCE_LOCK.LOCKER_TYPE, RESOURCE_LOCK.RESOURCE_TYPE FROM=20 > DNS.RESOURCE_LOCK GROUP BY RESOURCE_LOCK.LOCKER_TYPE >=20 > The exception: >=20 > org.apache.torque.TorqueException: java.sql.SQLException:=20 > ORA-00979: not a GROUP BY expression >=20 > DNS is the torque.database.schema, but I don't understand why=20 > it is inconsistent in adding the database prefix in the FROM=20 > clause but not the GROUP clause. >=20 > Joins are similarly affected: >=20 > Criteria crit =3D new Criteria(); > crit.add(ResourceLockPeer.LOCKER_ID, new Long(2115)); > crit.add(ResourceLockPeer.LOCKER_TYPE, "RECURBILLINGPROC"); > crit.addJoin(ResourceLockPeer.RESOURCE_DESCRIPTOR,=20 > RecurBillingPeer.ID); > crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_ID); > crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_PAYMENT_ID); > List recBilling =3D RecurBillingPeer.doSelect(crit); >=20 > The SQL is even worse, as it contains both the tablename and=20 > dbprefix.tablename in the where clause, which gives Oracle=20 > confusion fits. >=20 > SELECT <..all RECUR_BILLING fields..> FROM RESOURCE_LOCK,=20 > RECUR_BILLING, DNS.RECUR_BILLING, DNS.RESOURCE_LOCK WHERE=20 > RESOURCE_LOCK.RESOURCE_DESCRIPTOR=3DRECUR_BILLING.ID AND=20 > RESOURCE_LOCK.LOCKER_TYPE=3D'RECURBILLINGPROC' AND=20 > RESOURCE_LOCK.LOCKER_ID=3D2115 GROUP BY=20 > RECUR_BILLING.ACCOUNT_ID, RECUR_BILLING.ACCOUNT_PAYMENT_ID >=20 > Brendan >=20 > --------------------------------------------------------------------- > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org > For additional commands, e-mail: torque-user-help@db.apache.org >=20 >=20 Duke CE Privacy Statement Please be advised that this e-mail and any files transmitted with it are = confidential communication or may otherwise be privileged or = confidential and are intended solely for the individual or entity to = whom they are addressed. If you are not the intended recipient you may = not rely on the contents of this email or any attachments, and we ask = that you please not read, copy or retransmit this communication, but = reply to the sender and destroy the email, its contents, and all copies = thereof immediately. Any unauthorized dissemination, distribution or = copying of this communication is strictly prohibited. --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org For additional commands, e-mail: torque-user-help@db.apache.org