db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Miller" <bmil...@dotster.com>
Subject Re: Wrong SQL generation from Criteria
Date Wed, 18 Apr 2007 17:49:08 GMT

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


Mime
View raw message