Salient details from two tables:
db.define_table(
'suppliers',
Field('name', length=256, required=True, notnull=True),
....
db.define_table(
'supplier_contacts',
Field('supplier_id', db.suppliers),
Field('first_name', length=32, required=True, notnull=True),
The code below worked in 1.99.7. If the supplier had two contacts, it
would return two rows as expected, both with the same supplier data but
each with individual contact data.
In 2.2.1 it returns no rows.
def get_approved_suppliers(product_id):
return db(
(db.product_suppliers.product_id==product_id) &
(db.product_suppliers.supplier_id==db.suppliers.id)
).select(
db.suppliers.id,
db.suppliers.name,
# more supplier details omitted for brevity,
db.supplier_contacts.id,
db.supplier_contacts.first_name,
# contact details omitted for brevity.
left = db.supplier_contacts.on(
db.supplier_contacts.supplier_id==db.suppliers.id
)
)
This is the query as shown by db._lastsql. (Broken into chunks for
readability)
SELECT suppliers.id, suppliers.name, suppliers.address, suppliers.address_2
, suppliers.city, suppliers.state, suppliers.zip, suppliers.land_line,suppliers
.fax, suppliers.email, suppliers.website, supplier_contacts.id,supplier_contacts
.first_name, supplier_contacts.middle_name,
supplier_contacts.last_name,supplier_contacts
.generation, supplier_contacts.email, supplier_contacts.mobile,supplier_contacts
.land_line, supplier_contacts.fax
FROM product_suppliers, suppliers
LEFT JOIN supplier_contacts ON (supplier_contacts.supplier_id = suppliers.id
)
WHERE (((((product_suppliers.product_id = 340) AND
(product_suppliers.supplier_id
= suppliers.id)) AND (product_suppliers.is_active = 'T')) AND
(suppliers.is_active
= 'T')) AND (supplier_contacts.is_active = 'T'));
--
