grid not ordering lambda-generated column values correctly

[weheh]

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 ...?

[Cliff Kachinske]

Me too.  It's one of the reasons I don't use grid/smartgrid.

Sorry I don't have an answer.

[Niphlod]

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.

[Cliff Kachinske]

Maybe I missed something here.  

I know you can pass a query to grid.  By queryset do you mean a set of row objects?

[weheh]

Thanks for the responses. @Niphlod: I'm not sure what you mean by a full queryset. Can you elaborate, please?

[Bruno Rocha]

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.

[Niphlod]

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?

[weheh]

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?

[Richard Vézina]

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:

reeeeeally don't know if this is officially supported or not, but works in a test I just made.

--
 
 
 

[Niphlod]

yep. you would have to fetch those fields for searching through them anyway.

[Richard Vézina]

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

--
 
---
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.