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.
