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 <[email protected]>
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 <[email protected]> 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
