DAL and JOINS error

[Diogo Munaro]

Hi guy, I'm using web2py version 2.7.2 with ubuntu 12.04.

I made a simple db.py:

state = db.define_table('state',
    Field('name','string',label=T('State')))

city = db.define_table('city',
    Field('name','string',label=T('City')),
    Field('state_id','reference state',required=True,
          requires=IS_IN_DB(db,'state.id', lambda reg: '%(city)s - %(state)s' % dict(city=reg.name,state=reg.country_id.name))))

When I make a query:

db(db.city.name.contains('rio')).select(db.city.name,db.city.id,db.state.name,join=db.city.on(db.city.state_id == db.state.id))

it takes 180ms, but when i make

db(db.city.name.contains('rio') & db.city.state_id == db.state.id).select(db.city.name,db.city.id,db.state.name)

it takes 24 seconds waiting and 5 seconds to retrieve.

Looking inside, I saw that web2py is making a WHERE clause instead of a JOIN.

It's normal?

[Anthony]

db(db.city.name.contains('rio') & db.city.state_id == db.state.id).select(db.city.name,db.city.id,db.state.name)

db(db.city.name.contains('rio') & db.city.state_id == db.state.id)

should be:

db(db.city.name.contains('rio') & (db.city.state_id == db.state.id))

In python, the & takes precedence over the == (see http://docs.python.org/2/reference/expressions.html#operator-precedence), so you need to put the second part of the query in parentheses.

 

Looking inside, I saw that web2py is making a WHERE clause instead of a JOIN.

It's normal?

Yes, if you do an implicit join via the query, web2py generates a WHERE clause, not an explicit JOIN statement.

Anthony

[Diogo Munaro]

But the book is wrong?
Here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=join#Inner-joins

[Anthony]

On Tuesday, October 22, 2013 3:22:09 PM UTC-4, Diogo Munaro wrote:

But the book is wrong?
Here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=join#Inner-joins

What do you see wrong in the book? Note, using the WHERE clause still does an inner join, it just doesn't do it via a JOIN clause. The book even clarifies this (see highlighted text below):

There is an alternative syntax for INNER JOINS:

>>> rows = db(db.person).select(join=db.thing.on(db.person.id==db.thing.owner_id))
>>> for row in rows:
    print row.person.name, 'has', row.thing.name
Alex has Boat
Alex has Chair
Bob has Shoes

While the output is the same, the generated SQL in the two cases can be different.

Anthony

[Diogo Munaro]

Ohhhh...Ok, thanks Anthony!

2013/10/22 Anthony <abas...@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/EW_WhDeP9Uo/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.