Yes, web2py is not ok with it. Making 3 or 4 explicit joins and it gets errors
2013/10/31 Vinicius Assef <vinicius...@gmail.com> > That was my point, Diogo. > > Is there some fault when we have many explicit joins in DAL? > > > On Wed, Oct 30, 2013 at 4:01 PM, Diogo Munaro <diogo.mvie...@gmail.com> > wrote: > > Hi Vinicius! > > The query with a lot of natural joins really don't work, but join with > WHERE > > worked. > > > > I don't know what happend, but web2py become crazy when I set more > natural > > joins > > > > > > 2013/10/30 Vinicius Assef <vinicius...@gmail.com> > >> > >> Maybe I missed something, but why the simple query (with few joins) > >> worked and the complex one (with many joins) didn't? > >> > >> On Wed, Oct 30, 2013 at 2:31 PM, Diogo Munaro <diogo.mvie...@gmail.com> > >> wrote: > >> > Hi Michele, I'm looking here the results... > >> > > >> > If I get where and natural join is different. > >> > > >> > The explain is like that: > >> > > >> > > http://stackoverflow.com/questions/15996226/natural-join-vs-where-in-clauses > >> > > >> > Here is massimo suggested code: > >> > > >> > mysql> explain SELECT groups.name, city.name, auth_user.id, > >> > auth_user.is_active, auth_user.created_on, auth_user.created_by, > >> > auth_user.modified_on, auth_user.modified_by, auth_user.email, > >> > auth_user.person_id, auth_user.password, auth_user.know_id, > >> > auth_user.registration_key, auth_user.reset_password_key, > >> > auth_user.registration_id FROM researcher, researcher_lab_permission, > >> > lab, > >> > groups, auth_user, city WHERE ((((((((groups.id = lab.group_id) AND > >> > (lab.id > >> > = researcher_lab_permission.lab_id)) AND (researcher.id = > >> > researcher_lab_permission.researcher_id)) AND (researcher.user_id = > >> > auth_user.id)) AND (researcher_lab_permission.is_active = 'T')) AND > >> > (lab.is_active = 'T')) AND (groups.is_active = 'T')) AND > >> > (auth_user.is_active = 'T')) > >> > -> ; > >> > > >> > > +----+-------------+---------------------------+--------+--------------------------------+---------+---------+------------------------------------------------+------+--------------------------------+ > >> > | id | select_type | table | type | > possible_keys > >> > | key | key_len | ref | > >> > rows > >> > | Extra | > >> > > >> > > +----+-------------+---------------------------+--------+--------------------------------+---------+---------+------------------------------------------------+------+--------------------------------+ > >> > | 1 | SIMPLE | city | ALL | NULL > >> > | NULL | NULL | NULL | > >> > 5535 > >> > | | > >> > | 1 | SIMPLE | researcher_lab_permission | ALL | > >> > researcher_id__idx,lab_id__idx | NULL | NULL | NULL > >> > | 2 | Using where; Using join buffer | > >> > | 1 | SIMPLE | lab | eq_ref | > >> > PRIMARY,group_id__idx | PRIMARY | 4 | > >> > labsyn.researcher_lab_permission.lab_id | 1 | Using where > >> > | > >> > | 1 | SIMPLE | groups | eq_ref | PRIMARY > >> > | PRIMARY | 4 | labsyn.lab.group_id | > >> > 1 > >> > | Using where | > >> > | 1 | SIMPLE | researcher | eq_ref | > >> > PRIMARY,user_id__idx | PRIMARY | 4 | > >> > labsyn.researcher_lab_permission.researcher_id | 1 | > >> > | > >> > | 1 | SIMPLE | auth_user | eq_ref | PRIMARY > >> > | PRIMARY | 4 | labsyn.researcher.user_id | > >> > 1 > >> > | Using where | > >> > > >> > > +----+-------------+---------------------------+--------+--------------------------------+---------+---------+------------------------------------------------+------+--------------------------------+ > >> > > >> > > >> > Here is with JOIN: > >> > > >> > explain SELECT l.id,g.name,c.name FROM researcher_lab_permission as > rl > >> > JOIN > >> > lab as l > >> > -> JOIN researcher as r JOIN auth_user as a JOIN > groups > >> > as g > >> > JOIN city as c > >> > -> ON rl.researcher_id = r.id AND rl.lab_id = l.idAND > >> > a.id > >> > = r.user_id AND l.group_id = g.id > >> > -> AND c.id = g.city_id > >> > -> ; > >> > > >> > > +----+-------------+-------+--------+--------------------------------+-----------------+---------+-------------------------+------+--------------------------------+ > >> > | id | select_type | table | type | possible_keys | > >> > key > >> > | key_len | ref | rows | Extra > >> > | > >> > > >> > > +----+-------------+-------+--------+--------------------------------+-----------------+---------+-------------------------+------+--------------------------------+ > >> > | 1 | SIMPLE | l | index | PRIMARY,group_id__idx | > >> > group_id__idx | 5 | NULL | 2 | Using > index > >> > | > >> > | 1 | SIMPLE | rl | ALL | researcher_id__idx,lab_id__idx | > >> > NULL > >> > | NULL | NULL | 2 | Using where; Using join > >> > buffer > >> > | > >> > | 1 | SIMPLE | a | index | PRIMARY | > >> > created_by__idx | 5 | NULL | 2 | Using > >> > index; > >> > Using join buffer | > >> > | 1 | SIMPLE | r | eq_ref | PRIMARY,user_id__idx | > >> > PRIMARY | 4 | labsyn.rl.researcher_id | 1 | Using > where > >> > | > >> > | 1 | SIMPLE | g | eq_ref | PRIMARY,city_id__idx | > >> > PRIMARY | 4 | labsyn.l.group_id | 1 | > >> > | > >> > | 1 | SIMPLE | c | eq_ref | PRIMARY | > >> > PRIMARY | 4 | labsyn.g.city_id | 1 | > >> > | > >> > > >> > > +----+-------------+-------+--------+--------------------------------+-----------------+---------+-------------------------+------+--------------------------------+ > >> > > >> > Without natural join it's getting all the cities first without any > >> > optimizations. So I observed that that code was not filtering cities. > >> > > >> > Now works great: > >> > rows = db(db.groups.id == db.lab.group_id)(db.groups.city_id == > >> > db.city.id)(db.lab.id == > >> > db.researcher_lab_permission.lab_id)(db.researcher.id == > >> > db.researcher_lab_permission.researcher_id)(db.researcher.user_id == > >> > db.auth_user.id).select(db.groups.name,db.city.name) > >> > > >> > Thank you guys! > >> > > >> > > >> > > >> > 2013/10/30 Michele Comitini <michele.comit...@gmail.com> > >> >> > >> >> implicit inner join vs explicit should be same in speed terms, but... > >> >> > >> >> http://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where > >> >> > >> >> > >> >> > >> >> > >> >> 2013/10/30 Diogo Munaro <diogo.mvie...@gmail.com> > >> >>> > >> >>> I really need these joins to filter tables instead of join all and > >> >>> then > >> >>> make a filter with WHERE (spend a long time). > >> >>> > >> >>> I only need these 2 fields. > >> >>> > >> >>> My query works great with db.executesql but I'm not working with dal > >> >>> optimizations, like table record versioning, and need to do some > where > >> >>> statment by myself (is_active). > >> >>> > >> >>> It's a DAL bug? I'm using web2py 2.7.2 > >> >>> > >> >>> > >> >>> 2013/10/30 Massimo Di Pierro <massimo.dipie...@gmail.com> > >> >>>> > >> >>>> What fields do you need to select. We can optimize this. > >> >>>> > >> >>>> > >> >>>> On Wednesday, 30 October 2013 05:17:09 UTC-5, Diogo Munaro wrote: > >> >>>>> > >> >>>>> I'ts working, but it's results a WHERE JOIN and takes much more > time > >> >>>>> than JOIN sintax :( > >> >>>>> > >> >>>>> > >> >>>>> 2013/10/29 Massimo Di Pierro <massimo....@gmail.com> > >> >>>>>> > >> >>>>>> Try this: > >> >>>>>> > >> >>>>>> rows = db(db.groups.id == db.lab.group_id)(db.lab.id == > >> >>>>>> db.researcher_lab_permission.lab_id)(db.researcher.id == > >> >>>>>> db.researcher_lab_permission.researcher_id)(db.researcher.user_id > >> >>>>>> == > >> >>>>>> > >> >>>>>> db.auth_user.id).select(db.groups.name,db.city.name > ,db.auth_user.ALL) > >> >>>>>> > >> >>>>>> > >> >>>>>> On Monday, 28 October 2013 21:46:28 UTC-5, Diogo Munaro wrote: > >> >>>>>>> > >> >>>>>>> Here is the sql generated: > >> >>>>>>> > >> >>>>>>> SELECT groups.name, city.name, auth_user.email, name1.id, > >> >>>>>>> name1.is_active, name1.created_on, name1.created_by, > >> >>>>>>> name1.modified_on, > >> >>>>>>> name1.modified_by, name1.user_id, name1.image, name1.image_file, > >> >>>>>>> name1.lattes, name2.id, name2.is_active, name2.created_on, > >> >>>>>>> name2.created_by, > >> >>>>>>> name2.modified_on, name2.modified_by, name2.site, name2.url, > >> >>>>>>> name2.cnpj, > >> >>>>>>> name2.type_id, name2.group_id, name2.privacity FROM > >> >>>>>>> researcher_lab_permission, researcher, lab JOIN groups ON > >> >>>>>>> ((groups.id = > >> >>>>>>> lab.group_id) AND (groups.is_active = 'T')) JOIN city ON ( > city.id > >> >>>>>>> = > >> >>>>>>> groups.city_id) JOIN auth_user ON ((researcher.user_id = > >> >>>>>>> auth_user.id) AND > >> >>>>>>> (auth_user.is_active = 'T')) JOIN `researcher` AS name1 ON > >> >>>>>>> (researcher.id = > >> >>>>>>> researcher_lab_permission.researcher_id) JOIN `lab` AS name2 ON > >> >>>>>>> ((lab.id = > >> >>>>>>> researcher_lab_permission.lab_id) AND (lab.is_active = 'T')) > >> >>>>>>> > >> >>>>>>> Some JOINS are wrong > >> >>>>>>> > >> >>>>>>> > >> >>>>>>> 2013/10/29 Diogo Munaro <diogo....@gmail.com> > >> >>>>>>>> > >> >>>>>>>> I need these joins because I need filter some tables without > >> >>>>>>>> selecting all the tables and filtering with where. Anyway, I > >> >>>>>>>> tried: > >> >>>>>>>> > >> >>>>>>>> rows = (db.groups.id == db.lab.group_id)(db.lab.id == > >> >>>>>>>> db.researcher_lab_permission.lab_id)(db.researcher.id == > >> >>>>>>>> > db.researcher_lab_permission.researcher_id)(db.researcher.user_id > >> >>>>>>>> == > >> >>>>>>>> > >> >>>>>>>> db.auth_user.id).select(db.groups.name,db.city.name > ,db.auth_user.ALL) > >> >>>>>>>> > >> >>>>>>>> And it returns: > >> >>>>>>>> > >> >>>>>>>> <type 'exceptions.TypeError'> 'Query' object is not callable > >> >>>>>>>> > >> >>>>>>>> I'm using db.executesql and it's working: > >> >>>>>>>> > >> >>>>>>>> db.executesql('''SELECT l.id,g.name,c.name FROM > >> >>>>>>>> researcher_lab_permission as rl JOIN lab as l > >> >>>>>>>> JOIN researcher as r JOIN auth_user as a JOIN > >> >>>>>>>> groups > >> >>>>>>>> as g JOIN city as c > >> >>>>>>>> ON rl.researcher_id = r.id AND rl.lab_id = > l.id > >> >>>>>>>> AND > >> >>>>>>>> a.id = r.user_id AND l.group_id = g.id > >> >>>>>>>> AND c.id = g.city_id WHERE a.id = %s''' > >> >>>>>>>> %(auth.user_id)) > >> >>>>>>>> > >> >>>>>>>> Something strange with DAL... > >> >>>>>>>> > >> >>>>>>>> 2013/10/28 Massimo Di Pierro <massimo....@gmail.com> > >> >>>>>>>>> > >> >>>>>>>>> (db.groups.id == db.lab.group_id)(db.lab.id == > >> >>>>>>>>> db.researcher_lab_permission.lab_id)(db.researcher.id == > >> >>>>>>>>> > >> >>>>>>>>> > db.researcher_lab_permission.researcher_id)(db.researcher.user_id == > >> >>>>>>>>> > >> >>>>>>>>> db.auth_user.id).select(db.groups.name,db.city.name > ,db.auth_user.ALL) > >> >>>>>>>> > >> >>>>>>>> > >> >>>>>>>> > >> >>>>>>> > >> >>>>>> -- > >> >>>>>> 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 a topic > in > >> >>>>>> the > >> >>>>>> Google Groups "web2py-users" group. > >> >>>>>> To unsubscribe from this topic, visit > >> >>>>>> https://groups.google.com/d/topic/web2py/0YdtJwCEdl4/unsubscribe > . > >> >>>>>> To unsubscribe from this group and all its topics, send an email > to > >> >>>>>> web2py+un...@googlegroups.com. > >> >>>>>> > >> >>>>>> For more options, visit https://groups.google.com/groups/opt_out > . > >> >>>>> > >> >>>>> > >> >>>> -- > >> >>>> 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 a topic in > >> >>>> the > >> >>>> Google Groups "web2py-users" group. > >> >>>> To unsubscribe from this topic, visit > >> >>>> https://groups.google.com/d/topic/web2py/0YdtJwCEdl4/unsubscribe. > >> >>>> To unsubscribe from this group and all its topics, send an email to > >> >>>> web2py+unsubscr...@googlegroups.com. > >> >>>> > >> >>>> For more options, visit https://groups.google.com/groups/opt_out. > >> >>> > >> >>> > >> >>> -- > >> >>> 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/groups/opt_out. > >> >> > >> >> > >> >> -- > >> >> 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 a topic in > the > >> >> Google Groups "web2py-users" group. > >> >> To unsubscribe from this topic, visit > >> >> https://groups.google.com/d/topic/web2py/0YdtJwCEdl4/unsubscribe. > >> >> To unsubscribe from this group and all its topics, send an email to > >> >> web2py+unsubscr...@googlegroups.com. > >> >> For more options, visit https://groups.google.com/groups/opt_out. > >> > > >> > > >> > -- > >> > 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/groups/opt_out. > >> > >> -- > >> 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 a topic in the > >> Google Groups "web2py-users" group. > >> To unsubscribe from this topic, visit > >> https://groups.google.com/d/topic/web2py/0YdtJwCEdl4/unsubscribe. > >> To unsubscribe from this group and all its topics, send an email to > >> web2py+unsubscr...@googlegroups.com. > >> For more options, visit https://groups.google.com/groups/opt_out. > > > > > > -- > > 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/groups/opt_out. > > -- > 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 a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/web2py/0YdtJwCEdl4/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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/groups/opt_out.
