db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject RE: Wrong SQL generation from Criteria
Date Wed, 18 Apr 2007 18:23:12 GMT
Sounds like you want to use the:

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

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

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

Will generate SQL like:

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



> -----Original Message-----
> From: Brendan Miller [mailto:bmiller@dotster.com] 
> Sent: Wednesday, April 18, 2007 1:49 PM
> To: Apache Torque Users List
> Subject: Re: Wrong SQL generation from Criteria
> 
> 
> 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<Record> 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
> 
> 

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


Mime
View raw message