Thanks, Massimo. On Friday, 17 January 2014 19:12:00 UTC+1, Massimo Di Pierro wrote: > > 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.