groupby in sqlform.grid resulted into unsupported query

[Matt Grham]

I have a table like:

3 records found
Man_ID    Man_Type 
8                BC   
8                BC   
8                BC  

When I try to group by Man_ID, it does the grouping but it prints Unsupported Query on top of the table

Unsupported query
Man_ID    Man_Type 
8                BC 

I used the following statement:

    grid = SQLFORM.grid(query=query, fields=fields, headers=myheaders,
                create=False, deletable=False, editable=False, details=False, csv=False,
                maxtextlength=64,
                paginate=25,
                ui='jquery-ui',
                user_signature=False,
                searchable=searchable,
                sortable=sortable,
                groupby=db.mytable.Man_ID
                )

Can you help me here?

Thanks,

Matt

[Niphlod]

what are the "fields" you're requiring ? Do you know that any groupby type of query can only include the group by column(s) and all the other fields need to be an aggregate of the "basic" field (such as count, min, max, etc) ?

[Matt Grham]

Fields to be shown:

fields = (  db[str_tableName].Man_ID,
               db[str_tableName].Man_Type)

[Niphlod]

here's the answer: there's no way you can do

select man_id, man_type
from tablename
group by man_id

and expecting it to return something meaningful.

man_type needs to be either included in your groupby (in which case, it equals a "distinct" type of query) or used as an aggregate (first, last, count, etc etc etc)

[Matt Grham]

But the following works perfectly:

    fields=['Man_ID','Man_Type']
    sel=[db[str_tableName][field] for field in fields]

    rows = db(query).select(*sel,groupby=db[str_tableName].Man_ID)

It does not work in sqlform.grid. Even in grid, it actually works but it also writes Unsupported Query on top of the table:

Unsupported query
Man_ID    Man_Type 
8                BC 

[Niphlod]

it may work on some backends (namely, SQLite), but that query doesn't reeeeally make sense. What do you need as a result precisely ?

[Matt Grham]

I am using mysql. Using my query in grid results into a table like the following:

3 records found
Man_ID    Man_Type  
8                BC    
8                BC    
8                BC

I want to add a groupby statement which will provide the following result:

Man_ID    Man_Type  
8                BC    

Thanks,

Matt

[villas]

For the example you provide,  probably better with:  distinct=True
That is the usual SQL method of suppressing duplicate rows.

[Matt Grham]

Could be but I am trying to do it in SQLFORM.grid statement. How can I do that?

[villas]

I made a few tests but it appears that groupby produces buggy SQL when used with grid,  so I'm not sure what you can do there.  Maybe someone else could try it

Here is a simple example:

def testgrid():
    return dict(grid=SQLFORM.grid(db.auth_user, groupby=db.auth_user.first_name,orderby=db.auth_user.first_name ))

at one point sqlhtml.py produces this invalid SQL (for firebird):

select count(*) from (SELECT  count(*) FROM auth_user WHERE (auth_user.id IS NOT
 NULL) GROUP BY auth_user.first_name)

Not sure what's going on,  but it's not going to work like that...

[Massimo Di Pierro]

Which web2py version. This may be fixed in trunk. I see that ORDERBY is missing. I think that is the problem. Please check trunk and let us know. If still a problem, please open an issue.

[Niphlod]

@all: you can't have a grid displaying all the fields of the auth_user table if you're grouping by first_name....

[villas]

This wasn't a 'real life' example,  I was just trying to demonstrate that some of the SQL doesn't seem to be valid.
It doesn't work if you specify the field either:  fields=[db.auth_user.first_name]

The question is this:  should it be possible to make such a SQLFORM.grid?  If so,  how?

[Niphlod]

yep, it's majorly borked. the fact that with sqlite it results in a correct query (but a totally unuseful resultset) shouldn't matter.
I'm trying to figure out the best way to handle that in the code, but in my POV any query with a groupby should disable any editing in the grid.... how do you edit a row that is the result of an aggregated recordset ?

[villas]

IMO anyone who want to have a grid populated by aggregate functions should be rolling their own,  but that isn't the issue raised by the OP. 

Yes, anything which doesn't have a unique record id is probably not going to be editable,  on a practical basis.  I'm not convinced that SQLFORM.gird should be offering a groupby clause,  but I guess we already have one,  in which case just a view-only grid is the way forward...?

[Jayakumar Bellie]

I am also having the same issue.

I am using "2.5.1-stable+timestamp.2013.06.06.15.39.19"

------------------