Problem with grid search feature

[Iago Pinal]

Hello,

I am using an SQLFORM.grid to interact with a database. When I use the LEFT JOIN with this form the search fields in the SQLFORM.grid repeats the search fields (see screen capture).

Did you any of you have any clue why this could be happening and how to solve it?

Thank you

Iago

#MODEL

db = DAL('sqlite://storage.myositis_db')

db.define_table('d_dx',

    Field('dx_code', 'string', label='Diagnosis code'),

    Field('dx_descr', 'string', label='Diagnosis'))

db.define_table('d_subdx',

    Field('subdx_code', 'integer', label='Subdiagnosis code'),

    Field('subdx_descr', 'string', label='Subdiagnosis'))

db.define_table('d_atb',

    Field('atb_code', 'integer', label='Antibody code', notnull=True, unique=True),

    Field('atb_descr', 'string', label='Antibody', notnull=True, unique=True))

db.define_table('general',

    Field('general_id', 'id', label='ID', notnull=True, unique=True),

    Field('mrn', 'string', label='MRN', notnull=True, unique=True),

    Field('first_name', 'string'),

    Field('last_name', 'string'),

    Field('serum_number', 'integer'),

    Field('clinical_dx', 'reference d_dx', 'string', label='Clinical diagnosis', default='Unknown'),

    Field('clinical_subdx', 'string', label='Clinical subdiagnosis', default='Unknown'),

    Field('atb_dx', 'string', label='Antibody', default='Unknown'))

#Enforce rules

db.general.mrn.requires = [IS_NOT_EMPTY(error_message='The medical record field cannot be empty'),

                           IS_MATCH('^BV\d{8}$', error_message='Medical record number is in the wrong format'),

                           IS_NOT_IN_DB(db, db.general.mrn, error_message='The medical record is already in the database')]

db.general.clinical_dx.requires = IS_IN_DB(db, db.d_dx.dx_code, '%(dx_descr)s', zero=None)

db.general.clinical_subdx.requires = IS_IN_DB(db, db.d_subdx.subdx_code, '%(subdx_descr)s', zero=None)

db.general.atb_dx.requires = IS_IN_DB(db, db.d_atb.atb_code, '%(atb_descr)s',zero=None)

auth = Auth(db)

auth.define_tables()

auth.settings.registration_requires_verification = False

auth.settings.registration_requires_approval = True

auth.settings.reset_password_requires_verification = False

#CONTROLLER

@auth.requires_login()

def patients_list():

    fields=[db.general.general_id,

            db.general.mrn,

            db.general.first_name,

            db.general.last_name,

            db.general.serum_number,

            db.d_dx.dx_descr,

            db.d_subdx.subdx_descr,

            db.d_atb.atb_descr]

    links=[dict(header='Edit', body=lambda row: A(T('Edit'),_href=URL('default', 'test',args=[row.get('general', row).general_id])))]

    left = [db.d_dx.on(db.general.clinical_dx==db.d_dx.dx_code), db.d_subdx.on(db.general.clinical_subdx==db.d_subdx.subdx_code), db.d_atb.on(db.general.clinical_dx==db.d_atb.atb_code)]

    grid = SQLFORM.grid(db.general.id>0,

                        fields=fields,

                        left=left,

                        links=links,

                        orderby=[db.general.id],

                        exportclasses=dict(csv_with_hidden_cols=False, xml=False, tsv_with_hidden_cols=False, tsv=False, json=False, html=False),

                        deletable=True,

                        editable=True,

                        details=True,

                        paginate=50)

    return locals()

[Marlysson Silva]

You sure that that your "on" are between tables with id equals?

Likely are happening cartesian product .. I think

[Iago Pinal]

Hi, could you extend on what do you mean with checking that the "on" between tables with id equals?

The grid itself looks good.

Thank you.

Iago

[Iago Pinal]

Hello,
Just in case anybody else has the same problem. I found a quick and dirty solution modifying the grid element that shows the list of options.
This does not seem to be the optimal solution though. I will thank any further feedback in how to solve this issue.

Iago

#Fix search list
option_list = grid.element(_id='w2p_query_fields')
   
if option_list != None:
    for i in range(len(option_list)):
        option_list[i] = None
    for index, i in enumerate(fields):
         option_list[index] = OPTION(i.label, _value=i)