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 17:30:45 GMT

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.

> -----Original Message-----
> From: Brendan Miller [mailto:bmiller@dotster.com] 
> Sent: Wednesday, April 18, 2007 11:31 AM
> To: torque-user@db.apache.org
> Subject: Wrong SQL generation from Criteria
> 
> 
> Maybe I'm doing something very poor, but I thought I had used 
> these constructs successfully before.  (This is Oracle, BTW.)
> 
> My RESOURCE_LOCK table schema:
> 
>     <table name="RESOURCE_LOCK">
>         <column name="ITEM_ID" size="27" type="DECIMAL" 
> javaName="ItemID"/>
>         <column name="RESOURCE_DESCRIPTOR" primaryKey="true"
>             required="true" size="100" type="VARCHAR" 
> javaName="ResourceDescriptor"/>
>         <column name="ENTRY_TIMESTAMP" type="TIMESTAMP" 
> javaName="EntryTimestamp"/>
>         <column name="LOCKER_ID" size="27" type="DECIMAL" 
> javaName="LockerID"/>
>         <column name="LOCKER_TYPE" size="100" type="VARCHAR" 
> javaName="LockerType"/>
>         <column name="RESOURCE_TYPE" size="27" type="DECIMAL" 
> javaName="ResourceTypeID"/>
>     </table>
> 
> My code:
> 
>     Criteria crit = new Criteria();
>     crit.addGroupByColumn(ResourceLockPeer.LOCKER_TYPE);
>     List<ResourceLock> locks = ResourceLockPeer.doSelect(crit);
> 
> The observed SQL from log4j when turned on:
> 
>     SELECT RESOURCE_LOCK.ITEM_ID, 
> RESOURCE_LOCK.RESOURCE_DESCRIPTOR, 
> RESOURCE_LOCK.ENTRY_TIMESTAMP, RESOURCE_LOCK.LOCKER_ID, 
> RESOURCE_LOCK.LOCKER_TYPE, RESOURCE_LOCK.RESOURCE_TYPE FROM 
> DNS.RESOURCE_LOCK GROUP BY RESOURCE_LOCK.LOCKER_TYPE
> 
> The exception:
> 
>     org.apache.torque.TorqueException: java.sql.SQLException: 
> ORA-00979: not a GROUP BY expression
> 
> DNS is the torque.database.schema, but I don't understand why 
> it is inconsistent in adding the database prefix in the FROM 
> clause but not the GROUP clause.
> 
> Joins are similarly affected:
> 
>     Criteria crit = new Criteria();
>     crit.add(ResourceLockPeer.LOCKER_ID, new Long(2115));
>     crit.add(ResourceLockPeer.LOCKER_TYPE, "RECURBILLINGPROC");
>     crit.addJoin(ResourceLockPeer.RESOURCE_DESCRIPTOR, 
> RecurBillingPeer.ID);
>     crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_ID);
>     crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_PAYMENT_ID);
>     List<RecurBilling> recBilling = RecurBillingPeer.doSelect(crit);
> 
> The SQL is even worse, as it contains both the tablename and 
> dbprefix.tablename in the where clause, which gives Oracle 
> confusion fits.
> 
>     SELECT <..all RECUR_BILLING fields..> FROM RESOURCE_LOCK, 
> RECUR_BILLING, DNS.RECUR_BILLING, DNS.RESOURCE_LOCK WHERE 
> RESOURCE_LOCK.RESOURCE_DESCRIPTOR=RECUR_BILLING.ID AND 
> RESOURCE_LOCK.LOCKER_TYPE='RECURBILLINGPROC' AND 
> RESOURCE_LOCK.LOCKER_ID=2115 GROUP BY 
> RECUR_BILLING.ACCOUNT_ID, RECUR_BILLING.ACCOUNT_PAYMENT_ID
> 
> Brendan
> 
> ---------------------------------------------------------------------
> 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