You are passing a string in place of a query. Now sure what it does
but probably not what you want.
I think this does what you want.
customer_id = session.customer_id
query = db.properties.customer==customer_id
db.rooms.property.requires=IS_IN_DB(db(query),"properties.id")
form = SQLFORM(db.rooms)
On Feb 15, 8:13 am, Ed Greenberg <greenberg...@gmail.com> wrote:
> Sorry to be back with a request for clarification.
>
> My relationship is that a customer has properties and a property has
> rooms.
>
> I did this...
> customer_id = session.customer_id
> query = "select id from properties where customer = %d" %
> customer_id
> db.rooms.property.requires=IS_IN_DB(db(query),"properties.id")
> form = SQLFORM(db.rooms)
>
> So while creating the rooms form, I want to limit the properties
> dropdown to those properties owned by the customer, whose id is in the
> session.
>
> My query above produces a list of valid property IDs (when run outside
> this context). When run with the above code, it generates a sql
> error:
> <class 'gluon.contrib.pymysql.err.ProgrammingError'>((1064, u"You have
> an error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near 'select id from
> properties where customer = 2 ORDER BY properties.id' at line 1"))
>
> Pasting that exact sql into msyql works fine.
>
> First question: was I correct in writing a query to produce a list of
> IDs? Were other columns needed?
>
> Second question: am I using the field argument (second arg in the call
> to IS_IN_DB correctly?
>
> Third question concerns the generated sql : Why is the same sql
> failing in web2py that is succeeding in the mysql program. Perhaps I'm
> only seeing a fragment of what web2py submitted to mysql? How can I
> see the whole thing?
>
> Many thanks.
>
> Ed Greenberg
>
> On Feb 14, 4:19 pm, Massimo Di Pierro <massimo.dipie...@gmail.com>
> wrote:
>
>
>
>
>
>
>
> > def index():
> > query = ....
> > db.table.field.requires=IS_IN_DB(db(query),....)
> > form=SQLFORM(...)
> > if form.accepts(...)
> > ...
> > return dict(form=form)
>
> > On Feb 14, 3:06 pm, Ed Greenberg <greenberg...@gmail.com> wrote:
>
> > > When SQLFORM constructs an HTML form with a dropdown of foreign keys,
> > > I need to limit the records to only those that match a certain where
> > > clause.
>
> > > This is a master-detail problem. For instance, when displaying a
> > > detail form, we only want some of the maser records to be selectable
> > > (and even visible).
>
> > > A crud won't work reliably -- it's too generic. Do I have to create my
> > > own form and populate my dropdown, or can I have SQLFORM do the work
> > > in some fashion?
>
> > > Thanks
> > > Ed Greenberg
