Re: [web2py] Re: Join might work? Table limits?
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.id AND 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
Re: [web2py] Re: Join might work? Table limits?
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
Re: [web2py] Re: Join might work? Table limits?
Are you sure you do not want left joins? Except for this query (db.city.id == db.groups.city_id) everything else seems un-necessary to me and perhaps should be a left join. Perhaps that's what is confusing the DB too. On Thursday, 31 October 2013 07:50:47 UTC-5, Diogo Munaro wrote: Yes, web2py is not ok with it. Making 3 or 4 explicit joins and it gets errors 2013/10/31 Vinicius Assef vinic...@gmail.com javascript: 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@gmail.comjavascript: 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 vinic...@gmail.com javascript: 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@gmail.comjavascript: 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 |
Re: [web2py] Re: Join might work? Table limits?
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.dipie...@gmail.com
Try this:
rows = db(db.groups.id == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.l**ab_id)(db.researcher.id ==
db.researcher_lab_permission.r**esearcher_id)(db.researcher.us**er_id ==
db.auth_user.id).select(db.**groups.name http://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.l**ab_id)(db.researcher.id ==
db.researcher_lab_permission.r**esearcher_id)(db.researcher.us**er_id ==
db.auth_user.id).select(db.**groups.name http://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 http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**l**ab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**r**esearcher_id)(db.researcher.**us*
*er_id == db.auth_user.id).select(db.**groups.namehttp://db.groups.name/
,db.**city.name http://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+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.
Re: [web2py] Re: Join might work? Table limits?
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 javascript:
Try this:
rows = db(db.groups.id == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.l**ab_id)(db.researcher.id ==
db.researcher_lab_permission.r**esearcher_id)(db.researcher.us**er_id ==
db.auth_user.id).select(db.**groups.name http://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.l**ab_id)(db.researcher.id ==
db.researcher_lab_permission.r**esearcher_id)(db.researcher.us**er_id
== db.auth_user.id).select(db.**groups.name http://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 http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**l**ab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**r**esearcher_id)(db.researcher.**us
**er_id ==
db.auth_user.id).select(db.**groups.namehttp://db.groups.name/
,db.**city.name http://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 javascript:.
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.
Re: [web2py] Re: Join might work? Table limits?
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 http://db.groups.name/,
db.city.name,db.**au**th_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.**rese**archer_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 http://db.groups.name/
,db.city.name,db.**au**th_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 http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**lab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**researcher_id)(db.researcher.*
*user_id ==
db.auth_user.id).select(db.groups.namehttp://db.groups.name/
,db.**city.name http://db.city.name/,db.**au**th_user.ALL)
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/**web2py
http://github.com/web2py/web2py(Source code)
-
https://code.google.com/p/**web2py/issues/listhttps://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/**unsubscribehttps://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_outhttps://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
Re: [web2py] Re: Join might work? Table limits?
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 http://db.groups.name/,
db.city.name,db.**au**th_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.**rese**archer_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.namehttp://db.groups.name/
,db.city.name,db.**au**th_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 http://db.groups.id/ == db.lab.group_id)(db.lab.id
==
db.researcher_lab_permission.**lab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**researcher_id)(db.researcher.
**user_id ==
db.auth_user.id).select(db.groups.namehttp://db.groups.name/
,db.**city.name http://db.city.name/,db.**au**th_user.ALL)
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/**web2py
http://github.com/web2py/web2py(Source code)
-
https://code.google.com/p/**web2py/issues/listhttps://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/**unsubscribehttps://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_outhttps://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.
--
Re: [web2py] Re: Join might work? Table limits?
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.id AND 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
Re: [web2py] Re: Join might work? Table limits?
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.id AND 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
Re: [web2py] Re: Join might work? Table limits?
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.id AND 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 |
Re: [web2py] Re: Join might work? Table limits?
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 javascript:
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 javascript:
(db.groups.**id http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**lab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**researcher_id)(db.researcher.**user_id
==
db.auth_user.id).select(db.groups.name,db.**city.namehttp://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 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.
[web2py] Re: Join might work? Table limits?
No but if you join the same table multiple times
(db.researcher_lab_permission) you have to do so using an alias. This
should work.
groups = db().select(db.groups.name,db.city.name
,db.auth_user.email,db.researcher.with_alias('name1').ALL, db.lab.
with_alias('name2').ALL,
join=[db.groups.on(db.groups.id ==
db.lab.group_id),
db.city.on(db.city.id == db.groups.city_id),
db.auth_user.on(db.researcher.user_id ==
db.auth_user.id),
db.researcher.with_alias('name1').on(
db.researcher.id == db.researcher_lab_permission.researcher_id),
db.lab.with_alias('name2').on(db.lab.id ==
db.researcher_lab_permission.lab_id)
])
You would have the same issue with raw SQL.
On Monday, 28 October 2013 15:29:02 UTC-5, Diogo Munaro wrote:
Hey guys, I'm using web2py with mysql and I can't do a Inner Join
When I try:
groups = db().select(db.groups.name,db.city.name,
join=[db.groups.on(db.groups.id ==
db.lab.group_id),
db.city.on(db.city.id ==
db.groups.city_id),
db.auth_user.on(db.researcher.user_id ==
db.auth_user.id),
db.researcher.on(db.researcher.id ==
db.researcher_lab_permission.researcher_id),
db.lab.on(db.lab.id ==
db.researcher_lab_permission.lab_id)
])
Raise this error:
class 'gluon.contrib.pymysql.err.InternalError' (1054, uUnknown column
'lab.group_id' in 'on clause')
But when I try only:
groups = db().select(db.groups.name,db.city.name,
join=[db.groups.on(db.groups.id ==
db.lab.group_id),
db.city.on(db.city.id == db.groups.city_id)
])
It's works. There are some limit for tables join?
--
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.
Re: [web2py] Re: Join might work? Table limits?
Yes, thanks Massimo! The first error pass, but now:
class 'gluon.contrib.pymysql.err.InternalError' (1054, uUnknown column
'researcher.user_id' in 'on clause')
How could I generate sql to debug it?
2013/10/28 Massimo Di Pierro massimo.dipie...@gmail.com
groups = db().select(db.groups.name,db.**city.name http://db.city.name/
,db.auth_user.email,db.researcher.with_alias('name1').ALL, db.lab.
with_alias('name2').ALL,
join=[db.groups.on(db.groups.**idhttp://db.groups.id/ ==
db.lab.group_id),
db.city.on(db.city.id ==
db.groups.city_id),
db.auth_user.on(db.researcher.**user_id ==
db.auth_user.id),
db.researcher.with_alias('name1').on(db.**
researcher.id http://db.researcher.id/ == db.researcher_lab_permission.*
*researcher_id),
db.lab.with_alias('name2').on(db.lab.id ==
db.researcher_lab_permission.**lab_id)
])
--
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.
Re: [web2py] Re: Join might work? Table limits?
I am reading this again. I misunderstood the model. This is not an outer
join. This is an inner join. There is no conflict.
Yet I do not understand. You only select db.groups.name and db.city.name.
So what you so seem equivalent to this.
groups = db(db.city.id == db.groups.city_id).select(db.groups.name,
db.city.name)
Why join all the other tables? Anyway, try this:
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)
On Monday, 28 October 2013 18:45:28 UTC-5, Diogo Munaro wrote:
Yes, thanks Massimo! The first error pass, but now:
class 'gluon.contrib.pymysql.err.InternalError' (1054, uUnknown column
'researcher.user_id' in 'on clause')
How could I generate sql to debug it?
2013/10/28 Massimo Di Pierro massimo@gmail.com javascript:
groups = db().select(db.groups.name,db.**city.name http://db.city.name/
,db.auth_user.email,db.researcher.with_alias('name1').ALL, db.lab.
with_alias('name2').ALL,
join=[db.groups.on(db.groups.**idhttp://db.groups.id/ ==
db.lab.group_id),
db.city.on(db.city.id ==
db.groups.city_id),
db.auth_user.on(db.researcher.**user_id
== db.auth_user.id),
db.researcher.with_alias('name1').on(db.**
researcher.id http://db.researcher.id/ == db.researcher_lab_permission.
**researcher_id),
db.lab.with_alias('name2').on(db.lab.id ==
db.researcher_lab_permission.**lab_id)
])
--
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.
Re: [web2py] Re: Join might work? Table limits?
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.dipie...@gmail.com
(db.groups.**id http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**lab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**researcher_id)(db.researcher.**user_id
== db.auth_user.id).select(db.groups.name,db.**city.namehttp://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 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.
Re: [web2py] Re: Join might work? Table limits?
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.mvie...@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.dipie...@gmail.com
(db.groups.**id http://db.groups.id/ == db.lab.group_id)(db.lab.id ==
db.researcher_lab_permission.**lab_id)(db.**researcher.idhttp://db.researcher.id/
== db.researcher_lab_permission.**researcher_id)(db.researcher.**user_id
==
db.auth_user.id).select(db.groups.name,db.**city.namehttp://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 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.
