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
On Wednesday, November 19, 2014 11:43:45 AM UTC-5, Prasad Muley wrote:
>
> 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.
>
>
> On Wed, Nov 19, 2014 at 7:47 PM, Anthony <[email protected]> wrote:
>
>> 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
>>
>>
>> On Wednesday, November 19, 2014 6:16:19 AM UTC-5, Prasad Muley wrote:
>>>
>>> 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
>>> <http://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?
>>>
>>>
>>> --
>> 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
>> [email protected].
>> 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/*
> <http://www.cs.bell-labs.com/who/dmr/>
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
