0

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

ChrisGuest
  • 3,398
  • 4
  • 32
  • 53

2 Answers2

1

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

Diogo Martins
  • 917
  • 7
  • 15
1
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.

Anthony
  • 25,466
  • 3
  • 28
  • 57