Re: [web2py] Re: .sum() and .count() in grid?
Johann, I developed an alternative to grid/smartgrid which has most of the features. I can put it on github if you like. Caution: not documented. But I'll put up a sample controller that uses it if you like. The main drawback is the search widget isn't nearly as slick as smartgrid's. And you do have to create your own query parser. But it does have a selector for the number of rows per page. On Monday, April 8, 2013 3:11:20 AM UTC-4, Johann Spies wrote: I can use SQLTABLE for this and then have to write some code for pagination and search options - all which are already in the SQLFORM.grid available. In this case addition, deletion and editing will be avoided. Regards Johann On 4 April 2013 15:17, Niphlod nip...@gmail.com javascript: wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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+un...@googlegroups.com javascript:. For more options, visit https://groups.google.com/groups/opt_out. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
Please! On Wednesday, 10 April 2013 06:36:45 UTC-5, Cliff Kachinske wrote: Johann, I developed an alternative to grid/smartgrid which has most of the features. I can put it on github if you like. Caution: not documented. But I'll put up a sample controller that uses it if you like. The main drawback is the search widget isn't nearly as slick as smartgrid's. And you do have to create your own query parser. But it does have a selector for the number of rows per page. On Monday, April 8, 2013 3:11:20 AM UTC-4, Johann Spies wrote: I can use SQLTABLE for this and then have to write some code for pagination and search options - all which are already in the SQLFORM.grid available. In this case addition, deletion and editing will be avoided. Regards Johann On 4 April 2013 15:17, Niphlod nip...@gmail.com wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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+un...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
Massimo, do you have a preference as far as licensing? I don't want to discourage people from using it, but if anyone makes a much-needed improvement I would like to get benefit of it. If the ideas are worthy of incorporation into grid or smartgrid, that would be great. I don't do works for hire, so I own the code completely. I'll post in this thread once I've cleaned up the code and uploaded it to github. I wrote it without referencing grid or smartgrid, so it does not take advantage of shortcuts in those two methods. But it does have selectable number of rows per page, and it keeps any query, orderby and rows per page parms in the session, so if you go off on an excursion to edit something, it retrieves the index just as you left it. Also things like the console maker are split off into separate functions. I thought this would provide a couple of advantages. If you wanted a console or paginator on some page, you could easily incorporate it. At the same time, if you wanted to substitute a different console building function, you could easily do so. Cliff Kachinske On Wednesday, April 10, 2013 9:18:17 AM UTC-4, Massimo Di Pierro wrote: Please! On Wednesday, 10 April 2013 06:36:45 UTC-5, Cliff Kachinske wrote: Johann, I developed an alternative to grid/smartgrid which has most of the features. I can put it on github if you like. Caution: not documented. But I'll put up a sample controller that uses it if you like. The main drawback is the search widget isn't nearly as slick as smartgrid's. And you do have to create your own query parser. But it does have a selector for the number of rows per page. On Monday, April 8, 2013 3:11:20 AM UTC-4, Johann Spies wrote: I can use SQLTABLE for this and then have to write some code for pagination and search options - all which are already in the SQLFORM.grid available. In this case addition, deletion and editing will be avoided. Regards Johann On 4 April 2013 15:17, Niphlod nip...@gmail.com wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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+un...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
For the code to be incorporated in web2py (grid/smartgrid/etc.) you should sign the web2py contributor agreement which means you can do what you want with the code but I can too. If you release it under BSD or MIT or Apache or LGPL license, it may be considered for the contrib folder. Massimo On Wednesday, 10 April 2013 21:29:45 UTC-5, Cliff Kachinske wrote: Massimo, do you have a preference as far as licensing? I don't want to discourage people from using it, but if anyone makes a much-needed improvement I would like to get benefit of it. If the ideas are worthy of incorporation into grid or smartgrid, that would be great. I don't do works for hire, so I own the code completely. I'll post in this thread once I've cleaned up the code and uploaded it to github. I wrote it without referencing grid or smartgrid, so it does not take advantage of shortcuts in those two methods. But it does have selectable number of rows per page, and it keeps any query, orderby and rows per page parms in the session, so if you go off on an excursion to edit something, it retrieves the index just as you left it. Also things like the console maker are split off into separate functions. I thought this would provide a couple of advantages. If you wanted a console or paginator on some page, you could easily incorporate it. At the same time, if you wanted to substitute a different console building function, you could easily do so. Cliff Kachinske On Wednesday, April 10, 2013 9:18:17 AM UTC-4, Massimo Di Pierro wrote: Please! On Wednesday, 10 April 2013 06:36:45 UTC-5, Cliff Kachinske wrote: Johann, I developed an alternative to grid/smartgrid which has most of the features. I can put it on github if you like. Caution: not documented. But I'll put up a sample controller that uses it if you like. The main drawback is the search widget isn't nearly as slick as smartgrid's. And you do have to create your own query parser. But it does have a selector for the number of rows per page. On Monday, April 8, 2013 3:11:20 AM UTC-4, Johann Spies wrote: I can use SQLTABLE for this and then have to write some code for pagination and search options - all which are already in the SQLFORM.grid available. In this case addition, deletion and editing will be avoided. Regards Johann On 4 April 2013 15:17, Niphlod nip...@gmail.com wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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+un...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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: .sum() and .count() in grid?
On Saturday, 6 April 2013 00:04:24 UTC+2, Niphlod wrote:
You just need to:
- include a pkey, better if resembles a sequence, and have a Field of
type 'id' matching it
This is not very usable. Here is an example:
In postgresql:
CREATE OR REPLACE VIEW sabinet.sabinet_records_per_journal AS
SELECT sabinet.jt AS journal, count(*) AS records
FROM sabinet.sabinet
GROUP BY sabinet.jt
ORDER BY count(*) DESC;
The pkey is 'id' in all DAL objects. I cannot add it to this view because
then I will have to include it in the 'group by' which will destroy the
purpose of the query.
I have tried with the following model:
migrate= False
db.define_table(sabinet_records_per_journal,
Field('journal'),
Field('records', 'integer'),
migrate = migrate)
But I get:
Query Not Supported: current transaction is aborted, commands ignored until
end of transaction block
No records found
When I run try the following controller:
def sabinet_records_per_journal():
response.view = 'isi/isi_grid.html'
opskrif=SABINET-data
data = SQLFORM.grid(db.sabinet_records_per_journal, deletable = False,
editable = False,
details = False,
csv = False,
searchable = False)
return dict(data=data, opskrif=opskrif)
Regards
Johann
--
---
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: .sum() and .count() in grid?
try with SELECT row_number(ORDER BY records DESC) as id, a.* FROM ( SELECT sabinet.jt AS journal, count(*) AS records FROM sabinet.sabinet GROUP BY sabinet.jt ) a -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
That causes an error: ERROR: syntax error at or near ORDER LINE 2:SELECT row_number(ORDER BY records DESC) as id, a... ^ Regards Johann On 9 April 2013 15:11, Niphlod niph...@gmail.com wrote: try with SELECT row_number(ORDER BY records DESC) as id, a.* FROM ( SELECT sabinet.jt AS journal, count(*) AS records FROM sabinet.sabinet GROUP BY sabinet.jt ) a -- --- 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. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
well, I don't have a postgres installation at hand, but the gist of it should be clear if you can't come up with a solution I'll test it at home. On Tuesday, April 9, 2013 3:26:03 PM UTC+2, Johann Spies wrote: That causes an error: ERROR: syntax error at or near ORDER LINE 2:SELECT row_number(ORDER BY records DESC) as id, a... ^ Regards Johann On 9 April 2013 15:11, Niphlod nip...@gmail.com javascript: wrote: try with SELECT row_number(ORDER BY records DESC) as id, a.* FROM ( SELECT sabinet.jt AS journal, count(*) AS records FROM sabinet.sabinet GROUP BY sabinet.jt ) a -- --- 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+un...@googlegroups.com javascript:. For more options, visit https://groups.google.com/groups/opt_out. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
I can use SQLTABLE for this and then have to write some code for pagination and search options - all which are already in the SQLFORM.grid available. In this case addition, deletion and editing will be avoided. Regards Johann On 4 April 2013 15:17, Niphlod niph...@gmail.com wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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. -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
Re: [web2py] Re: .sum() and .count() in grid?
Thanks for the explanation of using views Niphod. I have used Postgresql's
select into... in the past for similar situations, but using views seems to
be a better option.
Regards
Johann
On 6 April 2013 00:04, Niphlod niph...@gmail.com wrote:
often referred as materialized views for the ones who can spare me a
lecture about what are exactly those :P, let's say you have an
iper-complicated query that reconstructs to a table that you want to
show in a grid just for searching purposes (other purposes not listed,
sorry, they make absolute no sense)
the dba that is in you says I'll write a view for my iper-complicated
query ...
so, you code something like
create view iperquery as
select something_that_resemble_a_sequence, bla1, bla2, bla3,
.
from bla1
.
left outer join (
select blablabla
group by blablabla
)
.
where max(whatever) 0
Perfect. now you can do a select * from iperquery and your db engine
does all the work.
To use iperquery as a table (read-only mode, for write only go to see
docs about your engine support for keyed views, another story I'm NOT going
to cover here)
db.define_table('iperquery',
Field('something_that_resemble_a_sequence', 'id')
Field('bla1'),
Field('bla2'),
.,
migrate=False
)
the migrate=False bit is the key: web2py won't try creating it and assumes
there's a table ready to be used.
You just need to:
- include a pkey, better if resembles a sequence, and have a Field of
type 'id' matching it
- tune the Field types accordingly to the type of column of your view
On Friday, April 5, 2013 11:24:23 PM UTC+2, Jurgis Pralgauskis wrote:
You'll better code a view and access it as a fake table.
are there examplses of this?
Is it possible to show virtual fields in grid (in appadmin they don't
show up)?
--
---
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.
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
--
---
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: .sum() and .count() in grid?
You'll better code a view and access it as a fake table. are there examplses of this? Is it possible to show virtual fields in grid (in appadmin they don't show up)? -- --- 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: .sum() and .count() in grid?
often referred as materialized views for the ones who can spare me a
lecture about what are exactly those :P, let's say you have an
iper-complicated query that reconstructs to a table that you want to
show in a grid just for searching purposes (other purposes not listed,
sorry, they make absolute no sense)
the dba that is in you says I'll write a view for my iper-complicated
query ...
so, you code something like
create view iperquery as
select something_that_resemble_a_sequence, bla1, bla2, bla3,
.
from bla1
.
left outer join (
select blablabla
group by blablabla
)
.
where max(whatever) 0
Perfect. now you can do a select * from iperquery and your db engine does
all the work.
To use iperquery as a table (read-only mode, for write only go to see
docs about your engine support for keyed views, another story I'm NOT going
to cover here)
db.define_table('iperquery',
Field('something_that_resemble_a_sequence', 'id')
Field('bla1'),
Field('bla2'),
.,
migrate=False
)
the migrate=False bit is the key: web2py won't try creating it and assumes
there's a table ready to be used.
You just need to:
- include a pkey, better if resembles a sequence, and have a Field of type
'id' matching it
- tune the Field types accordingly to the type of column of your view
On Friday, April 5, 2013 11:24:23 PM UTC+2, Jurgis Pralgauskis wrote:
You'll better code a view and access it as a fake table.
are there examplses of this?
Is it possible to show virtual fields in grid (in appadmin they don't show
up)?
--
---
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: .sum() and .count() in grid?
I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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: .sum() and .count() in grid?
It would be really sweet to do something like mycrumbtrail = SQLFORM.smartgrid.do_crumbtrail(... or myconsole = SQLFORM.smartgrid.do_console(... On Thursday, April 4, 2013 9:17:11 AM UTC-4, Niphlod wrote: I'm guessing it will be hard. You'll better code a view and access it as a fake table. PS: you show it in a grid then what record do you want to add/edit/delete ? I'm guessing that this time you want to use the grid just to search, orderby and paginate, but there's a LOT of logic inside it for more things that needs to be considered. On Thursday, April 4, 2013 12:23:58 PM UTC+2, Johann Spies wrote: How would I be able to use a query like this (or the DAL equivalent) to show the result in a grid? select so as Journal, SUM(aantal) as Articles from isi.nr_arts_per_journal group by Journal order by Journal Regars Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- 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.
