How do I specify my display field here in IS_IN_DB?

[Jim S]

    requires = IS_IN_DB(db((~db.productSite.productSiteId.belongs(currentItems)) &

                           (db.productSite.availableAddon==True) & 

                           (db.productSite.siteId==workorder.siteId) &

                           (db.product.productId==db.productSite.productId)), 

                        db.productSite.productSiteId,

                        '%(productName)s', zero='..')

I get a trace back with this.  If I pull out the '%(productName)s' it works.   But, I want to specify the field name to display being a field in the table linked by the 4 'anded' query.

Help?

-Jim

[Anthony]

Since you are joining tables, you might need to use the "tablename.fieldname" format to refer to the field in question. Does '%(product.productName)s' work (assuming "productName" is in the db.product table)?

Anthony

[Jim Steil]

Tried that already but got the same error.

Jim

--
 
---
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/vb0268kC_mc/unsubscribe?hl=en.
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/groups/opt_out.
 
 

[Anthony]

It probably should work, but for some reason, the code converts the Row to a dict before doing the string formatting, and that breaks for Rows involving joins. For now, you can use a lambda instead:

lambda r: r.product.productName

Anthony

[Jim Steil]

Shoot, should have thought of that.

Will try when I get back to the office.

Jim

[Jim Steil]

    requires = IS_IN_DB(db((~db.productSite.productSiteId.belongs(currentItems)) &

                           (db.productSite.availableAddon==True) & 

                           (db.productSite.siteId==workorder.siteId) &

                           (db.product.productId==db.productSite.productId)), 

                        db.productSite.productSiteId,

                        lambda r: r.product.productName, zero='..')

...gives me...

Traceback (most recent call last):
  File "C:\dev\web2py\gluon\restricted.py", line 212, in restricted

    exec ccode in environment
  File "C:/dev/web2py/applications/infocenter/controllers/applications.py", line 4291, in <module>

  File "C:\dev\web2py\gluon\globals.py", line 194, in <lambda>

    self._caller = lambda f: f()

  File "C:\dev\web2py\gluon\tools.py", line 2976, in f

    return action(*a, **b)
  File "C:/dev/web2py/applications/infocenter/controllers/applications.py", line 864, in workorderBom

    _formname='addonForm')
  File "C:\dev\web2py\gluon\sqlhtml.py", line 1599, in factory

    **attributes)
  File "C:\dev\web2py\gluon\sqlhtml.py", line 1113, in __init__

    inp = self.widgets.options.widget(field, default)

  File "C:\dev\web2py\gluon\sqlhtml.py", line 252, in widget

    options = requires[0].options()
  File "C:\dev\web2py\gluon\validators.py", line 546, in options

    self.build_set()
  File "C:\dev\web2py\gluon\validators.py", line 543, in build_set

    self.labels = [self.label(r) for r in records]

  File "C:/dev/web2py/applications/infocenter/controllers/applications.py", line 853, in <lambda>

    lambda r: r.product.productName, zero='..')

AttributeError: 'Row' object has no attribute 'product'

[Jim S]

What I really want to do is to use a syntax like this for the IS_IN_DB for the LEFT JOIN.

    requires = IS_IN_DB(db((~db.productSite.productSiteId.belongs(currentItems)) &
                           (db.productSite.availableAddon==True) &

                           (db.productSite.siteId==workorder.siteId))._select(left=db.product.on(db.product.productId==db.productSite.productId),
                                                                              orderby=[db.product.productName]),
                        db.productSite.productSiteId,
                        lambda r: r.product.productName, zero='..')

...because I'd like to control the order of the items in the list.  But, this method gives me the following traceback.

Traceback (most recent call last):
 File "C:\dev\web2py\gluon\restricted.py", line 212, in restricted
 exec ccode in

environment
 File "C:/dev/web2py/applications/infocenter/controllers/applications.py", line 4292, in <module>

 File "C:\dev\web2py\gluon\globals.py", line 194, in <lambda>
 self._caller = lambda f: f()
 File "C:\dev\web2py\gluon\tools.py", line 2976, in f
 return action(*a, **b)

 File "C:/dev/web2py/applications/infocenter/controllers/applications.py", line 865, in workorderBom
 _formname='addonForm')

 File "C:\dev\web2py\gluon\sqlhtml.py", line 1599, in factory
 **attributes)
 File "C:\dev\web2py\gluon\sqlhtml.py", line 1113, in __init__
 inp = self.widgets.options.widget(field, default)
 File "C:\dev\web2py\gluon\sqlhtml.py", line 252, in widget
 options = requires[0].options()
 File "C:\dev\web2py\gluon\validators.py", line 546, in options
 self.build_set()

 File "C:\dev\web2py\gluon\validators.py", line 518, in build_set
 table = self.dbset.db[self.ktable]
AttributeError: 'str' object has no attribute 'db'

I'm assuming because I'm now returning SQL instead of a query object.  Can I do  LEFT JOIN in IS_IN_DB?

-Jim

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Anthony]

Well, a change was just made in trunk to allow '%(tablename.fieldname)s' notation, but it looks like the query retains only the fields in the main table (db.productSite in this case) anyway, so I guess that won't help. So instead, maybe:

lambda r: db(db.product.productId == r.productId).select().first().productName

Anthony

[Anthony]

Not sure there's a way to do a left join, but seems like that could be enabled. If you can't get what you want, you might consider handling the queries to generate the items separately, and then pass them to IS_IN_SET.

Anthony

[Jim Steil]

So, that should allow me to sort though shouldn't it?

[Anthony]

You can specify an orderby for the query.

Anthony

[Jim Steil]

In the _select() ?

[Anthony]

No, you can't do the ._select(). However, IS_IN_DB takes orderby, groupby, and distinct arguments (though not "left", which presumably could be added) and uses them in the .select() that it does. You don't expect all this stuff to be documented, do you? We have to have something to talk about on Google Groups. ;-)

Anthony

[Jim Steil]

That perfect. I'll get it going next week. Thanks for the help with this.

Jim

[Jim Steil]

Thanks for the help on this Anthony.  I ended up using the lambda function and the orderby to get what I needed.  Would be great to see the left join supported here.

[Anthony]

Maybe submit an issue with that request.

[Jim S]

Submitted.  Issue 1454.

-Jim