CODEWITHSUNDEEP
python-2.7web2py
ChrisGuest
ChrisGuest

Reputation: 3608

What is the correct way to build complex queries in web2py's DAL?

I am using web2py's DAL and trying to do the equivalent of this MySQL query.

SELECT header.tag, detail.site
FROM header 
    LEFT OUTER JOIN detail ON header.id= detail.header
WHERE header.tag IN ('abc', 'def', 'xyz')

I presumed that the DAL equivalent was as follows, but this code is giving me a cross product.

tags = ['abc', 'def', 'xyz']
query = (db.header.tag.belongs(tags) & db.header.id==db.detail.header)
raw_data = db(query).select(
    db.header.tag,
    db.detail.site,
)

SELECT header.tag, detail.site FROM header, detail;

If I omit the belongs clause from my query, all works fine.

query = (db.header.id==db.detail.header)
raw_data = db(query).select(
    db.header.tag,
    db.detail.site,
)

SELECT header.tag, detail.site FROM header, detail WHERE (header.id = detail.header);

I am using this version: 2.12.3-stable+timestamp.2015.08.18.19.14.07

Upvotes: 0

Views: 99

Answers (2)

Anthony
Anthony

Reputation: 25536

query = db.header.tag.belongs(['abc', 'def', 'xyz'])
db(query).select(db.header.tag, db.detail.site,
                 left=db.detail.on(db.header.id == db.detail.header))

For me, see http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Left-outer-join.

Upvotes: 1

Diogo Martins
Diogo Martins

Reputation: 937

You're missing the left parameter on your select method. Check the documentation for Left Outer Join here: http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Left-outer-join

Upvotes: 1

Related Questions