Re: [web2py] Re: Join might work? Table limits?

[Massimo Di Pierro]

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 >
>
>> 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 
>> > 
>> 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 >
>> >>
>> >> 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 
>> >> 
>> >
>> >> 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
>> >> > -> ;
>> >> >
>> >> > 
>> ++-+---+++-+-+-+--+--

Re: [web2py] Re: Join might work? Table limits?

[Diogo Munaro]

Yes, web2py is not ok with it. Making 3 or 4 explicit joins and it gets
errors


2013/10/31 Vinicius Assef 

> 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 
> 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 
> >>
> >> 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 
> >> 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   |

Re: [web2py] Re: Join might work? Table limits?

[Vinicius Assef]

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  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 
>>
>> 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 
>> 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__id

Re: [web2py] Re: Join might work? Table limits?

[Diogo Munaro]

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 

> 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 
> 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 | PRIMAR

Re: [web2py] Re: Join might work? Table limits?

[Vinicius Assef]

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  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 |
> |
> ++-+---+++-+-+-+--++
>
> Wit

Re: [web2py] Re: Join might work? Table limits?

[Diogo Munaro]

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

Re: [web2py] Re: Join might work? Table limits?

[Michele Comitini]

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 

> 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 
>
>> 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 
>>>
 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.**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 
>
>> 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.**au**th_user.ALL)
>>
>> And it returns:
>>  '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 
>>
>>> (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.**au**th_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 Issu

Re: [web2py] Re: Join might work? Table limits?

[Diogo Munaro]

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 

> 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 
>>
>>> 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.**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 

> 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.**au**th_user.ALL)
>
> And it returns:
>  '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 
>
>> (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.**au**th_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+u

Re: [web2py] Re: Join might work? Table limits?

[Massimo Di Pierro]

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 >
>
>> 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 ,
>> 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 
>>>
 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 ,
 db.city.name,db.**auth_user.ALL)

 And it returns:
  '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 

> (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
> ,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 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?

[Diogo Munaro]

I'ts working, but it's results a WHERE JOIN and takes much more time than
JOIN sintax :(


2013/10/29 Massimo Di Pierro 

> 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 ,
> 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 
>>
>>> 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 ,
>>> db.city.name,db.**auth_user.ALL)
>>>
>>> And it returns:
>>>  '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 
>>>
 (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
 ,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?

[Massimo Di Pierro]

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 >
>
>> 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:
>>  '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 >
>>
>>> (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 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?

[Diogo Munaro]

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 

> 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:
>  '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 
>
>> (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 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?

[Diogo Munaro]

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:
 '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 

> (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 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?

[Massimo Di Pierro]

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:
>
>  (1054, u"Unknown column 
> 'researcher.user_id' in 'on clause'") 
>
> How could I generate sql to debug it?
>
>
> 2013/10/28 Massimo Di Pierro >
>
>>
>> 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)
>>  ])
>>
>
>
>

-- 
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?

[Diogo Munaro]

Yes, thanks Massimo! The first error pass, but now:

 (1054, u"Unknown column
'researcher.user_id' in 'on clause'")

How could I generate sql to debug it?


2013/10/28 Massimo Di Pierro 

>
> 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)
>  ])
>

-- 
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.