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'));




-- 



Reply via email to