Db fields in the "links" of the grid

51 views
Skip to first unread message

Vlad

unread,
Oct 7, 2020, 6:44:41 PM10/7/20
to web2py-users
Seems to me this is an inconsistency in the way how grid operates (which breaks it, as I show below, but, of course, most probably I am just missing something.)

The following code crashes: 

    query = db.cart
    fields = [db.cart.id]
    links = [dict(header='View', body=lambda row: str(row.cart.id))]
    grid = SQLFORM.grid(query, editable=True, details=True, links=links, fields=fields)
 
This is because row.cart is undefined in the links. Instead, the links should be made as such: 

    links = [dict(header='View', body=lambda row: str(row.id))]

Now this works. 

However, when I add more fields in the code, like this: 

    fields = [db.cart.id, db.cart.description, db.cart_ownership.boss, db.cart_ownership.status, db.cart.count]

Now in the links I can't use "row.id". It must be "row.cart.id"

This by itself would be fine, I could just use row.id or row.card.id accordingly, depending on the fields used (though I would like to control this structure), but I am having the following problem further on: 

The grid described by the code

    query = db.cart
    fields = [db.cart.id, db.cart.description, db.cart_ownership.boss, db.cart_ownership.status, db.cart.count]
    links = [dict(header='View', body=lambda row: str(row.cart.id))]
    grid = SQLFORM.grid(query, editable=True, details=True, links=links, fields=fields)

crashes when I try to view or edit a row of the grid. This is because the links takes  row.cart.id in the grid itself, but expects row.id in edit- or view- actions (i.e. when editing or viewing a row). When viewing or editing a row, row.cart is undefined in the links, so row.cart.id crashes it (when "view" or "edit" buttons are clicked), while in the grid itself row.cart.id works just fine (and row.id would not work). 

What am I missing here? How do I control how this field should be expected in the links in the grid vs. in the view/edit a row of the grid? 

Here is still simplified but more complete code, in case I missed something important in a "shortcut" code above: 

    query = db.cart
    fields = [db.cart.id, db.cart.description, db.cart_ownership.boss, db.cart_ownership.status, db.cart.count]
    links = [dict(header='View', body=lambda row: str(row.cart.id))]
    grid = SQLFORM.grid(query,
                        editable=True,
                        details=True,
                        links=links,
                        fields=fields,
                        left = [db.cart_ownership.on(db.cart.id==db.cart_ownership.cart)],
                        field_id=db.cart.id,
                        )

Jim S

unread,
Oct 7, 2020, 8:48:25 PM10/7/20
to web2py-users
I have a couple of ideas, but none are tested

First, can you try adding the field_id parameter to your SQLFORM.grid() call?  I believe that tells this grid which is your 'primary' table.

Secondly (this is the way I typically handle it) - instead of coding everything in a lambda, call a function to build your buttons and just pass it the id of the row.  Then, in your function you can retrieve the entire row and get all the data you need even if it isn't included in the grid fields.

Not sure that completely addresses your concern, but if you run through those ideas it might help you onto a solution.

-Jim

Eliezer (Vlad) Tseytkin

unread,
Oct 8, 2020, 2:13:09 AM10/8/20
to web...@googlegroups.com
Jim, 

Thank you for the suggestions. 

I do specify field_id (it wasn't there in the simplified code, but the complete code does have it). 

When I use a function, instead of the lambda, I indeed can have a solution, but at the same time it emphasizes that something is wrong: 

The links are now presented as such: 

    links = [dict(header='View', body=GetCartsGridLink)]

And the GetCarrsGridLink function as follows: 

def GetCartsGridLink(row):
   
    id = None
   
    try:
      id = row.cart.id # this works for the grid itself
    except:
      id = row.id # this works for the view/edit of a record of the grid
     
    result = A(id,
               _href=URL('manage', 'view_cart', args=id, user_signature=True),
               _target='blank')
   
    return result

It does solve the problem, because try/except takes care of it, setting up the id based on the context. 

I feel there is something wrong with the very necessity of having to use try/except here. Why would it use different structures in the grid itself vs. view/edit a row of the grid??? 

The problem has been solved, but the mystery remains. I am still missing something about it...  

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/UGhYBMwmXec/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/257aa85d-41f2-4b2e-a59a-bd1980c83efco%40googlegroups.com.

Jim Steil

unread,
Oct 8, 2020, 2:22:56 AM10/8/20
to web...@googlegroups.com
I think that is a result of having a left join specified.  With the left join you now have to specify which table the field is in as well.

Or, am I missing something?

-Jim

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CABZ%2BKCCVY37AMAFQ6FxgpJvX3X%3Dbr9gFBU68c74HpUJw3pEa%2BQ%40mail.gmail.com.

Eliezer (Vlad) Tseytkin

unread,
Oct 8, 2020, 2:30:28 AM10/8/20
to web...@googlegroups.com
But shouldn't it be the same for the grid and for the record of the grid? The difference confuses me - having to specify the table should be consistent when I use the grid for all the transactions of the grid, including view/edit etc.. 

In other words, having to specify the table is perfectly fine - but it should be consistent and I should as well specify the table for the view/edit action of the grid record. Different structures for the gird and fo the record of the grid seems inconsistent to me.It took me time to figure this out - now that I know how this works, I simply have the try/except to solve it - this makes practical sense, as it solves the problem, but it makes no logical sense. Do you know what I mean? Anybody who encounters such a situation will presumably be messed up and have to spend time figuring out what's going on. 

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CAERBpoBS7skdN8q%3DzYwfA01WK-TKEHXkvq0iX%3Df4ghMRVbPzyw%40mail.gmail.com.

Jim S

unread,
Oct 8, 2020, 2:38:39 AM10/8/20
to web2py-users
I'm having trouble understanding why you need the try/except.  To me it seems like "if you specify a left" -> you need to use the table name.  "if you don't specify a left" -> omit the table name

Do you have an occasion where sometimes different rows in the same grid require you to specify the field differently?

-Jim
To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/UGhYBMwmXec/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/UGhYBMwmXec/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

Eliezer (Vlad) Tseytkin

unread,
Oct 8, 2020, 2:50:26 AM10/8/20
to web...@googlegroups.com
Jom, you said " I'm having trouble understanding why you need the try/except" - this is exactly the trouble I'm having too :) 

I do use the left join. So I should simply specify the table name - correct?

Well, if I do - then the grid itself works fine, but once I click "view" or "edit"  - it crashes. To fix that crash, I must specify try/except clause in the function used by the "links". WIthout that try/except the actions of view and edit don' work! 

That's exactly what I am saying - with the left join, the grid expects the table name, but the view/edit actions of this grid expect no table name and in fact crash when the table name is there.  

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/ffccc9ca-dc13-4efe-a6f3-d4140576053co%40googlegroups.com.

Jim Steil

unread,
Oct 8, 2020, 3:12:42 AM10/8/20
to web...@googlegroups.com
I will take a look tomorrow to see if I can code something up to reproduce the error.

Which tackle are you setting as the 'primary' table, by that I mean what field are you specifying in the field_id parameter?  And, is there ALWAYS a matching record in the secondary table? 

Oh, just thought of another thing. How about specifying db.cart.id in fields=[], and the also specifying it in hidden_fields=[]?

Jim



Eliezer (Vlad) Tseytkin

unread,
Oct 8, 2020, 12:38:48 PM10/8/20
to web...@googlegroups.com
Yes, there is always a corresponding record in the second table (doesn't have to be, but I do clean up ownerless carts, so when I am testing things, the corresponding record is always there). 

Here is the complete creation of the grid (where GetCartsGridLink is the function that now has try/except clause to flip between row.id and row.cart.id):

def show_carts():
    query = db.cart
    headers = {'cart.id':'ID', 'cart.description':'Name', 'cart_ownership.boss':'Owner', 'cart_ownership.status':'Status', 'cart.count':'Items'}

    fields = [db.cart.id, db.cart.description, db.cart_ownership.boss, db.cart_ownership.status, db.cart.count]
    links = [dict(header='View', body=GetCartsGridLink)]

   
    grid = SQLFORM.grid(query,
                        editable=True,
                        details=True,
                        create=True,
                        links=links,
                        fields=fields,
                        headers=headers,
                        orderby=[~db.cart.created_on],
                        left = [db.cart_ownership.on(db.cart.id==db.cart_ownership.cart)],
                        formname='carts_grid',
                        field_id=db.cart.id,
                        )
       
    return grid
 



To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CAERBpoCUMsMOm47LJOFgs5V%3DoWOx6LCngXCUNA0D9J10aWDQYQ%40mail.gmail.com.

Jim S

unread,
Oct 8, 2020, 1:18:53 PM10/8/20
to web2py-users
any chance you have a model and a small sample-set of data you could share that cause the inconsistencies to be shown?  Just 3 records or so in each table that can show the inconsistency?

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/UGhYBMwmXec/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/UGhYBMwmXec/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

Vlad

unread,
Oct 8, 2020, 1:37:34 PM10/8/20
to web2py-users
Sure, the sample data attached (are these 2 tables enough?), and the models are as follows: 

db.define_table('cart',
   Field('description','string',default='My Cart (%s)' % str((datetime.now().strftime("%m/%d/%Y %I:%M%p")))),
   auth.signature)

db.define_table('cart_ownership',
    Field('description', 'string'),
    Field('boss','reference auth_user'),
    Field('cart', 'reference cart'),
    Field('status', 'string', requires=IS_IN_SET(['current','onhold'])),
    auth.signature)


db_cart.csv
db_cart_ownership.csv

Vlad

unread,
Oct 8, 2020, 1:45:41 PM10/8/20
to web2py-users
Just realized that there is another field, virtual one, referenced in the code. In case the problem doesn't reproduce without it, here is the definition (but I don't think that this would be necessary): 

db.cart.count = Field.Virtual(lambda row: db(db.cart_content.cart==row.cart.id).select(db.cart_content.quantity.count()).first()[db.cart_content.quantity.count()])

And here is the model (and data attached):

db.define_table('cart_content',
   Field('cart','reference cart'),
   Field('product','reference product'),
   Field('formula','reference formula'),
   Field('description','string',default=None),
   Field('flavor','reference flavor'),
   Field('quantity','integer',requires=[IS_NOT_EMPTY()],default=1),
   Field('price','integer',represent=lambda x, row: '$' + str(x/100)),
   Field('imported','boolean'),
   auth.signature)



db_cart_content.csv

Jim S

unread,
Oct 8, 2020, 2:58:06 PM10/8/20
to web2py-users
Vlad

The behavior is exactly as you said it is.

I don't know if I'd go as far as saying this is a problem with web2py.  I got around it by using this instead of the try/except in GetCartsGridLink

id = row.cart.id if 'cart' in row else row.id

I know it isn't ideal and isn't what you were looking for, but as you found, it isn't that hard to get around.  I'm just surprised that I haven't run into this over the years I've been working with web2py because I do similar linking in my apps all the time.

Sorry I couldn't be of more help.  

Anyone else have any thoughts on this?

-Jim

Eliezer (Vlad) Tseytkin

unread,
Oct 8, 2020, 3:38:12 PM10/8/20
to web...@googlegroups.com
Jim,

Thank you very much for your help,

This is a perfect solution for me. It just took me time to figure out what was going on. 

I don't think that this can be classified as a bug in web2py. It's just very confusing for anybody who encounters this situation. However, if I am the only one who has encountered it for the past 10 years, we can live with it :) 

Thank you! 


To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/6b303bc0-a860-4d9d-8b00-c1277f6dbeb1o%40googlegroups.com.

Jim S

unread,
Oct 8, 2020, 3:40:27 PM10/8/20
to web2py-users
I looked back through my code and didn't find an instance where I had custom links on a grid with a left join.  And, I've been coding in web2py since 2011.

-Jim
Reply all
Reply to author
Forward
0 new messages