Excel like filter in grid

[Gael Princivalle]

Hello.

I would like to make a grid with some drop down menus for filtering results.

Is there already something that exist ?

Thanks.

[Gael Princivalle]

Done it:

http://www.hydrover.it/en/search_product

Thank's again to all the web2py community for the support.

If someone need to do a similar job I'll be happy to give a help.

[Antonio Salazar]

This is very nice and user friendly!

I noticed it takes a second or more per request. Does the number of criteria combinations affect performance?

[Ron Chatterjee]

How did you do it? Can you share?

[Gael Princivalle]

Everytime the user make a change in the criteria there are 6 areas to update. PC1/2/3/4 and NG select, because only the choices that fits to the actual criteria are displayed, and the product list. In the search field I've add a delay for the ajax callback, for avoiding ajax callbacks during the typing.

I've got a shared webfaction hosting in UK, so perhaps if you are far away for Europe it could add some delay.

[Gael Princivalle]

Sure Ron I can share.

In a few words on each select I've add a call to a javascript function that say which select has been change, for avoiding to call an update on it:

onchange="update_data('from_cm2')"

The update_data function call by ajax all changes:

        function update_data(from){
            if (from != 'from_cm1') {
                ajax('{{=URL('default','get_options_for_cm1_id')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], ':eval');
            }
            if (from != 'from_cm2') {
                ajax('{{=URL('default','get_options_for_cm2_id')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], ':eval');
            }
            if (from != 'from_cm3') {
                ajax('{{=URL('default','get_options_for_cm3_id')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], ':eval');
            }
            if (from != 'from_cm4') {
                ajax('{{=URL('default','get_options_for_cm4_id')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], ':eval');
            }
            if (from != 'from_ng') {
                ajax('{{=URL('default','get_options_for_ng')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], ':eval');
            }
            switch (from) {
                case 'from_previous':
                    ajax('{{=URL('default','get_products_grid', vars={'from':'from_previous'})}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], 'target_data');
                    break;
                case 'from_next':
                    ajax('{{=URL('default','get_products_grid', vars={'from':'from_next'})}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], 'target_data');
                    break;
                default:
                    ajax('{{=URL('default','get_products_grid')}}', ['search_string','cm1_id','cm2_id','cm3_id','cm4_id','ng'], 'target_data');
            }
        }

An example of a get_options:

def get_options_for_cm1_id():
    search_string = request.vars['search_string']
    cm1_id = int(request.vars['cm1_id'])
    cm2_id = int(request.vars['cm2_id'])
    cm3_id = int(request.vars['cm3_id'])
    cm4_id = int(request.vars['cm4_id'])
    ng = int(request.vars['ng'])
    criteria = 0
    query_str = ''
    if len(search_string) > 0:
        query_main_str = "(db.products.code.contains('" + search_string + "')) | (db.products.material_number.contains('" + search_string + "')) | (db.products.description.contains('" + search_string + "'))"
    else:
        query_main_str = 'db.products.cm1 == db.cm1.id'
    if cm1_id != 0:
        cm1 = db.cm1(request.vars['cm1_id'])
    if cm2_id != 0:
        cm2 = db.cm2(request.vars['cm2_id'])
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm2 == cm2.id)'
        criteria += 1
    if cm3_id != 0:
        cm3 = db.cm3(request.vars['cm3_id'])
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm3 == cm3.id)'
        criteria += 1
    if cm4_id != 0:
        cm4 = db.cm4(request.vars['cm4_id'])
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm4 == cm4.id)'
        criteria += 1
    if ng > 0:
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.ng == ' + str(ng) + ')'
        criteria += 1
    if criteria > 0:
        query_str += ' & '
    query_str += '(db.cm1.id == db.products.cm1)'
    if request.uri_language == 'it':
        order_by = 'db.cm1.title_it'
    else:
        order_by = 'db.cm1.title_en'
    cms = db(eval(query_main_str)).select(db.cm1.ALL, orderby=eval(order_by), groupby=db.cm1.id, join=(db.products.on(eval(query_str))))
    html=''
    #html += query_str
    html += "<option value='0'>" + T('All') + "</option>" + '&#13;'
    if request.uri_language == 'it':
        for cm in cms:
            html += "<option value='" + str(cm.id) + "' "
            if cm.id == cm1_id:
                html += ' selected'
            html += ">" + cm.title_it + "</option>" + '&#13;'
    else:
        for cm in cms:
            html += "<option value='" + str(cm.id) + "' "
            if cm.id == cm1_id:
                html += ' selected'
            html += ">" + cm.title_en + "</option>" + '&#13;'
    return 'jQuery("#cm1_id").html("%s");' % html # jQuery("#cm1_id").change();

get_products_grid (that update the div target_data):

def get_products_grid():
    #take all vars
    search_string = request.vars['search_string']
    cm1_id = int(request.vars['cm1_id'])
    cm2_id = int(request.vars['cm2_id'])
    cm3_id = int(request.vars['cm3_id'])
    cm4_id = int(request.vars['cm4_id'])
    ng = int(request.vars['ng'])
    criteria = 0
    query_str = ''
    if len(search_string) > 0:
        query_str += "((db.products.code.contains('" + search_string + "')) | (db.products.material_number.contains('" + search_string + "')) | (db.products.description.contains('" + search_string + "')))"
        criteria += 1
    if cm1_id != 0:
        cm1 = db.cm1(cm1_id)
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm1 == cm1.id)'
        criteria += 1
    if cm2_id != 0:
        cm2 = db.cm2(cm2_id)
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm2 == cm2.id)'
        criteria += 1
    if cm3_id != 0:
        cm3 = db.cm3(cm3_id)
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm3 == cm3.id)'
        criteria += 1
    if cm4_id != 0:
        cm4 = db.cm4(cm4_id)
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.cm4 == cm4.id)'
        criteria += 1
    if ng != 0:
        if criteria > 0:
            query_str += ' & '
        query_str += '(db.products.ng == ' + str(ng) + ')'
        criteria += 1
    if criteria > 0:
        query_str += ' & '
    query_str += '(db.products.ecommerce>0)'
    n_page_rows = 10
    n_products = db(eval(query_str)).count()
    n_pages = (n_products / n_page_rows)
    if n_products % n_page_rows > 0:
        n_pages += 1
    if (request.vars['from'] == 'from_previous') & (session.current_first_product > n_page_rows):
        session.current_first_product -= n_page_rows
    elif (request.vars['from'] == 'from_next'):
        if session.current_first_product + n_page_rows < n_products:
            session.current_first_product += n_page_rows
    else: session.current_first_product = 0
    current_last_product = session.current_first_product + n_page_rows
    current_product_page = current_last_product / n_page_rows
    products = db(eval(query_str)).select(limitby=(session.current_first_product,current_last_product), orderby=db.products.code)
    if auth.is_logged_in():
        discounts=db(db.discounts.user_id == auth.user_id).select(db.discounts.ALL)
    html = ''
    html += "<div class='left'>" + str(n_products)
    if n_products > 1:
        html += ' ' + T('products found')
    else:
        html += ' ' + T('product found')
    '''
    if request.vars['from']:
        html += ' ' + request.vars['from']
    '''
    html += "</div>"
    html += "<div class='right'>" + T('Page') + ' ' + str(current_product_page) + "/" + str(n_pages) + '</div>'
    html += "<table class='table table-striped cmtable'>"
    html += "<thead>"
    html += "<tr>"
    html += "<th>" + T('Product code') + "</th>"
    html += "<th>" + T('Mat. nr.') + "</th>"
    html += "<th>" + T('Description') + "</th>"
    html += "<th>" + T('PC1') + "</th>"
    html += "<th>" + T('PC2') + "</th>"
    html += "<th>" + T('PC3') + "</th>"
    html += "<th>" + T('PC4') + "</th>"
    html += "<th>NG</th>"
    html += "<th>" + T('Price') + "</th>"
    html += "<th>" + T('Catalogue') + "</th>"
    html += "</thead>"
    html += "<tbody>"
    for product in products:
        html+= "<tr>"
        html+= "<td>" + product.code + "</td>"
        html+= "<td>" + product.material_number + "</td>"
        html+= "<td>" + product.description + "</td>"
        if request.uri_language == 'it':
            html+= "<td>" + product.cm1.title_it + "</td>"
            html+= "<td>" + product.cm2.title_it + "</td>"
            html+= "<td>" + product.cm3.title_it + "</td>"
            html+= "<td>" + product.cm4.title_it + "</td>"
        else:
            html+= "<td>" + product.cm1.title_en + "</td>"
            html+= "<td>" + product.cm2.title_en + "</td>"
            html+= "<td>" + product.cm3.title_en + "</td>"
            html+= "<td>" + product.cm4.title_en + "</td>"
        html+= "<td>"
        if product.ng > 0:
            html += str(product.ng)
        else:
            html += '-'
        html += "</td>"
        html+= "<td>"
        if product.special_offer:
            html+= T('Special offer, contact us.')
        else:
            if auth.is_logged_in():
                found_discount=False
                for discount in discounts:
                    if discount.brand == product.brand:
                        xcent_discount=discount.xcent
                        found_discount=True
                if found_discount and product.price_list > 0:
                    price_str = "{0:.2f}".format(product.price_list * (100 - xcent_discount) / 100) + "<small> EUR</small>"
                    html+= price_str
                else:
                    html+= T('Ask for it')
            else:
                html+= T('login required')
        html+= "</td>"
        html+= "<td>"
        html+= "<a href='" + product.pdf_path + "' target='blank' alt='Download pdf'>Download</a>"
        html+= "</td>"
        html+= "</tr>"
    html += "</tbody>"
    html += "</table>"
    html += "<div class='web2py_paginator'>"
    html += "</div>"
    return html

Hope it can help.

[Marlysson Silva]

@Gael Princivalle , good code, you could to post this code in GitHub , so everybody could see your code and can improvement them.

[Ron Chatterjee]

This can be a good plugin for w2p to have.  Add  this to grid or a table. Thank you Gael for sharing. Great work !

[Gael Princivalle]

Thank you guys but making a plug-in is another time investment and I have other priorities. If someone wants to make it I'll be happy to give a hand.

[Marlysson Silva]

The project is on github?

[Manuele Pesenti]

Dear Gael and all,
I recentrly do something like that, it's an interface to some json API REST service that permits to filter informations about some scientific publication exposed with that web service.
The main code is here[1] and the demo here[2]

I think it's quite easy to rewrite the _fetch[3] method in order to deal with some ordinary db table in stead of the api rest service...

I hope it could be usefull to someone.
Cheers

    Manuele

[1] https://github.com/manuelep/iitbiblio/commit/636395edf0ce49132bf18330f619be5022f47e64
[2] http://web2py-manuele.rhcloud.com/iitbiblio/plugin_iitBiblio/index
[3] https://github.com/manuelep/iitbiblio/blob/master/models/plugin_iitBiblio.py#L17


Il 26/07/16 21:32, Gael Princivalle ha scritto:

--
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.
For more options, visit https://groups.google.com/d/optout.