list:reference is faster for finding referenced fields
many-to-many are faster for finding referencing fields.
Usually categories are nested. I assume you want tags instead of categories
(it is more general). I usually use both approaches and de-normalize it.
Below there is class Tag (which is a little complex but can be used out of
the box) and some example of usage:
class Tag(object):
class Validator(object):
def __call__(self,values):
if isinstance(values,str):
values = values.split(',')
tags = [IS_SLUG.urlify(x) for x in values]
tags = [x.lower() for x in tags if x]
return (tags, None)
def formatter(self,values):
return ', '.join(values) if values else ''
@staticmethod
def represent(v,r=None):
return SPAN(*(SPAN(t,_class='tag') for t in v)) if v else ''
@staticmethod
def after_insert(fields, record_id, field2):
if 'tags' in fields:
for tag in fields.tags:
d = {'name':tag, field2.name:record_id}
field2.table.insert(**d)
@staticmethod
def after_update(dbset,fields,field1,field2):
if 'tags' in fields:
subquery = dbset._select(field1.table.id)
field2._db(field2.belongs(subquery)).delete()
for tag in fields.tags:
d = {'name':tag, field2.name:record_id}
field2.table.insert(**d)
@staticmethod
def connect(field1,name="tag"):
db = field1._db
table =
db.define_table(name,Field('name'),Field(field1.name,'reference thing'))
field2 = table[field1.name]
field1.requires = Tag.Validator
field1.widget = SQLFORM.widgets.string.widget
field1.represent = Tag.represent
field1.table._after_insert.append(lambda f,u:
Tag.after_insert(f,u,field2))
field1.table._after_update.append(lambda f,u:
Tag.after_insert(f,u,field1,field2))
@staticmethod
def has_all(field1,tags,name="tag",mode="and"):
db = field1._db
table = db[name]
if isinstance(tags,str):
tags = [tags]
queries = []
for tag in tags:
queries.append(field1.table.id.belongs(db(table.name==tag)._select(table[field1.name])))
if mode=='and':
return reduce(lambda a,b:a&b, queries)
else:
return reduce(lambda a,b:a|b, queries)
@staticmethod
def has_any(field1,tags,name="tag"):
return Tag.has_all(field1,tags,name=name,mode="or")
### example of usage
db.define_table('thing', Field('name'), Field('tags','list:string'))
Tag.connect(db.thing.tags)
db.thing.insert(name='table', tags=['round','blue'])
print db(Tag.has_all(db.thing.tags,['round'])).select(db.thing.ALL)
print db(Tag.has_all(db.thing.tags,['blue'])).select(db.thing.ALL)
print db(Tag.has_all(db.thing.tags,['red'])).select(db.thing.ALL)
print db(Tag.has_all(db.thing.tags,['blue', 'round'])).select(db.thing.ALL)
print db(Tag.has_any(db.thing.tags,['red','round'])).select(db.thing.ALL)
On Friday, 17 January 2014 03:21:52 UTC-6, Najtsirk wrote:
>
> Hi,
>
> I have a dilemma about *list:reference, multiple=True vs. many-to-many
> with intermediate table.*
>
> I have Projects which haveto be in several categories.
>
> Is it better to use list:reference (to list categories from the categories
> table) or many-to-many? Which is faster in queries?
>
> Thanks for the answers.
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
