[web2py] Re: groupby in sqlform.grid resulted into unsupported query
I am also having the same issue. I am using 2.5.1-stable+timestamp.2013.06.06.15.39.19 -- On Tuesday, August 6, 2013 4:08:38 AM UTC+5:30, villas wrote: 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...? -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
@all: you can't have a grid displaying all the fields of the auth_user table if you're grouping by first_name On Sunday, August 4, 2013 11:38:42 PM UTC+2, Massimo Di Pierro wrote: 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. On Sunday, 4 August 2013 15:13:48 UTC-5, villas wrote: 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... On Sunday, 4 August 2013 06:11:01 UTC+1, Matt Grham wrote: Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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? On Monday, 5 August 2013 09:54:08 UTC+1, Niphlod wrote: @all: you can't have a grid displaying all the fields of the auth_user table if you're grouping by first_name On Sunday, August 4, 2013 11:38:42 PM UTC+2, Massimo Di Pierro wrote: 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. On Sunday, 4 August 2013 15:13:48 UTC-5, villas wrote: 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... On Sunday, 4 August 2013 06:11:01 UTC+1, Matt Grham wrote: Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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 ? On Monday, August 5, 2013 11:17:04 AM UTC+2, villas wrote: 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? On Monday, 5 August 2013 09:54:08 UTC+1, Niphlod wrote: @all: you can't have a grid displaying all the fields of the auth_user table if you're grouping by first_name On Sunday, August 4, 2013 11:38:42 PM UTC+2, Massimo Di Pierro wrote: 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. On Sunday, 4 August 2013 15:13:48 UTC-5, villas wrote: 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... On Sunday, 4 August 2013 06:11:01 UTC+1, Matt Grham wrote: Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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...? -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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... On Sunday, 4 August 2013 06:11:01 UTC+1, Matt Grham wrote: Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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. On Sunday, 4 August 2013 15:13:48 UTC-5, villas wrote: 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... On Sunday, 4 August 2013 06:11:01 UTC+1, Matt Grham wrote: Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
Fields to be shown: fields = ( db[str_tableName].Man_ID, db[str_tableName].Man_Type) On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: 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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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) On Saturday, August 3, 2013 8:52:21 PM UTC+2, Matt Grham wrote: Fields to be shown: fields = ( db[str_tableName].Man_ID, db[str_tableName].Man_Type) On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: 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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
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_IDMan_Type 8BC On Saturday, August 3, 2013 12:01:45 PM UTC-7, Niphlod wrote: 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) On Saturday, August 3, 2013 8:52:21 PM UTC+2, Matt Grham wrote: Fields to be shown: fields = ( db[str_tableName].Man_ID, db[str_tableName].Man_Type) On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: 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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
it may work on some backends (namely, SQLite), but that query doesn't rally make sense. What do you need as a result precisely ? On Saturday, August 3, 2013 9:25:44 PM UTC+2, Matt Grham wrote: 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_IDMan_Type 8BC On Saturday, August 3, 2013 12:01:45 PM UTC-7, Niphlod wrote: 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) On Saturday, August 3, 2013 8:52:21 PM UTC+2, Matt Grham wrote: Fields to be shown: fields = ( db[str_tableName].Man_ID, db[str_tableName].Man_Type) On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: 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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
I am using mysql. Using my query in grid results into a table like the following: 3 records found Man_IDMan_Type 8BC 8BC 8BC I want to add a groupby statement which will provide the following result: Man_IDMan_Type 8BC Thanks, Matt On Saturday, August 3, 2013 12:40:31 PM UTC-7, Niphlod wrote: it may work on some backends (namely, SQLite), but that query doesn't rally make sense. What do you need as a result precisely ? On Saturday, August 3, 2013 9:25:44 PM UTC+2, Matt Grham wrote: 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_IDMan_Type 8BC On Saturday, August 3, 2013 12:01:45 PM UTC-7, Niphlod wrote: 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) On Saturday, August 3, 2013 8:52:21 PM UTC+2, Matt Grham wrote: Fields to be shown: fields = ( db[str_tableName].Man_ID, db[str_tableName].Man_Type) On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: 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) ? On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: I have a table like: 3 records found Man_IDMan_Type 8BC 8BC 8BC 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_IDMan_Type 8BC * 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 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: groupby in sqlform.grid resulted into unsupported query
Could be but I am trying to do it in SQLFORM.grid statement. How can I do that? On Saturday, August 3, 2013 4:04:40 PM UTC-7, villas wrote: For the example you provide, probably better with: distinct=True That is the usual SQL method of suppressing duplicate rows. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
