Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of greg.monroe@dukece.com
 designates 152.3.166.24 as permitted sender)
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 14:23:12 -0400
Message-ID: <8F5843B903F59D4C8C6806BB49A3911902F75EC7@dukece-mail3.dukece.com>
In-Reply-To: <20070418174907.GA19755@localhost.localdomain>
Thread-Topic: Wrong SQL generation from Criteria
Thread-Index: AceB4fkTDvfk4N0NR3agGAfN6ffO/AAA+hGA
References: <8F5843B903F59D4C8C6806BB49A3911902F75E48@dukece-mail3.dukece.com>
 <20070418174907.GA19755@localhost.localdomain>
From: "Greg Monroe" <Greg.Monroe@DukeCE.com>
To: "Apache Torque Users List" <torque-user@db.apache.org>

Sounds like you want to use the:

  Criteria.addAscendingOrderBy(Column)
  Criteria.addDecendingOrderBy(Column)

methods.  Calling these in sequence will sort your=20
results.  E.g.:

   c.addAscendingOrderBy(TablePeer.COL_1);
   c.addDescendingOrderBy(TablePeer.COL_2);

Will generate SQL like:

  Select * from Table=20
     order by Table.COL_1 ASC TABLE.COL_2 DESC


> -----Original Message-----
> From: Brendan Miller [mailto:bmiller@dotster.com]=20
> Sent: Wednesday, April 18, 2007 1:49 PM
> To: Apache Torque Users List
> Subject: Re: Wrong SQL generation from Criteria
>=20
>=20
> Thanks for the thorough explanation--this would make a great=20
> addition to the documentation if there was an "How things work"
> section.
>=20
> I see now why Torque is doing what it is doing.  What I was=20
> hoping to achieve was a list of records grouped by one or=20
> more of the fields.  That way I could let the database group=20
> them and I could process the list in my code with a valid=20
> assumption as to the ordering of the objects.  Is this possible?
>=20
> Brendan
>=20
>=20
> On Wed, Apr 18, 2007 at 01:30:45PM -0400, Greg Monroe wrote:
> >=20
> > OK, pardon me if you know some of this... just being complete...
> >=20
> > The SQL Group By clause is defined for use with SQL summary=20
> functions. =20
> > E.g.:
> >=20
> >   Select category, product, count(product) as in_stock
> >     from products=20
> >     group by category, product
> >=20
> > If you use a GROUP BY clause, the only valid non-summary=20
> fields on the=20
> > select are the ones listed in the group by clause.
> >=20
> > Torque is for the most part a record retrieval based OM. =20
> E.g., when=20
> > you ask for something, you get a record object that is fully=20
> > populated.  This means that when you use the RecordPeer.doSelect(c)=20
> > methods, Torque needs to retreive all field values to fully=20
> populate=20
> > the record object.  So, it HAS to add all the table fields to the=20
> > select.
> >=20
> > If you want to use Criteria.groupBy() and the normal Peer=20
> methods, you=20
> > need to include ALL the fields.
> > However, by the time you do that, it's pretty much the same=20
> as doing=20
> > an AddAscendingOrderBy.
> >=20
> > If you want to do a summary type function, you can do this=20
> a couple of=20
> > ways.  If it's just a count, you can use the CountHelper=20
> function.  If=20
> > it's more than that you can use BasePeer.doSelect(c) and=20
> get the data=20
> > from the Village Record objects returned.  E.g.
> >=20
> >   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);
> >=20
> >   List<Record> 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();
> >   }
> >=20
> > This is almost the same as going back to standard results sets with=20
> > the exception that you get protection against schema=20
> changes.  Eg., if=20
> > a column or table is dropped, your code won't compile so you know=20
> > somethings wrong.
>=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