Re: [SQLObject] Group By multiple columns

[Oleg Broytmann <phd@ph...>]

On Thu, Jan 25, 2007 at 01:54:00PM -0600, Jim Steil wrote:
> I'm trying to group my result set by more than 1 column.  I'm doing so with
> the following:
>  
> from sqlobject.sqlbuilder import *
> from model import CheckLog
>  
> conn = CheckLog._connection
> query = Select([CheckLog.q.account, CheckLog.q.subAccount,
> CheckLog.q.accountName, func.Sum(CheckLog.q.amount)],
> groupBy=[CheckLog.q.account, CheckLog.q.subAccount, CheckLog.q.accountName],
> orderBy=CheckLog.q.account) x = conn.queryAll(conn.sqlrepr(query))
>  
> .which nets me this SQL:
>  
> SELECT CheckLog.account, CheckLog.subAccount, CheckLog.accountName,
> Sum(CheckLog.amount) FROM CheckLog GROUP BY (CheckLog.account,
> CheckLog.subAccount, CheckLog.accountName) ORDER BY CheckLog.account
>  
> MySQL pukes on this and gives me:
>  
> 1241, 'Operand should contain 1 column(s)'
>  
> If I remove the parentheses around the columns in the Group By clause,
> everything works.  Is there anything I can do to get SO to NOT put the
> parens around that clause?

   These parens are put by SequenceConverter() from converters.py but, of
course, you cannot just remove the parens - they are needed in other
queries.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Group By multiple columns

[Jim Steil <steil@ma...>]

Hi:
 
I'm trying to group my result set by more than 1 column.  I'm doing so with
the following:
 
from sqlobject.sqlbuilder import *
from model import CheckLog
 
conn = CheckLog._connection
query = Select([CheckLog.q.account, CheckLog.q.subAccount,
CheckLog.q.accountName, func.Sum(CheckLog.q.amount)],
groupBy=[CheckLog.q.account, CheckLog.q.subAccount, CheckLog.q.accountName],
orderBy=CheckLog.q.account) x = conn.queryAll(conn.sqlrepr(query))
 
.which nets me this SQL:
 
SELECT CheckLog.account, CheckLog.subAccount, CheckLog.accountName,
Sum(CheckLog.amount) FROM CheckLog GROUP BY (CheckLog.account,
CheckLog.subAccount, CheckLog.accountName) ORDER BY CheckLog.account
 
MySQL pukes on this and gives me:
 
1241, 'Operand should contain 1 column(s)'
 
If I remove the parentheses around the columns in the Group By clause,
everything works.  Is there anything I can do to get SO to NOT put the
parens around that clause?
 
      -Jim
 
 
 
 

Re: [SQLObject] Group By multiple columns

[Oleg Broytmann <phd@ph...>]

On Thu, Jan 25, 2007 at 01:54:00PM -0600, Jim Steil wrote:
> I'm trying to group my result set by more than 1 column.  I'm doing so with
> the following:
>  
> from sqlobject.sqlbuilder import *
> from model import CheckLog
>  
> conn = CheckLog._connection
> query = Select([CheckLog.q.account, CheckLog.q.subAccount,
> CheckLog.q.accountName, func.Sum(CheckLog.q.amount)],
> groupBy=[CheckLog.q.account, CheckLog.q.subAccount, CheckLog.q.accountName],
> orderBy=CheckLog.q.account) x = conn.queryAll(conn.sqlrepr(query))
>  
> .which nets me this SQL:
>  
> SELECT CheckLog.account, CheckLog.subAccount, CheckLog.accountName,
> Sum(CheckLog.amount) FROM CheckLog GROUP BY (CheckLog.account,
> CheckLog.subAccount, CheckLog.accountName) ORDER BY CheckLog.account
>  
> MySQL pukes on this and gives me:
>  
> 1241, 'Operand should contain 1 column(s)'
>  
> If I remove the parentheses around the columns in the Group By clause,
> everything works.  Is there anything I can do to get SO to NOT put the
> parens around that clause?

   These parens are put by SequenceConverter() from converters.py but, of
course, you cannot just remove the parens - they are needed in other
queries.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Group By multiple columns

[Oleg Broytmann <phd@ph...>]

On Thu, Jan 25, 2007 at 02:36:54PM -0600, Jim Steil wrote:
> But, it doesn't put parens around the field list.

   That's because the field list is generated in DBAPI in dbconnection.py.

> Any suggestions on what I
> could do to get this to work?

1. Temporary replace the converter at runtime.
2. File a bug-report in Trac or SF tracker.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            phd@...
           Programmers don't die, they just GOSUB without RETURN.