Re: [web2py] Re: grid not ordering lambda-generated column values correctly
So, if I do understand correctly the Niphold explanation if I want to search for instance the first_name of the auth_user table that is referenced from my table I can't just pass the to SQLFORM.grid my_table, I better pass it a join query where all the represent are replaced with the really field of the referenced table, right? Richard On Mon, Aug 6, 2012 at 4:25 PM, Niphlod niph...@gmail.com wrote: really don't know if this is officially supported or not, but works in a test I just made. On Monday, August 6, 2012 10:06:44 PM UTC+2, Cliff Kachinske wrote: Maybe I missed something here. I know you can pass a query to grid. By queryset do you mean a set of row objects? On Monday, August 6, 2012 3:56:12 PM UTC-4, Niphlod wrote: this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/**computed field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? -- -- --- 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: grid not ordering lambda-generated column values correctly
yep. you would have to fetch those fields for searching through them anyway. On Wednesday, May 1, 2013 4:54:28 PM UTC+2, Richard wrote: So, if I do understand correctly the Niphold explanation if I want to search for instance the first_name of the auth_user table that is referenced from my table I can't just pass the to SQLFORM.grid my_table, I better pass it a join query where all the represent are replaced with the really field of the referenced table, right? Richard On Mon, Aug 6, 2012 at 4:25 PM, Niphlod nip...@gmail.com javascript:wrote: really don't know if this is officially supported or not, but works in a test I just made. On Monday, August 6, 2012 10:06:44 PM UTC+2, Cliff Kachinske wrote: Maybe I missed something here. I know you can pass a query to grid. By queryset do you mean a set of row objects? On Monday, August 6, 2012 3:56:12 PM UTC-4, Niphlod wrote: this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/**computed field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? -- -- --- 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: grid not ordering lambda-generated column values correctly
This is faster I guest then making a lambda db query each record each request... I post an other thread about SQLFORM.grid() and search if you want to comment... Thanks Richard On Wed, May 1, 2013 at 3:35 PM, Niphlod niph...@gmail.com wrote: yep. you would have to fetch those fields for searching through them anyway. On Wednesday, May 1, 2013 4:54:28 PM UTC+2, Richard wrote: So, if I do understand correctly the Niphold explanation if I want to search for instance the first_name of the auth_user table that is referenced from my table I can't just pass the to SQLFORM.grid my_table, I better pass it a join query where all the represent are replaced with the really field of the referenced table, right? Richard On Mon, Aug 6, 2012 at 4:25 PM, Niphlod nip...@gmail.com wrote: really don't know if this is officially supported or not, but works in a test I just made. On Monday, August 6, 2012 10:06:44 PM UTC+2, Cliff Kachinske wrote: Maybe I missed something here. I know you can pass a query to grid. By queryset do you mean a set of row objects? On Monday, August 6, 2012 3:56:12 PM UTC-4, Niphlod wrote: this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/**compute**d field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? -- -- --- 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. -- --- 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: grid not ordering lambda-generated column values correctly
Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? --
[web2py] Re: grid not ordering lambda-generated column values correctly
this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/computed field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? --
[web2py] Re: grid not ordering lambda-generated column values correctly
Maybe I missed something here. I know you can pass a query to grid. By queryset do you mean a set of row objects? On Monday, August 6, 2012 3:56:12 PM UTC-4, Niphlod wrote: this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/computed field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? --
[web2py] Re: grid not ordering lambda-generated column values correctly
Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please? --
Re: [web2py] Re: grid not ordering lambda-generated column values correctly
Basically you have to keep in mind that 'orderby and limitby will be done by the database engine, the db does not know anything about your lambda' logic. --
[web2py] Re: grid not ordering lambda-generated column values correctly
again, it seems to work but I didn't check the code to see if it is supported. when you pass a table to the grid constructor, search, orderby etc are done against the real data of the table. So, if you're going to display, e.g., a table with a user_id fields referenced to the auth_user table, you get a user_id column with the first_name - last_name - id value. But if you search tom you get nothing, the search is done on the actual table, not in the referenced one. This is smart because you'd need to fetch all the relations every time and search/orderby through those. In addition, computed refs like yours are really heavy to search through. it's a single count() for every row out there. In a word, if you pass the table, the search is done on the db engine, not in the complex logic that lambdas do. Anyway, it seems that for simple joins, if you have something like myq = db.auth_event.user_id == db.auth_user.id grid = SQLFORM.grid(myq) works fine for ordering/searching. On Monday, August 6, 2012 11:23:50 PM UTC+2, weheh wrote: Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please? On Monday, August 6, 2012 11:23:50 PM UTC+2, weheh wrote: Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please? --
[web2py] Re: grid not ordering lambda-generated column values correctly
OK, so I guess the answer is to ditch the virtual field and go with a regular field, then compute the field before putting up the grid. Lambda is nice in my failed implementation because it iterates automatically. I wonder if I can do the assignment without having to loop while updating? In other words can I avoid: for host in db(db.host).select(): #create count #update host table Anybody know how to do this in 1 line? On Tuesday, August 7, 2012 5:45:17 AM UTC+8, Niphlod wrote: again, it seems to work but I didn't check the code to see if it is supported. when you pass a table to the grid constructor, search, orderby etc are done against the real data of the table. So, if you're going to display, e.g., a table with a user_id fields referenced to the auth_user table, you get a user_id column with the first_name - last_name - id value. But if you search tom you get nothing, the search is done on the actual table, not in the referenced one. This is smart because you'd need to fetch all the relations every time and search/orderby through those. In addition, computed refs like yours are really heavy to search through. it's a single count() for every row out there. In a word, if you pass the table, the search is done on the db engine, not in the complex logic that lambdas do. Anyway, it seems that for simple joins, if you have something like myq = db.auth_event.user_id == db.auth_user.id grid = SQLFORM.grid(myq) works fine for ordering/searching. On Monday, August 6, 2012 11:23:50 PM UTC+2, weheh wrote: Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please? On Monday, August 6, 2012 11:23:50 PM UTC+2, weheh wrote: Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please? --
[web2py] Re: grid not ordering lambda-generated column values correctly
really don't know if this is officially supported or not, but works in a test I just made. On Monday, August 6, 2012 10:06:44 PM UTC+2, Cliff Kachinske wrote: Maybe I missed something here. I know you can pass a query to grid. By queryset do you mean a set of row objects? On Monday, August 6, 2012 3:56:12 PM UTC-4, Niphlod wrote: this is more related to an implementation logic than a bug. fields that are represented by some other field gets computed at run-time, but to search/orderby them you have to fetch them too, and that can be expensive. If you need to orderby/search by a referenced/represented/computed field, you can pass the full queryset to the grid. On Monday, August 6, 2012 9:07:59 PM UTC+2, Cliff Kachinske wrote: Me too. It's one of the reasons I don't use grid/smartgrid. Sorry I don't have an answer. On Monday, August 6, 2012 10:18:07 AM UTC-4, weheh wrote: I have an SQLFORM.grid(... orderby=db.host.id ...) where db.host.id.represent=lambda value, row: int( db((db.url_queue.host_id == value) (db.url_queue.removed == None)).count() ) The host.id column displays the correct numbers, but when I click on the column title to order ascending or descending, the order is seemingly random. I suspect it's ordering not by the lambda value, but rather by the underlying host.id value. Seems like a bug ...? --
