Re: [web2py] How to implement multiple filter for grid?
Nope. in either case the burden is on the server, not the client accessing the page. The only way to move the burden to the client accessing the page would be retrieving coding in javascript something that is capable to get distinct values from a set, but you'd still be forced to transmit over the wire the entire 1M rows set. Of course that would be slow/counterproductive. You were saying that select() and select(distinct) generates the same query and the burden of distinct-ing the values was on web2py. That is not true. select() and select(distinct=...) generates different queries, but the processing of the distinct-ion is on the server, but it's on the database application, not on web2py. if it was on web2py, the db application should have been forced to send 1M rows to web2py, and web2py needed to cycle all the 1M set to get the different values. This is not true. The db application, when select(distinct=..) is called generates only the set of distinct values (let's say, 100 distinct values). It's safe to assume that dbs are much faster than python to do that kind of work, because they are engineered/optimized for that kind of work (some dbs are developed from more than 15 years) . That being said, using an index on the table field you are using as distinct can speedup the retrieval even further because the db is not forced to physically read 1M rows, but it can scan the (supposedly less large) index. In the case that on a 1M rows your distinct values are 100, the index would be ~1 times smaller than the table. --
Re: [web2py] How to implement multiple filter for grid?
I've followed Jim's model in creating my search filter, and it appears to be working beautifully! Just have some questions that I would like to ask. First - I slightly modified the selector code so that entries show up as unique, by adding the distinct clause and ordering alphabetically by category, by doing the following: category_list = db().select(db.t_files.f_category, distinct=True, orderby=db.t_files.f_category) options = [OPTION(category_list[i].f_category, _value=str(category_list[i].f_category)) for i inrange (len(category_list))] Will this become extremely inefficient and slow, and grind the system to a halt as the database become large? The second question concerns an important cosmetic change - is there a good way to put the filter input fields (no labels) under the column headers/labels in the grid? Finally, if you wanted to redesign the filters to update after a certain number of characters have been modified in an input field (i.e. without having to click search), how would you go about doing that? Thanks again! -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search example. You'll have to provide your own data to get it running. It worked with my test data. Let me know if you have troubles and I'll be glad to work through them with you. -Jim On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 dhea...@gmail.com javascript: wrote: Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --
Re: [web2py] How to implement multiple filter for grid?
Lamps Wish I could be more help with your questions. Here is my take. 1. If you are thinking about having a large list, I would try to avoid that. Maybe your DISTINCT keyword will keep the list small. My thoughts are that a long list (25 entries or more) is not very user friendly and if I had that situation, I'd just use the search box and filter on the fields base on the text entered. 2. I don't see how you'd be able to do this very easily. Can probably be done, but you'd have to get into /gluon/sqlhtml.py and make some changes there to implement. 3. I think you could probably do this. If it were me doing it I'd look into the suggest_widget plugin available from http://dev.s-cubism.com/plugin_suggest_widget. Best of luck! Sorry I couldn't be more helpful. -Jim On Thu, Nov 1, 2012 at 11:35 AM, Lamps902 dheap@gmail.com wrote: I've followed Jim's model in creating my search filter, and it appears to be working beautifully! Just have some questions that I would like to ask. First - I slightly modified the selector code so that entries show up as unique, by adding the distinct clause and ordering alphabetically by category, by doing the following: category_list = db().select(db.t_files.f_category, distinct=True, orderby=db.t_files.f_category) options = [OPTION(category_list[i].f_category, _value=str(category_list[i].f_category)) for i inrange (len(category_list))] Will this become extremely inefficient and slow, and grind the system to a halt as the database become large? The second question concerns an important cosmetic change - is there a good way to put the filter input fields (no labels) under the column headers/labels in the grid? Finally, if you wanted to redesign the filters to update after a certain number of characters have been modified in an input field (i.e. without having to click search), how would you go about doing that? Thanks again! -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search example. You'll have to provide your own data to get it running. It worked with my test data. Let me know if you have troubles and I'll be glad to work through them with you. -Jim On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 dhea...@gmail.com wrote: Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- -- --
Re: [web2py] How to implement multiple filter for grid?
Regarding the first point, the concern is not that the selector (as defined in the DB model) is instantiated with a huge variety of fields. If I'm not mistaken, if the distinct keyword in the select() function is left out, the program literally goes through every entry in the DB that matches the search criteria. I suspect that it does the same thing when distinct is used, and adds the additional step of filtering them down to the unique entries. I'm guessing this process may be quite resource intensive for a large DB. If there were a way to get the list of possible selector options straight from the model definition, that would seem to be much more efficient way of doing it (with the minor downside that there may not yet be any DB entries that match a given selector option), but I haven't found a way to do this. I guess I may need to start a new thread for the second point - might bring up some good tips on how to customize the grid. Thanks for the suggestion about the widget; I'll check it out. And thanks again for all the help - saved a tremendous amount of time on this task, and I'm sure others will find it useful as well! -Lamps On Thursday, November 1, 2012 12:44:26 PM UTC-4, Jim S wrote: Lamps Wish I could be more help with your questions. Here is my take. 1. If you are thinking about having a large list, I would try to avoid that. Maybe your DISTINCT keyword will keep the list small. My thoughts are that a long list (25 entries or more) is not very user friendly and if I had that situation, I'd just use the search box and filter on the fields base on the text entered. 2. I don't see how you'd be able to do this very easily. Can probably be done, but you'd have to get into /gluon/sqlhtml.py and make some changes there to implement. 3. I think you could probably do this. If it were me doing it I'd look into the suggest_widget plugin available from http://dev.s-cubism.com/plugin_suggest_widget. Best of luck! Sorry I couldn't be more helpful. -Jim On Thu, Nov 1, 2012 at 11:35 AM, Lamps902 dhea...@gmail.com javascript: wrote: I've followed Jim's model in creating my search filter, and it appears to be working beautifully! Just have some questions that I would like to ask. First - I slightly modified the selector code so that entries show up as unique, by adding the distinct clause and ordering alphabetically by category, by doing the following: category_list = db().select(db.t_files.f_category, distinct=True, orderby=db.t_files.f_category) options = [OPTION(category_list[i].f_category, _value=str(category_list[i].f_category)) for i inrange (len(category_list))] Will this become extremely inefficient and slow, and grind the system to a halt as the database become large? The second question concerns an important cosmetic change - is there a good way to put the filter input fields (no labels) under the column headers/labels in the grid? Finally, if you wanted to redesign the filters to update after a certain number of characters have been modified in an input field (i.e. without having to click search), how would you go about doing that? Thanks again! -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search example. You'll have to provide your own data to get it running. It worked with my test data. Let me know if you have troubles and I'll be glad to work through them with you. -Jim On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 dhea...@gmail.com wrote: Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- -- --
Re: [web2py] How to implement multiple filter for grid?
only on the db part. select(distinct=...) sends a different query to the db than select(). It's not web2py that uniquify the sets of the record returned to make it distinct. Then. It's true that doing a distinct on a million rows table forces the db to scan through a million of rows but doing distinct a) the db doesn't need to return one million of rows to your program (wire transfer from db to app is less bulky) b) your program doesn't need to load it into memory and transform it in a SELECT widget Last but not least.web2py hasn't an API to create indexes on the db, but indexes on a db are a powerful/somewhat required thing to do/think about when designing large applications. If you put an index in the table column you're requesting that distinct, the db scans only the index and not the million rows table On Thursday, November 1, 2012 8:18:50 PM UTC+1, Lamps902 wrote: Regarding the first point, the concern is not that the selector (as defined in the DB model) is instantiated with a huge variety of fields. If I'm not mistaken, if the distinct keyword in the select() function is left out, the program literally goes through every entry in the DB that matches the search criteria. I suspect that it does the same thing when distinct is used, and adds the additional step of filtering them down to the unique entries. I'm guessing this process may be quite resource intensive for a large DB. If there were a way to get the list of possible selector options straight from the model definition, that would seem to be much more efficient way of doing it (with the minor downside that there may not yet be any DB entries that match a given selector option), but I haven't found a way to do this. I guess I may need to start a new thread for the second point - might bring up some good tips on how to customize the grid. Thanks for the suggestion about the widget; I'll check it out. And thanks again for all the help - saved a tremendous amount of time on this task, and I'm sure others will find it useful as well! -Lamps On Thursday, November 1, 2012 12:44:26 PM UTC-4, Jim S wrote: Lamps Wish I could be more help with your questions. Here is my take. 1. If you are thinking about having a large list, I would try to avoid that. Maybe your DISTINCT keyword will keep the list small. My thoughts are that a long list (25 entries or more) is not very user friendly and if I had that situation, I'd just use the search box and filter on the fields base on the text entered. 2. I don't see how you'd be able to do this very easily. Can probably be done, but you'd have to get into /gluon/sqlhtml.py and make some changes there to implement. 3. I think you could probably do this. If it were me doing it I'd look into the suggest_widget plugin available from http://dev.s-cubism.com/plugin_suggest_widget. Best of luck! Sorry I couldn't be more helpful. -Jim On Thu, Nov 1, 2012 at 11:35 AM, Lamps902 dhea...@gmail.com wrote: I've followed Jim's model in creating my search filter, and it appears to be working beautifully! Just have some questions that I would like to ask. First - I slightly modified the selector code so that entries show up as unique, by adding the distinct clause and ordering alphabetically by category, by doing the following: category_list = db().select(db.t_files.f_category, distinct=True, orderby=db.t_files.f_category) options = [OPTION(category_list[i].f_category, _value=str(category_list[i].f_category)) for i inrange (len(category_list))] Will this become extremely inefficient and slow, and grind the system to a halt as the database become large? The second question concerns an important cosmetic change - is there a good way to put the filter input fields (no labels) under the column headers/labels in the grid? Finally, if you wanted to redesign the filters to update after a certain number of characters have been modified in an input field (i.e. without having to click search), how would you go about doing that? Thanks again! -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search example. You'll have to provide your own data to get it running. It worked with my test data. Let me know if you have troubles and I'll be glad to work through them with you. -Jim On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 dhea...@gmail.com wrote: Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you
Re: [web2py] How to implement multiple filter for grid?
Hi, Niphlod. If I understood correctly, you're saying that .select(distinct=True) on a large db does indeed require a lot of resources, but all of the heavy work is done by the server, rather than the client's computer, and that web2py doesn't have an inbuilt way around it. That sort of heavy lifting is indeed, something to be avoided if possible. I guess you can avoid the problem by keeping duplicates of the options lists/sets somewhere that's easily accessible - this takes up more space/requires more administrative effort, but the decrease in utilization of server resources should be worth it. Thank you for the clarification. -Lamps On Thursday, November 1, 2012 3:57:42 PM UTC-4, Niphlod wrote: only on the db part. select(distinct=...) sends a different query to the db than select(). It's not web2py that uniquify the sets of the record returned to make it distinct. Then. It's true that doing a distinct on a million rows table forces the db to scan through a million of rows but doing distinct a) the db doesn't need to return one million of rows to your program (wire transfer from db to app is less bulky) b) your program doesn't need to load it into memory and transform it in a SELECT widget Last but not least.web2py hasn't an API to create indexes on the db, but indexes on a db are a powerful/somewhat required thing to do/think about when designing large applications. If you put an index in the table column you're requesting that distinct, the db scans only the index and not the million rows table On Thursday, November 1, 2012 8:18:50 PM UTC+1, Lamps902 wrote: Regarding the first point, the concern is not that the selector (as defined in the DB model) is instantiated with a huge variety of fields. If I'm not mistaken, if the distinct keyword in the select() function is left out, the program literally goes through every entry in the DB that matches the search criteria. I suspect that it does the same thing when distinct is used, and adds the additional step of filtering them down to the unique entries. I'm guessing this process may be quite resource intensive for a large DB. If there were a way to get the list of possible selector options straight from the model definition, that would seem to be much more efficient way of doing it (with the minor downside that there may not yet be any DB entries that match a given selector option), but I haven't found a way to do this. I guess I may need to start a new thread for the second point - might bring up some good tips on how to customize the grid. Thanks for the suggestion about the widget; I'll check it out. And thanks again for all the help - saved a tremendous amount of time on this task, and I'm sure others will find it useful as well! -Lamps On Thursday, November 1, 2012 12:44:26 PM UTC-4, Jim S wrote: Lamps Wish I could be more help with your questions. Here is my take. 1. If you are thinking about having a large list, I would try to avoid that. Maybe your DISTINCT keyword will keep the list small. My thoughts are that a long list (25 entries or more) is not very user friendly and if I had that situation, I'd just use the search box and filter on the fields base on the text entered. 2. I don't see how you'd be able to do this very easily. Can probably be done, but you'd have to get into /gluon/sqlhtml.py and make some changes there to implement. 3. I think you could probably do this. If it were me doing it I'd look into the suggest_widget plugin available from http://dev.s-cubism.com/plugin_suggest_widget. Best of luck! Sorry I couldn't be more helpful. -Jim On Thu, Nov 1, 2012 at 11:35 AM, Lamps902 dhea...@gmail.com wrote: I've followed Jim's model in creating my search filter, and it appears to be working beautifully! Just have some questions that I would like to ask. First - I slightly modified the selector code so that entries show up as unique, by adding the distinct clause and ordering alphabetically by category, by doing the following: category_list = db().select(db.t_files.f_category, distinct=True, orderby=db.t_files.f_category) options = [OPTION(category_list[i].f_category, _value=str(category_list[i].f_category)) for i inrange (len(category_list))] Will this become extremely inefficient and slow, and grind the system to a halt as the database become large? The second question concerns an important cosmetic change - is there a good way to put the filter input fields (no labels) under the column headers/labels in the grid? Finally, if you wanted to redesign the filters to update after a certain number of characters have been modified in an input field (i.e. without having to click search), how would you go about doing that? Thanks again! -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search
Re: [web2py] How to implement multiple filter for grid?
You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] How to implement multiple filter for grid?
Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] How to implement multiple filter for grid?
Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- attachment: filter.jpg
Re: [web2py] How to implement multiple filter for grid?
Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] How to implement multiple filter for grid?
Hi, Jim. Thank you so much! At a glance, I think I understand the general idea behind it. I'll get into implementing a version for my page pretty soon, and I'll post an update on how it went, or any insights that may pop up in the process of doing this. If any issues come up, I'll let you know. Thanks again. -Lamps On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote: I'm attaching a quick-n-dirty search example. You'll have to provide your own data to get it running. It worked with my test data. Let me know if you have troubles and I'll be glad to work through them with you. -Jim On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 dhea...@gmail.com javascript: wrote: Hi, Jim. It would be great if you could provide more details and some code illustrating how you went about doing it. Thanks! -Lamps On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote: Using .smartgrid, I override the default filtering capabilities and provide my own. See attached screenshot. Let me know if you want more details. I think I uploaded a small example app to the list some time ago and should be able to find it or recreate it if you like. -Jim On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote: Hi, Johann - I know you can build a complex query using the search box, but that is far from intuitive for most users. I'd like to implement the same look/feel/usability as was available in webgrid, and as is shown in the image. Is there a reasonable way to do this? Thanks. -Lamps On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote: You can do that by building a complex query using the search box. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --
