list:<type> and contains

web2py provides the following special field types:

list:string
list:integer
list:reference <table>

They can contain lists of strings, of integers and of references respectively.

On Google App Engine NoSQL list:string is mapped into StringListProperty, the other two are mapped into ListProperty(int). On relational databases they are mapped into text fields which contain the list of items separated by |. For example [1, 2, 3] is mapped into |1|2|3|.

For lists of string the items are escaped so that any | in the item is replaced by a ||. Anyway this is an internal representation and it is transparent to the user.

You can use list:string, for example, in the following way:

>>> db.define_table('product',
...                 Field('name'),
...                 Field('colors', 'list:string'))
<Table product (id, name, colors)>
>>> db.product.colors.requires = IS_IN_SET(('red', 'blue', 'green'))
>>> db.product.insert(name='Toy Car', colors=['red', 'green'])
1
>>> products = db(db.product.colors.contains('red')).select()
>>> for item in products:
...     print item.name, item.colors
... 
Toy Car ['red', 'green']

list:integer works in the same way but the items must be integers.

As usual the requirements are enforced at the level of forms, not at the level of insert.

For list:<type> fields the contains(value) operator maps into a non trivial query that checks for lists containing the value. The contains operator also works for regular string and text fields and it maps into a LIKE '%value%'.

The list:reference and the contains(value) operator are particularly useful to de-normalize many-to-many relations. Here is an example:

>>> db.define_table('tag',
...                 Field('name'),
...                 format='%(name)s')
<Table tag (id, name)>
>>> db.define_table('product',
...                 Field('name'),
...                 Field('tags', 'list:reference tag'))
<Table product (id, name, tags)>
>>> a = db.tag.insert(name='red')
>>> b = db.tag.insert(name='green')
>>> c = db.tag.insert(name='blue')
>>> db.product.insert(name='Toy Car', tags=[a, b, c])
1
>>> products = db(db.product.tags.contains(b)).select()
>>> for item in products:
...     print item.name, item.tags
... 
Toy Car [1, 2, 3]
>>> for item in products:
...     print item.name, db.product.tags.represent(item.tags)
... 
Toy Car red, green, blue

Notice that a list:reference tag field get a default constraint

requires = IS_IN_DB(db, db.tag._id, db.tag._format, multiple=True)

that produces a SELECT/OPTION multiple drop-box in forms.

Also notice that this field gets a default represent attribute which represents the list of references as a comma-separated list of formatted references. This is used in read forms and SQLTABLEs.

While list:reference has a default validator and a default representation, list:integer and list:string do not. So these two need an IS_IN_SET or an IS_IN_DB validator if you want to use them in forms.