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

-- 



Reply via email to