Case closed.
After doing something else all day, the answer came to me ...
Change the controller to this:
# find the id of dogs, if any, that already belong to the owner
rows =
db(db.dog_owner.owner_id==request.args[-3]).select(
db.dog_owner.dog_id
)
# if there are such, build a list
if len(rows) > 0:
avoid = []
[avoid.append(row.dog_id) for row in rows]
query = ~(db.dog.id.belongs(avoid))
db.dog_owner.dog_id.requires = IS_IN_DB(
db(query), 'dog.id', '%(dog_name)s',
zero = 'Choose'
)
On Jan 3, 7:58 am, Cliff <[email protected]> wrote:
> I am trying to keep smartgrid from creating duplicate rows in a
> linking table, but it isn't working.
>
> Here is what I'm trying to do, but it isn't working.
>
> Any suggestions?
>
> Model:
> db.define_table('dog',
> Field('dog_name', length=32),
> auth.signature,
> format='%(dog_name)s',
> singular = 'Dog',
> plural = 'Dogs',
> )
>
> db.define_table('owner',
> Field('name', length=32),
> auth.signature,
> format = '%(name)s',
> )
>
> db.define_table('dog_owner',
> Field('dog_id', db.dog),
> Field('owner_id', db.owner),
> )
>
> db.dog_owner.dog_id.requires=IS_IN_DB(
> db, 'dog.id', '%(dog_name)s',
> zero='Choose'
> )
> db.dog_owner.owner_id.requires=IS_IN_DB(
> db, 'owner.id', '%(name)s',
> zero='Choose',
> )
>
> ++++++++++++++++++++++++++++++++++++++++++++++
> Controller code intended to avoid duplicates:
> ++++++++++++++++++++++++++++++++++++++++++++++
> # creating a new row in the dog_owner table
> for arg in request.args:
> if '.' in arg and 'new' in request.args:
>
> # find the id of dogs, if any, that already belong to the owner
> rows = db(db.dog_owner.owner_id==request.args[-3]).select(
> db.dog_owner.dog_id
> )
>
> # if there are such, build a list
> if len(rows) > 0:
> avoid = []
> [avoid.append(row.dog_id) for row in rows]
> constraints = {'dog': ~(db.dog.id.belongs(avoid)) }
>
> # also tried simple constraints such as this:
> # {'dog': db.dog.id < 5}
>
> # the dog pulldown still displays all the dogs in the db
> # thereby allowing the user to create duplicate rows in dog_owner
> # how can I fix this?
> form = SQLFORM.smartgrid(
> db.owner,
> ui='jquery-ui',
> onupdate=lambda form: auth.archive(
> form,
> archive_current=False
> ),
> constraints=constraints
> )
