Issue with lookup foreign key / format statement in 2.10.3

[wish...@gmail.com]

Hey guys!

Did 2.10.3-stable+timestamp.2015.04.02.21.42.07 change something in the way format statements %(fields)s are handled?

When referencing another table, the format statement now seems to be ignored. Instead only the foreign key id is displayed.

Example

I have the following lookup table

db.define_table('countries',
    Field('country', 'string'),
    Field('the_geom', 'geometry()'),
    Field.Virtual('latitude', lambda row: db(db.countries.id == row.countries.id).select(db.countries.centroid.st_y()).first()[db.countries.centroid.st_y()]),
    Field.Virtual('longitude', lambda row: db(db.countries.id == row.countries.id).select(db.countries.centroid.st_x()).first()[db.countries.centroid.st_x()]),
    format='%(country)s', migrate=True)
   
Another table is referencing this lookup table

db.define_table('uploads',
    Field('country', db.countries),
    Field('uploaded','date'),
    ...
    migrate=True)
               
Now I would like to count the number of uploads per country.

def count_uploads_by_country():
    import datetime
    from datetime import timedelta
    count = db.wifi_zone.id.count()
    result = db(db.uploads.uploaded > datetime.date.today() -  timedelta(days=7)).select(db.uploads.country, count, groupby = db.uploads.country).render()
    return dict(result=result)
   
I would expect that a query on the uploads table would display the country name as specified in the format statement, i.e.
Country  Uploads per Country
France     123
Italy           45
Germany   10

Until recently this worked perfectly, but following the update to 2.10.3 only the country ids are returned, i.e.
Country  Uploads per Country
1   <-- foreign id instead of name
2   <--                45
3   <--                10

db._lastsql shows that the country name isn't even queried:
SELECT uploads.country, COUNT(uploads.id) FROM uploads WHERE (uploads.last_updated > '2015-04-04') GROUP BY uploads.country;

Does anybody have a clue, why only the foreign id is displayed, but not the country name according to the format statement?

Cheers
Toby

[Niphlod]

yes, it's a bug. there's no reference anymore to the format.........opened on web2py/pydal

[wish...@gmail.com]

I just discovered, there's already a issue posted..

https://groups.google.com/forum/#!topic/web2py/Tog4tdUl400
https://github.com/web2py/web2py/issues/904

Are there already any solutions yet?

Thanks & cheers
Toby

wish...@gmail.com:

[Niphlod]

being a bug, we need to ship a new web2py. it got fixed already. we're waiting for Massimo to release a 2.10.4.

[wish...@gmail.com]

Thats's good news!

Thanks for taking care!
Cheers
Toby

[Dave S]

On Tuesday, April 14, 2015 at 2:29:48 PM UTC-7, Niphlod wrote:

being a bug, we need to ship a new web2py. it got fixed already. we're waiting for Massimo to release a 2.10.4.

Is that fix included in the nightly builds yet?
 
/dps

[Massimo Di Pierro]

No tomorrow.

[Jim S]

Can someone tell me if this is fixed?

I'm still having issues but maybe I'm doing something wrong:

I have this in my db.py:

db.define_table('customer',

Field('customerId', 'id', readable=True, writable=False, label='Customer #'),

Field('name', length=30, required=True, writable=False,

 requires=IS_NOT_EMPTY()),

Field('city', length=30, writable=False),

Field('state', length=2, writable=False),

format='%(customerId)s - %(name)s - %(city)s, %(state)s')

db.define_table('equip_order',

Field('id', 'id', readable=False, label='Order #'),

Field('order_type', 'reference orderType', label='Order Type', ondelete='RESTRICT',

  requires = IS_IN_DB(db(db.orderType.equipment==True),

  'orderType.id', '%(name)s', zero='..')),

Field('status', length=10, default='New',

  requires=IS_IN_SET(('New', 'Submitted', 'Invoiced'))),

Field('customer', 'reference customer', label='Customer', ondelete='RESTRICT'))

When I display the customer field in the equip_order table in a custom SQLFORM.grid form using:

{{=form.custom.widget.customer}}

...it displays the id of the customer, not the name.

I thought the 'format' on the customer table definition would control this. I know I can fix it by changing the definition of the customer field in the equip_order table to:

Field('customer', 'reference customer', label='Customer', ondelete='RESTRICT',

  represent=lambda x, r: '%s - %s - %s, %s' % (db.customer(x).customerId

db.customer(x).name,

db.customer(x).city,

db.customer(x).state) if x else ''),

Should I have to do that, or should the 'format' on customer take care of it?

-Jim

[Dave S]

On Monday, April 20, 2015 at 10:02:44 AM UTC-7, Jim S wrote:

Can someone tell me if this is fixed?

I'm still having issues but maybe I'm doing something wrong:

What version of Web2Py are you running?

/dps
 

[Jim Steil]

I'm running 2.10.4 beta.  Grabbed it from github this morning.

--
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/2i9zfAzG2s8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hilton Shumway]

On Monday, April 20, 2015 at 4:30:03 PM UTC-6, Jim S wrote:

I'm running 2.10.4 beta.  Grabbed it from github this morning.

On Mon, Apr 20, 2015 at 5:10 PM, Dave S <snide...@gmail.com> wrote:

On Monday, April 20, 2015 at 10:02:44 AM UTC-7, Jim S wrote:

Can someone tell me if this is fixed?

I'm still having issues but maybe I'm doing something wrong:

What version of Web2Py are you running?

I'm finding this same bug in latest git (54b385b32192d9f). Some testing determined that commit caa19765106522a51 was the first to introduce it.

Hilton

[Jim S]

My problem turned out to be that I didn't have the latest pydal.  I'd installed it separately from web2py and when I updated web2py, pydal didn't automatically update.  I removed my separate pydal and then installed the latest web2py (using git) and then everything worked for me.

[Hilton Shumway]

The pydal in my web2py download today (2.10.4) has the changes from https://github.com/web2py/pydal/pull/137, yet I still have the same issue. Should I open a separate bug?

[Massimo Di Pierro]

yes please.