smartgrid with linked_tables and constraints broke the links

[Pot]

Hi there,

I am very new to web2py, i posted 1 question here a week ago, but never got reply (if anyone sees my post, please let me know if I missed anything), I am trying luck with another question now.

I have a customer table, an invoice table and an item table. I simplified them below:

db.define_table('customer', Field('company_name', requires=IS_NOT_EMPTY()), auth.signature, format='%(company_name)s')

db.define_table('invoice', Field('customer', 'reference customer'), Field('project_name', requires=IS_NOT_EMPTY()))

db.define_table('item', Field('invoice', 'reference invoice',  writable=False), Field('description'), Field('unit_price', 'double'))

When I used smartgrid without specifying the constraints below, the linked_tables 'Items' worked correctly (i.e. i clicked Items link, it showed me all the items belongs to that particular invoice), but obviously i could not select a particular set of invoices based on a customer id. when I added the constraints, i could select the particular invoices based on the given customer id, but the linked_tables 'Items' broke with this error message - Query Not Supported: invalid literal for long() with base 10: 'item'. The URL got redirected from http://localhost:8000/connect28/accounting/view_invoices/invoice/item.invoice/10?_signature=0653b123ba6f4b43b82c48d49516af9c772b1496 to http://localhost:8000/connect28/accounting/view_invoices/item. Any help and pointers is much appreciated.

def view_invoices():

    customer_id = request.args(0)

    if customer_id:

        invoice_query=db.invoice.customer==customer_id

    else:

        invoice_query=db.invoice.customer!=None 

    constraints=dict(invoice=invoice_query)

    db.item.invoice.writable=False #avoid invoice # from any particular row in item table get overwritten

    invoice_grid = SQLFORM.smartgrid(db.invoice, constraints=constraints)

    return locals()

[Anthony]

def view_invoices():

    customer_id = request.args(0)

    if customer_id:

        invoice_query=db.invoice.customer==customer_id

    else:

        invoice_query=db.invoice.customer!=None 

    constraints=dict(invoice=invoice_query)

    db.item.invoice.writable=False #avoid invoice # from any particular row in item table get overwritten

    invoice_grid = SQLFORM.smartgrid(db.invoice, constraints=constraints)

    return locals()

The grid and smartgrid use request.args internally to determine what is being requested, so if you also need to use request.args for additional data, you must tell the grid via the "args" argument. It appears you are using the first URL arg to specify a customer ID, so, do the following:

SQLFORM.smartgrid(..., args=request.args[:1])

Now, when the grid creates its own internal links, it will (a) preserve your arg as the first arg of each URL, and (b) read its own args starting with the second URL arg.

Note, the "args" argument must be a list, even if it includes only a single item.

Anthony

[Rudy]

Thanks Anthony for your explanation of request.args use with smartgrid, I tried your suggestion, but had the same error. I think i made a mistake in the constraints in my earlier post (according to the documentation, constraints is a dict of tablename:query), here is what i have changed.

invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=dict(invoice=invoice_query), linked_tables=dict(invoice=['item'], item=[]))

Noted that i could select a set of invoices based on the customer_id I passed in through request.args(0), the only thing it broke was when I clicked on the linked_tables "Items" with error  Query Not Supported: invalid literal for long() with base 10: 'item'. 

I have been stuck here for a few days, any further help is much appreciated. 

[Dave S]

On Thursday, March 30, 2017 at 2:57:09 PM UTC-7, Rudy wrote:

Thanks Anthony for your explanation of request.args use with smartgrid, I tried your suggestion, but had the same error. I think i made a mistake in the constraints in my earlier post (according to the documentation, constraints is a dict of tablename:query), here is what i have changed.

invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=dict(invoice=invoice_query), linked_tables=dict(invoice=['item'], item=[]))

Noted that i could select a set of invoices based on the customer_id I passed in through request.args(0), the only thing it broke was when I clicked on the linked_tables "Items" with error  Query Not Supported: invalid literal for long() with base 10: 'item'. 

I have been stuck here for a few days, any further help is much appreciated. 

 From the error message, I suspect the part linked_tables=dict(invoice=['item'],...)  needs to specify an ID, and you've provided a string (and a string not corresponding to a valid base10 number).

/dps

[Anthony]

invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=dict(invoice=invoice_query), linked_tables=dict(invoice=['item'], item=[]))

Noted that i could select a set of invoices based on the customer_id I passed in through request.args(0), the only thing it broke was when I clicked on the linked_tables "Items" with error  Query Not Supported: invalid literal for long() with base 10: 'item'. 

I have been stuck here for a few days, any further help is much appreciated. 

 From the error message, I suspect the part linked_tables=dict(invoice=['item'],...)  needs to specify an ID, and you've provided a string (and a string not corresponding to a valid base10 number).

No, that's not the problem. The linked_tables argument should indeed be a dictionary with lists of table names.

Anthony

[Anthony]

It's not quite clear without seeing more code. I suggest you pack and attach a minimal app that reproduces the problem.

Anthony

[Rudy]

Hi Anthony,

here is the minimal app, and I did reproduce the problem and narrowed where it broke. 

In view_invoices() action, it checks if customer id args is provided, if so it will take it in for invoice_query = db.invoice.customer==customer_id, if not invoice_query = db.invoice.customer != None (pls let me know if there is a better way to define the query for selecting all invoice). Below code works when I specify the customer id in the URL eg. http://localhost:8000/tests/default/view_invoices/2, now when i click on the linked_tables 'Items', it will redirect to http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. 

Now, if I don't specify the customer id in URL eg. http://localhost:8000/tests/default/view_invoices/, i still got the list of invoices, but when I clicked on the linked_tables 'Items' for any particular invoice, it will redirect to http://localhost:8000/tests/default/view_invoices/invoice/item.invoice/1, error raised - ValueError: invalid literal for long() with base 10: 'invoice'. Any further pointer is much appreciated.

Another question related to it is - how does web2py construct the URL for redirect when linked_tables is used? eg. http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. The blue part is what I typed in, the red is provided by the linked_tables.

In db.py:

db.define_table('customer', 

                Field('company_name'),

                format='%(company_name)s',

               ) 

auth.settings.extra_fields['auth_user']= [

  Field('customer', 'reference customer', label=T('Customer'), notnull=True, required=True, requires=IS_IN_DB(db, db.customer.id, '%(company_name)s')),

  Field('mobile', requires=IS_NOT_EMPTY()) ]

In db_accounting.py:

db.define_table('invoice', 

                Field('customer', 'reference customer'),

                Field('project_name'),

                Field('amount', 'double'),

                auth.signature) # in db_accounting.py of consumer accounting app

db.define_table('item',

                Field('invoice', 'reference invoice',  writable=False),

                Field('description'),

                Field('unit_price', 'double'),

                Field('quantity', 'integer'),

                auth.signature)

In default.py:

def view_invoices():

    customer_id = request.args(0)

    if customer_id:

        invoice_query=db.invoice.customer==customer_id

    else:

        invoice_query=db.invoice.customer!=None

    constraints=dict(invoice=invoice_query)

    invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=constraints, linked_tables=dict(invoice=['item'], item=[]))

    return locals()

p.s. Dave, i think Anthony is right, i don't think we need to specify an ID for the linked table according to the documentation, but I appreciate you are chipping in your idea.

[Anthony]

here is the minimal app, and I did reproduce the problem and narrowed where it broke. 

In view_invoices() action, it checks if customer id args is provided, if so it will take it in for invoice_query = db.invoice.customer==customer_id, if not invoice_query = db.invoice.customer != None (pls let me know if there is a better way to define the query for selecting all invoice). Below code works when I specify the customer id in the URL eg. http://localhost:8000/tests/default/view_invoices/2, now when i click on the linked_tables 'Items', it will redirect to http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. 

Now, if I don't specify the customer id in URL

That's the problem. In some cases you don't specify the customer ID as request.args[0], and yet in all cases you tell smartgrid that you are in fact using request.args[0] (by specifying args=request.args[:1]). You must either always specify something as the first URL arg in your links, or conditionally set the "args" argument of the smartgrid.
 

Another question related to it is - how does web2py construct the URL for redirect when linked_tables is used? eg. http://localhost:8000/tests/default/view_invoices/2/invoice/item.invoice/1. The blue part is what I typed in, the red is provided by the linked_tables.

The grid generates a number of different links and uses the URL args (after any you explicitly specify) to determine what output to generate. For example, the above indicates you started viewing records in the invoice table and then clicked a link pointing to records in the item table with a db.item.invoice value of 1 (i.e., items linked to invoice 1).

Anthony

[Rudy]

Hi Anthony,

Pardon me for asking stupid question, what does SQLFORM.smartgrid(.....， args=request.args[:1], ...) really do? you mentioned in earlier post that args must be a list, and it preserves the 1st argument of URL for application specific use. Does it tell web2py whatever url it creates, it should always preserve the same URL up to the 1st argument (for this case) eg. http://localhost/tests/default/view_invoices/2? I looked up the documentation, there is little info about args in grid / smartgrid signature. If above assumption is correct, if my applications uses first 2 args for specific application purpose, i should modify the code to SQLFORM.smartgrid(.....， args=request.args[:2], ...)? Another stupid question - what's difference between request.args[:1] and request.args(0)?j

Since i want to allow enduser to type in http://localhost/tests/default/view_invoices without args for selecting all invoices, what do you suggest I should do? I tried below (not sure if it's what you meant ... conditionally set the "args" argument of the smartgrid) , but same issue happened. Many thanks for all your pointers.

def view_invoices():

    customer_id = request.args(0)

    if customer_id:

        invoice_query=db.invoice.customer==customer_id

        constraints=dict(invoice=invoice_query)

        invoice_grid = SQLFORM.smartgrid(db.invoice, args=request.args[:1], constraints=constraints, linked_tables=dict(invoice=['item'], item=[]))

    else:

        invoice_query=db.invoice.customer!=None

        constraints=dict(invoice=invoice_query)

        invoice_grid = SQLFORM.smartgrid(db.invoice, constraints=constraints, linked_tables=dict(invoice=['item'], item=[]))

    return locals()

[Anthony]

Pardon me for asking stupid question, what does SQLFORM.smartgrid(.....， args=request.args[:1], ...) really do? you mentioned in earlier post that args must be a list, and it preserves the 1st argument of URL for application specific use. Does it tell web2py whatever url it creates, it should always preserve the same URL up to the 1st argument (for this case) eg. http://localhost/tests/default/view_invoices/2?

The grid builds its own internal URLs by taking the base URL of the action that serves the grid and adding URL args. If you specify the "args" parameter, it will first add any args from that to the URL before adding any of its own args, thus preserving any args you are using for non-grid purposes.
 

I looked up the documentation, there is little info about args in grid / smartgrid signature. If above assumption is correct, if my applications uses first 2 args for specific application purpose, i should modify the code to SQLFORM.smartgrid(.....， args=request.args[:2], ...)?

Correct.
 

Another stupid question - what's difference between request.args[:1] and request.args(0)?

The former produces a list (even if it contains zero items or one item), whereas the latter returns an individual value.

def view_invoices():

    customer_id = request.args(0)

    if customer_id:

This is not a helpful conditional because once you click on any link produced by the grid, there will always be some value for request.args(0) (because the grid will have added some URL args to the URLs it generates). One option is something like:

    customer_id = request.args(0, cast=int, otherwise=lambda: None)
    if customer_id is not None:

The above will attempt to cast the first URL arg to an int and return None otherwise. The grid doesn't use ints as the first URL arg, so if the above returns None, that means there is no URL arg or it is not an int, which implies that no customer_id has been specified.

Anthony

[Rudy]

Anthony,

I can't thank you enough for all your advises and help, not only i understand more about the grid / smartgrid, but the use of request.args now. I appreciate a lot of your time. Rudy