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