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 On Friday, April 12, 2013 2:23:37 PM UTC-4, Jim S wrote: > > 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"<http://10.10.2.34:8000/admin/default/edit/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"<http://10.10.2.34:8000/admin/default/edit/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 > > On Friday, April 12, 2013 12:56:34 PM UTC-5, Jim S wrote: >> >> 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" >> <http://10.10.2.34:8000/admin/default/edit/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" >> <http://10.10.2.34:8000/admin/default/edit/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" >> <http://10.10.2.34:8000/admin/default/edit/infocenter/controllers/applications.py>, >> line 853, in <lambda> >> >> lambda r: r.product.productName, zero='..') >> AttributeError: 'Row' object has no attribute 'product' >> >> >> >> On Fri, Apr 12, 2013 at 12:10 PM, Jim Steil <[email protected]<javascript:> >> > wrote: >> >>> Shoot, should have thought of that. >>> >>> Will try when I get back to the office. >>> >>> Jim >>> On Apr 12, 2013 12:04 PM, "Anthony" <[email protected] <javascript:>> >>> wrote: >>> >>>> 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 >>>> >>>> On Friday, April 12, 2013 12:38:09 PM UTC-4, Jim S wrote: >>>>> >>>>> Tried that already but got the same error. >>>>> >>>>> Jim >>>>> On Apr 12, 2013 11:32 AM, "Anthony" <[email protected]> wrote: >>>>> >>>>>> 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 >>>>>> >>>>>> On Friday, April 12, 2013 11:59:59 AM UTC-4, Jim S wrote: >>>>>>> >>>>>>> requires = IS_IN_DB(db((~db.productSite.**p** >>>>>>> roductSiteId.belongs(**currentIt**ems)) & >>>>>>> (db.productSite.**availableAddo**n==True) >>>>>>> & >>>>>>> >>>>>>> (db.productSite.siteId==**worko**rder.siteId) >>>>>>> & >>>>>>> (db.product.productId==db.**pro** >>>>>>> ductSite.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 >>>>>>> >>>>>> -- >>>>>> >>>>>> --- >>>>>> 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<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<https://groups.google.com/groups/opt_out> >>>>>> . >>>>>> >>>>>> >>>>>> >>>>> -- >>>> >>>> --- >>>> 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 >>>> [email protected] <javascript:>. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>>> >>>> >>> >> -- --- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
