How to list unique elements of one column from db.table in drop down menu

[Elif Guduk]

Hi all,

My question is about this menu construction to allow to select a gene to add/edit/see/remove in database.

 The problem I faced is in my database Gene table has more than one gene record per gene because of different drug interactions of one gene we have them multiple times in gene column. In order to list the unique list of gene for this above drop down menu to update table for users, I tried to wrote a def unique method and passed in to requires=IS_IN_SET in field in db.py script of models as you can see in here:

db.define_table('Gene',

    Field('Gene', requires=IS_IN_SET(unique(db.Gene))),

    Field('Variants'),

    Field('GroupID'),

    Field('Types'),

    Field('Flags'),

    Field('Genotype'),

    Field('Functional_Phenotype'),

    Field('Patient_Impact'),

    Field('Actions', requires=IS_IN_SET(["STOP", "STOP/SLOW", "SLOW", "GO", "GO/SLOW", "DOWN", "SLOW/DOWN", "DOWN/STOP", "None"])),

    Field('Action_Legend'),

    Field('Drug'),

    Field('Drug_Class', requires=IS_NOT_EMPTY()),

    Field('Mechanism_of_Action', requires=IS_NOT_EMPTY()),

    Field('Therapeutic_Area', requires=IS_NOT_EMPTY()),

    Field('Gene_Drug_Interaction', requires=IS_NOT_EMPTY()),

    Field('Sources', requires=IS_NOT_EMPTY()),

    Field('Clinical_Utility', requires=IS_NOT_EMPTY()),

    auth.signature,

    Field("Reviewed", type='boolean', default=False),

    format=lambda r: r.Gene)

Unique method

def unique(list1):

    # insert the list to the set

    list_set = set(list1)

    # convert the set to the list

    unique_list = (list(list_set))

    print unique_list

What am I doing wrong? This type of unique method and passing into this unique gene list should be in default.py in controller instead of db.py in models using db.select() but how exactly?

This is my default.py for form/menu construction of gene:

def index():

    """

    example action using the internationalization operator T and flash

    rendered by views/default/index.html or views/generic.html

    if you need a simple wiki simply replace the two lines below with:

    return auth.wiki()

    """

    form_Gene = SQLFORM.factory(Field("Gene", requires=IS_IN_DB(db, "Gene.id", db.Gene._format)))

    form_Drug = SQLFORM.factory(Field("Drug", requires=IS_IN_DB(db, "Drug.id", db.Drug._format)))

    if form_Gene.process(formname="form_g").accepted:

        response.flash = "search by gene"

        redirect(URL('Gene', args=[form_Gene.vars.Gene]))

    if form_Drug.process(formname="form_d").accepted:

        response.flash = "search by drug"

        redirect(URL('Drug', args=[form_Drug.vars.Drug]))

    return dict(form_Gene = form_Gene, form_Drug=form_Drug)

def Gene():

    Gene_id = request.args[0]

    try:

        Gene = db.Gene[Gene_id]

    except:

        session.flash = "Gene not found, redirect to Home page!"

        redirect(URL('default', 'index'))

     #gene = db(db.Gene.Gene==Gene_id).select(distinct=True)

    #genotype = db(db.Genotype.Gene==Gene_id).select()

    #gdR = db(db.GeneDrugR.Gene==Gene_id).select()

    #gdA = db(db.GeneDrugAssociation.Gene==Gene_id).select()

    #annoR = db(db.Annotation.Gene==Gene_id).select()

    return locals()

Thanks in advance,

Elif

[AGRogers]

Hi Elif

I dont know if i understand your issue correctly. But i will suggest something anyhow :)

Is there a reason you are using IS_IN_SET and not IS_IN_DB. It seems like IS_IN_DB is more appropriate. Plus you can specify the values are distinct which i think is what you are looking for.

It also feels like your table is not normalised as well as it could be. As soon as i start getting duplicate values when i want unique values i start to think about splitting the table into two with the unique stuff in its own table. 

Cheers

Andrew

PS: I like your name - very pythonesque :)

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/a6a4fa3d-3907-4fce-a5f4-d8b08e530360n%40googlegroups.com.