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.