Join might work? Table limits?

[Diogo Munaro]

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, u"Unknown 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?

[Massimo Di Pierro]

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.

[Diogo Munaro]

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

<class 'gluon.contrib.pymysql.err.InternalError'> (1054, u"Unknown column 'researcher.user_id' in 'on clause'")

How could I generate sql to debug it?

2013/10/28 Massimo Di Pierro <massimo....@gmail.com>

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

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

<type 'exceptions.TypeError'> 'Query' object is not callable

I'm using db.executesql and it's working:

db.executesql('''SELECT l.id,g.name,c.name FROM researcher_lab_permission as rl JOIN lab as l
                JOIN researcher as r JOIN auth_user as a JOIN groups as g JOIN city as c
                ON rl.researcher_id = r.id AND rl.lab_id = l.id AND a.id = r.user_id AND l.group_id = g.id
                AND c.id = g.city_id WHERE a.id = %s''' %(auth.user_id))

Something strange with DAL...

2013/10/28 Massimo Di Pierro <massimo....@gmail.com>

(db.groups.id == db.lab.group_id)(db.lab.id == db.researcher_lab_permission.lab_id)(db.researcher.id == db.researcher_lab_permission.researcher_id)(db.researcher.user_id == db.auth_user.id).select(db.groups.name,db.city.name,db.auth_user.ALL)

[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 <diogo....@gmail.com>

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

[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 <massimo....@gmail.com>

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

[Massimo Di Pierro]

What fields do you need to select. We can optimize this.

[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 <massimo....@gmail.com>

[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 <diogo....@gmail.com>

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+un...@googlegroups.com.

[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 join it's getting all the cities first without any optimizations. So I observed that that code was not filtering cities.

Now works great:
rows = db(db.groups.id == db.lab.group_id)(db.groups.city_id == db.city.id)(db.lab.id == db.researcher_lab_permission.lab_id)(db.researcher.id == db.researcher_lab_permission.researcher_id)(db.researcher.user_id == db.auth_user.id).select(db.groups.name,db.city.name)

Thank you guys!

2013/10/30 Michele Comitini <michele....@gmail.com>

[Vinicius Assef]

Maybe I missed something, but why the simple query (with few joins)
worked and the complex one (with many joins) didn't?

[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 <vinic...@gmail.com>

[Vinicius Assef]

That was my point, Diogo.

Is there some fault when we have many explicit joins in DAL?

[Diogo Munaro]

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>

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

[Diogo Munaro]

Now I'm ok with wheres, I'm just replying Vinicius question.

Thx Massimo

2013/10/31 Massimo Di Pierro <massimo....@gmail.com>