[web2py] Re: SQLFORM.smartgrid - Query Not Supported: (1066, "Not unique table/alias: 'a_table'")
Can you use db._timings to grab the generated SQL, both under 2.9.11 and 2.16.11, to see what the differences are? Anthony On Saturday, May 12, 2018 at 11:20:50 AM UTC-4, 98uj...@gmail.com wrote: > > After update from web2py 2.9.11 (mysql 5.6.37) to web2py 2.16.11 (mysql > 5.6.40) the SQLFORM.smartgrid shows zero results and at the top right > displays a message: Query Not Supported: (1066, "Not unique table/alias: > 'labprice'"). > This same code works in 2.9.11: > db.labprice._common_filter = lambda query: > db.labprice.lp_term_date==None > db.labour._common_filter = lambda query: db.labour.lab_term_date==None > left=db.labprice.on(db.labour.lab_id==db.labprice.lp_lab_id) > orderby=[db.labour.lab_description] > form > =SQLFORM.smartgrid(db.labprice,left=left,fields=fields,headers=headers,paginate='all',maxtextlengths=maxtextlengths,maxtextlength=40, > > buttons_placement='left',links_placement='left',deletable=False,orderby=orderby,linked_tables=['repair']) > Enter code here... > > Any help in making it work in the newer version of web2py will be > appreciated. > Thanks > > -- 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.
[web2py] Re: SQLFORM.smartgrid - Query Not Supported: (1066, "Not unique table/alias: 'a_table'")
db stats for 2.16.11 SELECT `labour`.`lab_description`, `labour`.`lab_id` FROM `labour` WHERE ((`labour`.`lab_id` IS NOT NULL) AND (`labour`.`lab_term_date` IS NULL)) ORDER BY `labour`.`lab_description`, `labour`.`lab_id`; 1.11ms --- db stats for 2.9.11 SET FOREIGN_KEY_CHECKS=1; 1.31ms SET sql_mode='NO_BACKSLASH_ESCAPES'; 1.29ms SELECT labour.lab_description, labour.lab_id FROM labour WHERE ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY labour.lab_description, labour.lab_id; 9.32ms SELECT labour.lab_description, labour.lab_id FROM labour WHERE ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY labour.lab_description, labour.lab_id; 9.07ms SELECT count(*) FROM labour LEFT JOIN labprice ON ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE (labprice.lp_id IS NOT NULL); 2.57ms SELECT labprice.lp_id, labprice.lp_lab_id, labprice.lp_unit_price, labprice.lp_comment, labprice.lp_intro_date FROM labour LEFT JOIN labprice ON ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE (labprice.lp_id IS NOT NULL) ORDER BY labour.lab_description; 47.51ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 97) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.12ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 112) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.09ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.05ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.04ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 125) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 120) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.06ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 128) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.02ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 118) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 33) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 114) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.03ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 119) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour WHERE ((labour.lab_id = 18) AND (labour.lab_term_date IS NULL)) LIMIT 1 OFFSET 0; 2.01ms
[web2py] Re: SQLFORM.smartgrid - Query Not Supported: (1066, "Not unique table/alias: 'a_table'")
I don't see the left join queries for 2.16.11. Anyway, maybe try running the different queries in a separate MySQL client and see if you can reproduce the error to isolate the problem. Anthony On Saturday, May 12, 2018 at 1:40:51 PM UTC-4, 98uj...@gmail.com wrote: > > db stats for 2.16.11 > > SELECT `labour`.`lab_description`, `labour`.`lab_id` FROM `labour` WHERE > ((`labour`.`lab_id` IS NOT NULL) AND (`labour`.`lab_term_date` IS NULL)) > ORDER BY `labour`.`lab_description`, `labour`.`lab_id`; > > 1.11ms > --- > > db stats for 2.9.11 > > SET FOREIGN_KEY_CHECKS=1; > > 1.31ms > > SET sql_mode='NO_BACKSLASH_ESCAPES'; > > 1.29ms > > SELECT labour.lab_description, labour.lab_id FROM labour WHERE > ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY > labour.lab_description, labour.lab_id; > > 9.32ms > > SELECT labour.lab_description, labour.lab_id FROM labour WHERE > ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY > labour.lab_description, labour.lab_id; > > 9.07ms > > SELECT count(*) FROM labour LEFT JOIN labprice ON ((labour.lab_id = > labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE > (labprice.lp_id IS NOT NULL); > > 2.57ms > > SELECT labprice.lp_id, labprice.lp_lab_id, labprice.lp_unit_price, > labprice.lp_comment, labprice.lp_intro_date FROM labour LEFT JOIN labprice ON > ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) > WHERE (labprice.lp_id IS NOT NULL) ORDER BY labour.lab_description; > > 47.51ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 97) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.12ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 112) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.09ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.04ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.03ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.03ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.05ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.04ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.02ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 125) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.06ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 120) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.06ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 128) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.02ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 118) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.03ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 33) AND (labour.lab_term_date IS NULL)) LIMIT 1 > OFFSET 0; > > 2.03ms > > SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, > labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour > WHERE ((labour.lab_id = 114) AND (labour.lab_
[web2py] Re: SQLFORM.smartgrid - Query Not Supported: (1066, "Not unique table/alias: 'a_table'")
I am a weekend coder and my queries probably need to be redesigned. I was trying to move everything to a newer environment and immediately weak areas fail first. In time I should be able to sort things out. I do enjoy working with web2py . Thanks for your input. On Monday, May 14, 2018 at 10:48:12 AM UTC-4, Anthony wrote: > > I don't see the left join queries for 2.16.11. Anyway, maybe try running > the different queries in a separate MySQL client and see if you can > reproduce the error to isolate the problem. > > Anthony > > On Saturday, May 12, 2018 at 1:40:51 PM UTC-4, 98u...@gmail.com > wrote: >> >> db stats for 2.16.11 >> >> SELECT `labour`.`lab_description`, `labour`.`lab_id` FROM `labour` WHERE >> ((`labour`.`lab_id` IS NOT NULL) AND (`labour`.`lab_term_date` IS NULL)) >> ORDER BY `labour`.`lab_description`, `labour`.`lab_id`; >> >> 1.11ms >> --- >> >> db stats for 2.9.11 >> >> SET FOREIGN_KEY_CHECKS=1; >> >> 1.31ms >> >> SET sql_mode='NO_BACKSLASH_ESCAPES'; >> >> 1.29ms >> >> SELECT labour.lab_description, labour.lab_id FROM labour WHERE >> ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY >> labour.lab_description, labour.lab_id; >> >> 9.32ms >> >> SELECT labour.lab_description, labour.lab_id FROM labour WHERE >> ((labour.lab_id IS NOT NULL) AND (labour.lab_term_date IS NULL)) ORDER BY >> labour.lab_description, labour.lab_id; >> >> 9.07ms >> >> SELECT count(*) FROM labour LEFT JOIN labprice ON ((labour.lab_id = >> labprice.lp_lab_id) AND (labprice.lp_term_date IS NULL)) WHERE >> (labprice.lp_id IS NOT NULL); >> >> 2.57ms >> >> SELECT labprice.lp_id, labprice.lp_lab_id, labprice.lp_unit_price, >> labprice.lp_comment, labprice.lp_intro_date FROM labour LEFT JOIN labprice >> ON ((labour.lab_id = labprice.lp_lab_id) AND (labprice.lp_term_date IS >> NULL)) WHERE (labprice.lp_id IS NOT NULL) ORDER BY labour.lab_description; >> >> 47.51ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 97) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.12ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 112) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.09ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.04ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 113) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.03ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.03ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 90) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.05ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.04ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 86) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.02ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 125) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.06ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 120) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.06ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 128) AND (labour.lab_term_date IS NULL)) LIMIT 1 >> OFFSET 0; >> >> 2.02ms >> >> SELECT labour.lab_id, labour.lab_description, labour.lab_intro_date, >> labour.lab_term_date, labour.lab_term_id, labour.lab_comment FROM labour >> WHERE ((labour.lab_id = 118) AND (labour.lab_term_date IS NULL)) LIMIT 1
