From torque-user-return-7506-apmail-db-torque-user-archive=db.apache.org@db.apache.org Wed Apr 18 17:49:32 2007 Return-Path: Delivered-To: apmail-db-torque-user-archive@www.apache.org Received: (qmail 96933 invoked from network); 18 Apr 2007 17:49:32 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 18 Apr 2007 17:49:32 -0000 Received: (qmail 12378 invoked by uid 500); 18 Apr 2007 17:49:38 -0000 Delivered-To: apmail-db-torque-user-archive@db.apache.org Received: (qmail 12191 invoked by uid 500); 18 Apr 2007 17:49:37 -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 12180 invoked by uid 99); 18 Apr 2007 17:49:37 -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:49:37 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [70.103.251.107] (HELO scalix0.corp.dotster.net) (70.103.251.107) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Apr 2007 10:49:30 -0700 Received: from scalix0.corp.dotster.net (localhost.localdomain [127.0.0.1]) by scalix0.corp.dotster.net (8.13.1/8.13.1) with ESMTP id l3IHn9xm005884 for ; Wed, 18 Apr 2007 10:49:09 -0700 Received: from scalix0.corp.dotster.net (root@localhost) by scalix0.corp.dotster.net (8.13.1/8.13.1/Submit) with ESMTP id l3IHn9A0005883 for ; Wed, 18 Apr 2007 10:49:09 -0700 Received: from localhost.localdomain (dotster-vip2.corp.dotster.net 70.103.251.2) by scalix0.corp.dotster.net (Scalix SMTP Relay 10.0.1.3) via ESMTP; Wed, 18 Apr 2007 10:49:09 -0700 (PDT) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.13.5.20060308/8.13.5/Debian-3ubuntu1.1) with ESMTP id l3IHn8jP019775 for ; Wed, 18 Apr 2007 10:49:08 -0700 Received: (from brmiller@localhost) by localhost.localdomain (8.13.5.20060308/8.13.5/Submit) id l3IHn8PL019774 for torque-user@db.apache.org; Wed, 18 Apr 2007 10:49:08 -0700 Date: Wed, 18 Apr 2007 10:49:08 -0700 From: "Brendan Miller" To: Apache Torque Users List Message-ID: <20070418174907.GA19755@localhost.localdomain> In-Reply-To: <8F5843B903F59D4C8C6806BB49A3911902F75E48@dukece-mail3.dukece.com> References: <20070418153123.GA18982@localhost.localdomain> References: <8F5843B903F59D4C8C6806BB49A3911902F75E48@dukece-mail3.dukece.com> Subject: Re: Wrong SQL generation from Criteria x-scalix-Hops: 1 x-scalix-Authenticated-Sender: brmiller@dotster.com at 70.103.251.2 User-Agent: Mutt/1.5.11 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Disposition: inline X-Spam-Checker-Version: SpamAssassin 3.0.5 (2005-11-28) on scalix0.corp.dotster.net X-Virus-Checked: Checked by ClamAV on apache.org X-Old-Spam-Status: No, score=-102.8 required=6.0 tests=ALL_TRUSTED, USER_IN_WHITELIST autolearn=failed version=3.0.5 Thanks for the thorough explanation--this would make a great addition to the documentation if there was an "How things work" section. I see now why Torque is doing what it is doing. What I was hoping to achieve was a list of records grouped by one or more of the fields. That way I could let the database group them and I could process the list in my code with a valid assumption as to the ordering of the objects. Is this possible? Brendan On Wed, Apr 18, 2007 at 01:30:45PM -0400, Greg Monroe wrote: > > OK, pardon me if you know some of this... just being > complete... > > The SQL Group By clause is defined for use with SQL > summary functions. E.g.: > > Select category, product, count(product) as in_stock > from products > 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 > 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 > 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 = new Criteria(); > c.addSelectColumn(ProductsPeer.CATEGORY); > c.addSelectColumn(ProductsPeer.PRODUCT); > c.addAsColumn("in_stock", "COUNT(" > +ProductsPeer.PRODUCT+")"); > c.addGroupByColumn(ProductsPeer.CATEGORY); > c.addGroupByColumn(ProductsPeer.PRODUCT); > > List results = BasePeer.doSelect(c); > if ( results.size() > 0 ) { > Record rec = (Record) results.get(0); > category = rec.getValue(1).asString(); > product = rec.getValue(2).asString(); > in_stock = rec.getValue(3).asInt(); > } > > This is almost the same as going back to standard > results sets with the exception that you get > protection against schema changes. Eg., if a > column or table is dropped, your code won't compile > so you know somethings wrong. --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org For additional commands, e-mail: torque-user-help@db.apache.org