Filtering a set of items from a collection issues

[John Smidt]

Hi,
I am trying to populate a drop down box so that a user may choose a "featured Image" from a collection of images. There are multiple collections, so I need to filter out the other images that are in the different collections. The code I have is shown below:

db.geo_collection.f_featured_img.requires=IS_IN_DB(db, (db.geo_item.f_collection_id == db.geo_collection.id),lambda row: '%s' % row.id + " - " + row.f_name)

What I had in mind is that the program would go through every item in the database, and compare its collection id with the collection's id that we are currently looking at. If the item passes this boolean statement, then it is added to the dropdown box.

When I do this, it states that there are too many values to unpack. Traceback below:

Traceback (most recent call last):
  File "/home/SIRI/web2py/gluon/restricted.py", line 217, in restricted
    exec ccode in environment
  File "/home/SIRI/web2py/applications/mqr/models/db_wizard.py", line 149, in <module>
    db.geo_collection.f_featured_img.requires=IS_IN_DB(db, (db.geo_item.f_collection_id == db.geo_collection.id),lambda row: '%s' % row.id + " - " + row.f_name)
  File "/home/SIRI/web2py/gluon/validators.py", line 497, in __init__
    (ktable, kfield) = str(field).split('.')
ValueError: too many values to unpack

I thought that using the 'IS_IN_DB' command might be messing with this, so I changed it to 'IS_IN_SET'. I got this error instead:

Traceback (most recent call last):
  File "/home/SIRI/web2py/gluon/restricted.py", line 217, in restricted
    exec ccode in environment
  File "/home/SIRI/web2py/applications/mqr/controllers/cms.py", line 478, in <module>
  File "/home/SIRI/web2py/gluon/globals.py", line 372, in <lambda>
    self._caller = lambda f: f()
  File "/home/SIRI/web2py/gluon/tools.py", line 3239, in f
    return action(*a, **b)
  File "/home/SIRI/web2py/applications/mqr/controllers/cms.py", line 26, in display_manage
    oncreate=coll_create,onupdate=coll_create)
  File "/home/SIRI/web2py/gluon/sqlhtml.py", line 2764, in smartgrid
    user_signature=user_signature, **kwargs)
  File "/home/SIRI/web2py/gluon/sqlhtml.py", line 2093, in grid
    update_form = SQLFORM(table, record, **sqlformargs)
  File "/home/SIRI/web2py/gluon/sqlhtml.py", line 1145, in __init__
    inp = self.widgets.options.widget(field, default)
  File "/home/SIRI/web2py/gluon/sqlhtml.py", line 270, in widget
    options = requires[0].options()
  File "/home/SIRI/web2py/gluon/validators.py", line 428, in options
    items = [(k, self.labels[i]) for (i, k) in enumerate(self.theset)]
TypeError: 'Query' object does not support indexing

Any ideas on how I could do this? I feel like it's a really simple tweak, but I'm just not seeing it. Any help would be fantastic.

[Anthony]

First, you've got the arguments to IS_IN_DB wrong -- the first argument is a DAL connection object or a DAL Set object (which is what you want), and the second is a single field object (or a field name in 'table.field' format). Second, your query implies a join, but really you just want to filter f_collection_id based on a single value, so you probably want something like:

db.geo_collection.f_featured_img.requires = IS_IN_DB(
    db(db.geo_item.f_collection_id == current_collection_id),
    db.geo_item.id, ...)

You'll have to set current_collection_id to the appropriate value.

Anthony

[Massimo Di Pierro]

I think we may have changed the validator to IS_IN_SET but you are still passing the same parameters as to IS_IN_DB. They take different sets of parameters.

[Massimo Di Pierro]

I think we may have changed the validator to IS_IN_SET but you are still passing the same parameters as to IS_IN_DB. They take different sets of parameters.

On Monday, 12 October 2015 11:54:03 UTC-5, John Smidt wrote:

[John Smidt]

Ok, I get how the IS_IN_SET needs a filtered set as the first argument but now I'm not sure how to get the current_collection_id? This is all dealing with a SQLFORM.grid and my .requires statement is in my model so how do I get the id of the collection that was selected from the grid into my model for the filter?

[Anthony]

On Tuesday, October 13, 2015 at 12:21:10 PM UTC-4, John Smidt wrote:

Ok, I get how the IS_IN_SET needs a filtered set as the first argument but now I'm not sure how to get the current_collection_id? This is all dealing with a SQLFORM.grid and my .requires statement is in my model so how do I get the id of the collection that was selected from the grid into my model for the filter?

First, no reason not to use IS_IN_DB -- it is designed for this purpose.

Regarding the current collection ID, you can just set the validator within your controller (after you get the current ID) -- the "requires" attribute of a field can be changed at any time before the actual validation happens. Another option is to get it from request.vars (if the current request doesn't involve the controller action in question, then the value will simply be None).

Anthony

[John Smidt]

That worked! Thanks for the quick help!