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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
