The first 20 records appear in the same order in each case (using
DBBrowser). How does that help?
I *am* using the args (in code that appears before this), and do not use
any links generated by grid.
- Scott
On Monday, December 12, 2016 at 11:22:53 PM UTC-5, Anthony wrote:
>
> Run the following two queries using an external SQLite client (not web2py
> or the DAL):
>
> SELECT t_dog.id, t_dog.f_Dog_ID, t_dog.f_Name, t_dog.f_Breed, t_dog.
> f_Verified, t_dog.f_Lookup, t_dog.f_User, t_dog.uuid FROM t_dog WHERE (
> t_dog.id IN (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,
> 1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706
> ,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598
> ,603,606,607,610,613,615,618,659,634,635,638)) ORDER BY t_dog.f_Dog_ID
> LIMIT 20 OFFSET 0;
>
> SELECT t_dog.f_Name, t_dog.f_Breed, t_dog.f_Dog_ID, t_dog.id FROM t_dog
> WHERE (t_dog.id IN (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,
> 1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700
> ,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,
> 1422,598,603,606,607,610,613,615,618,659,634,635,638)) ORDER BY t_dog.
> f_Dog_ID, t_dog.id LIMIT 20 OFFSET 0;
>
> Do you get exactly the same results in each case? If so, do you get a
> different top 20 results when you run:
>
> SELECT t_dog.id, t_dog.f_Dog_ID, t_dog.f_Name, t_dog.f_Breed, t_dog.
> f_Verified, t_dog.f_Lookup, t_dog.f_User, t_dog.uuid FROM t_dog WHERE (
> t_dog.id IN (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,
> 1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706
> ,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598
> ,603,606,607,610,613,615,618,659,634,635,638)) ORDER BY t_dog.f_Dog_ID;
>
> Also, why do you have args=request.args in your call to the grid? The
> "args" argument is for args in the URL that you want the grid to preserve
> (i.e., args that are not related to the grid itself), but you don't appear
> to be using any request.args elsewhere in your code. In any case, even if
> you do have some args to preserve, you would not want to include all of
> request.args, because when a grid link is clicked, that will include the
> grid's args as well.
>
> Anthony
>
> On Monday, December 12, 2016 at 9:32:45 PM UTC-5, Scott Hunter wrote:
>>
>> This is the code for my grid:
>>
>> query = (db.t_classlevel.f_Trial==t.id)&(db.t_classlevel.id
>> ==db.t_run.f_Class_Level)&(db.t_run.f_Dog==db.t_dog.id)
>> fields = [db.t_dog.f_Name,db.t_dog.f_Breed,db.t_dog.f_Dog_ID]
>> dog_ids = [x.id for x in db(query).select(db.t_dog.id
>> ,distinct=True)]
>> query = db.t_dog.id.belongs(dog_ids)
>> # These two loops were added to demonstrate the problem
>> for d in db(query).select(orderby=db.t_dog.f_Dog_ID):
>> print d.f_Dog_ID, d.f_Name, d.f_Breed, d.f_User
>> print"---"
>> for d in
>> db(query).select(orderby=db.t_dog.f_Dog_ID,limitby=(0,20)):
>> print d.f_Dog_ID, d.f_Name, d.f_Breed, d.f_User
>> headers =
>> {'t_dog.f_Name':'Name','t_dog.f_Breed':'Breed','t_dog.f_Dog_ID':'UKI ID'}
>> args = { 'maxtextlength':{'t_dog.f_Breed':30},
>> 'orderby':db.t_dog.f_Name, 'editable':False, 'deletable':False}
>> links = [{'header':'Manage Entry','body':lambda
>> r:A('Edit',_href=URL(f='entry_details',args=[t.f_UKI_ID,r.f_Dog_ID],user_signature=True))}]
>> links.append( {'header':'Scribe Sheets',
>> 'body': lambda r: SPAN( *[A(("" if i==0 else ",
>> ")+d.f_Date.strftime("%A"),
>>
>> _href=URL(c='course',f='scribe_sheets',extension='pdf',vars={'set':'%d_%d_%d'%(t.f_UKI_ID,i,r.f_Dog_ID)},user_signature=True),
>> _target="_blank") for i,d in
>> enumerate(tds)] ) } )
>> form = SQLFORM.grid(query, csv=False, details=False,
>> fields=fields, headers=headers, searchable=False, create=False,
>> args=request.args,
>> links = links, **args)
>> return locals()
>>
>> This what gets printed:
>>
>> 99 Test Collie 2
>>
>> 1350 Tovah Border Collie 2
>>
>> 1582 Lexi Border Collie 2
>>
>> 1702 Bean Border Collie 2
>>
>> 1704 Ginger Border Collie 2
>>
>> 3865 Owen Border Collie 2
>>
>> 4099 Flirt Border Collie 2
>>
>> 5169 Super G Border Collie 2
>>
>> 5199 Emme Border Collie 2
>>
>> 5202 Beamer Border Collie 2
>> ...
>>
>> ---
>>
>> 1702 Bean Border Collie 2
>>
>> 1704 Ginger Border Collie 2
>>
>> 3865 Owen Border Collie 2
>>
>> 4099 Flirt Border Collie 2
>>
>> 5169 Super G Border Collie 2
>>
>> 5203 Audie Border Collie 2
>>
>> The list that appears after the --- is EXACTLY what appears in the list
>> grid, once I have specified to sort on f_Dog_ID.
>>
>> These are the relevant SQL statements report via the response.toolabr:
>>
>> SELECT DISTINCT t_dog.id FROM t_dog, t_classlevel, t_run WHERE
>> (((t_classlevel.f_Trial = 196) AND (t_classlevel.id = t_run.f_Class_Level))
>> AND (t_run.f_Dog = t_dog.id));
>>
>> 5.39ms
>>
>> SELECT t_dog.id, t_dog.f_Dog_ID, t_dog.f_Name, t_dog.f_Breed,
>> t_dog.f_Verified, t_dog.f_Lookup, t_dog.f_User, t_dog.uuid FROM t_dog WHERE
>> (t_dog.id IN
>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638))
>> ORDER BY t_dog.f_Dog_ID;
>>
>> 0.17ms
>>
>> SELECT t_dog.id, t_dog.f_Dog_ID, t_dog.f_Name, t_dog.f_Breed,
>> t_dog.f_Verified, t_dog.f_Lookup, t_dog.f_User, t_dog.uuid FROM t_dog WHERE
>> (t_dog.id IN
>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638))
>> ORDER BY t_dog.f_Dog_ID LIMIT 20 OFFSET 0;
>>
>> 0.14ms
>>
>> SELECT count(*) FROM t_dog WHERE (t_dog.id IN
>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638));
>>
>> 0.12ms
>>
>> SELECT t_dog.f_Name, t_dog.f_Breed, t_dog.f_Dog_ID, t_dog.id FROM t_dog
>> WHERE (t_dog.id IN
>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638))
>> ORDER BY t_dog.f_Dog_ID, t_dog.id LIMIT 20 OFFSET 0;
>>
>> On Monday, December 12, 2016 at 9:00:09 PM UTC-5, Anthony wrote:
>>>
>>> On Monday, December 12, 2016 at 6:55:27 PM UTC-5, Scott Hunter wrote:
>>>>
>>>> Web2py: version 2.14.6, on OSX (from source, python 2.7.x) and at
>>>> pythonanywhere (for the version that works properly).
>>>>
>>>> I think this is the SQL you are asking for:
>>>>
>>>> PRAGMA foreign_keys=ON;
>>>>
>>>> 0.01ms
>>>>
>>>> SELECT auth_group.id, auth_group.role, auth_group.description FROM
>>>> auth_group WHERE (auth_group.role = 'admin');
>>>>
>>>> 0.40ms
>>>>
>>>> SELECT auth_membership.id, auth_membership.user_id,
>>>> auth_membership.group_id FROM auth_membership WHERE
>>>> ((auth_membership.user_id = 1) AND (auth_membership.group_id = 1));
>>>>
>>>> 0.10ms
>>>>
>>>> SELECT t_trial.id, t_trial.f_Name, t_trial.f_Club, t_trial.f_UKI_ID,
>>>> t_trial.f_Date, t_trial.f_Days, t_trial.f_RunFee, t_trial.f_MSFee,
>>>> t_trial.f_CourseOrder, t_trial.f_LocalKey, t_trial.f_LabelType,
>>>> t_trial.f_User, t_trial.f_PaperClasses, t_trial.uuid,
>>>> t_trial.f_ReserveUser, t_trial.f_Access, t_trial.f_xlsx,
>>>> t_trial.f_Next_Contact, t_trial.f_Next_Raw, t_trial.f_Next_Measurement,
>>>> t_trial.f_Paid, t_trial.f_PaidWhen, t_trial.f_PaidAmount,
>>>> t_trial.f_PaymentID, t_trial.f_Status, t_trial.f_Requested,
>>>> t_trial.f_PaymentRecord FROM t_trial WHERE (t_trial.f_UKI_ID = 1321);
>>>>
>>>> 0.17ms
>>>>
>>>> SELECT auth_group.id, auth_group.role, auth_group.description FROM
>>>> auth_group WHERE (auth_group.role = 'admin');
>>>>
>>>> 0.04ms
>>>>
>>>> SELECT auth_membership.id, auth_membership.user_id,
>>>> auth_membership.group_id FROM auth_membership WHERE
>>>> ((auth_membership.user_id = 1) AND (auth_membership.group_id = 1));
>>>>
>>>> 0.03ms
>>>>
>>>> SELECT t_trialday.id, t_trialday.f_TrialDay, t_trialday.f_Date,
>>>> t_trialday.f_CourseNames, t_trialday.f_CLMap, t_trialday.f_SmallToTall,
>>>> t_trialday.f_Heights, t_trialday.f_Judge, t_trialday.f_ResultCounter,
>>>> t_trialday.f_LastResultCourse, t_trialday.f_LastResultClassCourse,
>>>> t_trialday.f_MCJFirst, t_trialday.uuid FROM t_trialday WHERE
>>>> (t_trialday.f_TrialDay LIKE '1321-%' ESCAPE '\') ORDER BY
>>>> t_trialday.f_Date;
>>>>
>>>> 0.32ms
>>>>
>>>> SELECT DISTINCT t_dog.id FROM t_dog, t_classlevel, t_run WHERE
>>>> (((t_classlevel.f_Trial = 196) AND (t_classlevel.id =
>>>> t_run.f_Class_Level)) AND (t_run.f_Dog = t_dog.id));
>>>>
>>>> 5.50ms
>>>>
>>>> SELECT count(*) FROM t_dog WHERE (t_dog.id IN
>>>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638));
>>>>
>>>> 0.18ms
>>>>
>>>> SELECT t_dog.f_Name, t_dog.f_Breed, t_dog.f_Dog_ID, t_dog.id FROM t_dog
>>>> WHERE (t_dog.id IN
>>>> (1666,1667,1669,1671,1673,650,1675,652,1677,1678,1680,1688,1682,1411,1684,661,662,664,1689,1690,667,1692,1694,1696,673,1698,1700,1706,683,685,691,692,694,649,698,699,578,651,580,585,587,588,589,1686,1422,598,603,606,607,610,613,615,618,659,634,635,638))
>>>> ORDER BY t_dog.f_Dog_ID, t_dog.id LIMIT 20 OFFSET 0;
>>>>
>>>> 0.15ms
>>>>
>>>> PRAGMA foreign_keys=ON;
>>>>
>>>>
>>>> Now sure what you mean by "the SQL you are running"
>>>>
>>>
>>> You said, "If I do the query myself." What did you mean by that?
>>>
>>> Are you sure you have the same dataset on your Mac? If you run the above
>>> SQL query (the last one) outside of web2py, do you get a different set of
>>> records from what you see in the grid?
>>>
>>> Anthony
>>>
>>>>
--
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.
