SQL is incorrect.
is: "AND supplier_contacts.is_active = 'T'"
should be "AND (supplier_contacts.is_active = 'T' OR
supplier_contacts.is_active IS NULL)"
On Friday, December 21, 2012 1:05:16 PM UTC-5, Massimo Di Pierro wrote:
>
> It looks to be the generated SQL is correct. It is possible you enabled
> record versioning and that added the common_filter is_active==True. Yet
> perhaps you have records with is_active=None and therefore they showed up
> before and not now.
>
> On Friday, 21 December 2012 10:50:13 UTC-6, Cliff Kachinske wrote:
>>
>> 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'));
>>
>>
>>
>>
>>
--
