Skip to first unread message
Niphlod
Aug 5, 2013, 2:56:36 PM8/5/13
to web2py-d...@googlegroups.com
Hi @all. In regards of this thread
https://groups.google.com/d/msg/web2py/05waBr6aBxU/y91mwAEy-wYJ
the code needs rethinking as a whole.
1st issue: any query that counts the records (except SQLite, that doesn't make any sense out of such a query) is mediumly borked. T-SQL needs an aliased subquery to work things out in most dbs....
this
select count(*) from (subselect)
needs to be
select count(*) from (subselect) aliased_subselect
so, at least, the code counting the rows doesn't go into exception (sqlhtml.py, line 1838)
'select count(*) from (%s) aliased_resultset;' %
works
so, 1st issue patched and ready to be used.
2nd issue: grid's default behaviour is to include in a select the id of the table (lines 1932-1935). Now, and that's the "majorly borked" part... . If you have a query with a groupby, any field requested HAS to be included in the groupby itself or be an aggregate. We can't (of course) include the PK in the groupby field, because it will simply not group anything.
Don't let SQLite fool you: when it does a groupby, it just "slims down" the resultset assigning to the unaggregate columns the first value returned by the driver. So, ok, it doesn't throw an exception, but the resultset is not meaningful.
Here's the big deal: what you edit when you press the edit button on a groupby(ed) grid ?
In SQLite, a random record picked out of the group.
In postgresql the whole grid fails badly (as it should), as long as you don't specify any field_id field .
The only way to have it working is in fact using one of the groupby(ed) fields as a field_id, but that doesn't make sense either, given the lack of support for editing a bunch of rows or to edit anything that is not a PK. So, it works but you can't view, you can't edit, you can't delete.
tl;dr: should we disable editing, viewing, deleting when groupby is used ? Do we even want to support such a flaky behaviour of the grid (meaning that to make it work you need to do groupby=field, field_id=field) ?
https://groups.google.com/d/msg/web2py/05waBr6aBxU/y91mwAEy-wYJ
the code needs rethinking as a whole.
1st issue: any query that counts the records (except SQLite, that doesn't make any sense out of such a query) is mediumly borked. T-SQL needs an aliased subquery to work things out in most dbs....
this
select count(*) from (subselect)
needs to be
select count(*) from (subselect) aliased_subselect
so, at least, the code counting the rows doesn't go into exception (sqlhtml.py, line 1838)
'select count(*) from (%s) aliased_resultset;' %
works
so, 1st issue patched and ready to be used.
2nd issue: grid's default behaviour is to include in a select the id of the table (lines 1932-1935). Now, and that's the "majorly borked" part... . If you have a query with a groupby, any field requested HAS to be included in the groupby itself or be an aggregate. We can't (of course) include the PK in the groupby field, because it will simply not group anything.
Don't let SQLite fool you: when it does a groupby, it just "slims down" the resultset assigning to the unaggregate columns the first value returned by the driver. So, ok, it doesn't throw an exception, but the resultset is not meaningful.
Here's the big deal: what you edit when you press the edit button on a groupby(ed) grid ?
In SQLite, a random record picked out of the group.
In postgresql the whole grid fails badly (as it should), as long as you don't specify any field_id field .
The only way to have it working is in fact using one of the groupby(ed) fields as a field_id, but that doesn't make sense either, given the lack of support for editing a bunch of rows or to edit anything that is not a PK. So, it works but you can't view, you can't edit, you can't delete.
tl;dr: should we disable editing, viewing, deleting when groupby is used ? Do we even want to support such a flaky behaviour of the grid (meaning that to make it work you need to do groupby=field, field_id=field) ?
villas
Aug 5, 2013, 7:03:07 PM8/5/13
to web2py-d...@googlegroups.com
I kind of like the idea of an aggregate grid without any edit capability. There are lots of occasions when that would be required, e.g. customers with a/c balances, and it would be great to have them paginated etc via the grid and with the possibility of link buttons to redirect to other forms/functions.
So it would be a nice feature and I agree any row which doesn't have it's own unique row id could be readonly.
So it would be a nice feature and I agree any row which doesn't have it's own unique row id could be readonly.
Massimo DiPierro
Aug 6, 2013, 2:55:23 AM8/6/13
to web2py-d...@googlegroups.com
I do not think there should be two grids (one for aggregates and one no). If we want to support aggregates we should do it with the current grid and disable the create/edit/delete/details buttons.
--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
Reply all
Reply to author
Forward
0 new messages