Query difference in SQLFORM.GRID

[Prasad Muley]

Hi All,

     

     I want to support multiple keywords search as well as web2py's built-in-search.

     I've written one module which will check if in_built_search is called or not.

     If in_built_search isn't called then search keywords in custom_module

def built_in_search(keywords):

    """This module will return True if opertor

       in built_in_search is used in keywords"""

    search_operators = ['=', '!=', '<', '>', '<=', '>=',

                        'starts with', 'contains', 'in', 'not in']

    return any(operator in keywords for operator in search_operators)

 Code of SQLFORM.GRID in controllers/default.py

 keywords = request.vars.keywords

 if keywords and not built_in_search(keywords):

        new_query, usr_company_exist = _custom_search_query(keywords, field_dicts)

       #field_dicts contains field_name and table attribute.

 grid = SQLFORM.grid(query, create=allow_create,

                                    orderby=~db.table_name.created_on,

                                    showbuttontext=False, csv=False, deletable=False,

                                    maxtextlengths=textlengths, searchable=True)

Here  I am building query according to request.vars.keywords

I've written a custom module to search keywords on selected attributes as :

It also searches on reference key (company_name) 

def _custom_search_query(keywords, field_dicts):

    """This module will build search each keyword in keywords

       in predefined table attributes"""

    keys = keywords.strip()

    user_company_exist = False

    import time

    start_time = time.time()

    filters = []

    print "\n Keywords", keys

    words = keys.split(' ')

    for field_name, db_table_attr in field_dicts.iteritems():

        #check for company name

        if field_name == 'company_name':

            company = db(db.company.name.contains(keys)).select().first()

            if company is not None:

                filters.append(db_table_attr == company.id)

                continue

        all_words_filter = []

        for word in words:

            all_words_filter.append(db_table_attr.contains(word))

        filters.append(reduce(lambda a, b: (a & b), all_words_filter))

    return reduce(lambda a, b: a | b, filters)

If I tried to search 'XYZ' (First_name_of_company) in search box, then it gives me incorrect records.

Actually It shows only 3 records.

I've printed count of query in pdb.

(Pdb) db(query).count()

139L

It is showing exact count as in database.

If I tried to search 'XYZ Private' (first and middle name of company) in search box, then It gives me correct records.

I've also printed query count in pdb.

(Pdb) db(query).count()

139L

It also shows exact count as in database.

Does any one know why is it giving different results?

[Anthony]

Is this the exact code? I notice that the output of the call to _custom_search_query gets assigned to the tuple (new_query, user_company_exist), but (a) that function does not return a tuple, and (b) you don't use new_query when calling the grid.

Anthony

[Prasad Muley]

Hi Anthony,

         Sorry for pasting insufficient code.

Following is code from controllers/xyz.py

def built_in_search(keywords):
    """This module will return True if opertor
       in built_in_search is used in keywords"""
    search_operators = ['=', '!=', '<', '>', '<=', '>=',
                        'starts with', 'contains', 'in', 'not in']
    return any(operator in keywords for operator in search_operators)

def _custom_search_query(user_company_locs, keywords, field_dicts):

    """This module will build search each keyword in keywords
       in predefined table attributes"""
    keys = keywords.strip()
    user_company_exist = False
    import time
    start_time = time.time()
    filters = []
    print "\n Keywords", keys
    words = keys.split(' ')
    for field_name, db_table_attr in field_dicts.iteritems():
        #check for company name
        if field_name == 'company_name':
            company = db(db.company.name.contains(keys)).select().first()

            if company is not None and company.id in user_company_locs:
                filters.append(db_table_attr == company.id)
                user_company_exist = True

            continue
        all_words_filter = []
        for word in words:
            all_words_filter.append(db_table_attr.contains(word))
        filters.append(reduce(lambda a, b: (a & b), all_words_filter))

    print "Time required custom build query", time.time() - start_time
    return reduce(lambda a, b: a | b, filters), user_company_exist

def controller_name():

      #following variable contains a list of all company ids

      user_company_locs = get_user_company_ids()

     query = (db.company.id.belongs(user_company_locs))
     

    status_query = valid_status_query #can't disclose it.

    #following dict it contains

    # { "id":db.company.id,

    # "name": db.company.name }
 

    field_dicts = { 'valid_table_attr_name' : valid_table_attr,
                        .
                        .
                       }

   keywords = request.vars.keywords

   if keywords and not built_in_search(keywords):
        new_query, usr_company_exist = _custom_search_query(

            user_company_locs, keywords, field_dicts)

        #entered company_name exist in user_company_locs list then
        #display only single record only single.
        if usr_company_exist:
            query  = new_query & status_query
        else:
            query &= status_query & new_query

 
         grid = SQLFORM.grid(query, create=allow_create,
                         orderby=~db.valid_table_name.created_on,

                         showbuttontext=False, csv=False, deletable=False,
                         maxtextlengths=textlengths, searchable=True)

I've printed query object in pdb.

IF I search "XYZ" keyword then it shows correct count in pdb -> db(query).count()

But grid shows only 3 records

IF I search "XYZ Private" or "XYZ P" or any valid word after "XYZ" 
then it also shows correct count in pdb as well as on grid.

Why is grid showing different records (count)?

It is showing same record (count) on DAL or PDB.

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/jPPj8zbV7k4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thanks and Regards
Prasad M. Muley

Programmer at One Delta Synergies Pvt Ltd. | PICT 2013

“Pretty much everything on the web uses those two things: C and UNIX,” - Dennis Ritchie
                               http://www.cs.bell-labs.com/who/dmr/        

[Anthony]

Hard to say what's going on. I would suggest looking at db._timings (which is available in response.toolbar()) after running each version of that query on the grid. db._timings will show the exact SQL that was executed to get the count and then to return the actual records. Perhaps that will provide a clue.

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.