> In my database I have 5000 customers who made purchases and made > some form of payment. I need to find the names of all customers who > made payments by cash. > > My SQL query looks like this: > > SELECT customers.name, payments.payid > FROM customers, purchases, payments > WHERE customers.cid = purchases.cid > AND purchases.payid = payments.payid > AND payments.pay_type = 'cash'
have a look at 'querying with joins' in the docs. Also, 0.4 docs are better explaning some things than 0.3 (but check for feature availability). non-orm: q = customers_table.join( purchases_table).join( payments_table).select( payments_table.c.pay_type=='cash').execute() for a in q: print a u have something like this anyway. orm: q = Customer.query().add_entity( Payment).join( ['purchases', 'payments'] ).filter( Payment.pay_type=='cash') for a in q: print a here u'll get tuples (objCustomer,objPayment) in both cases, for further optimization, u could (somehow?) preselect the columns u need instead of getting all. Especialy for the orm/Customers, u'll get whole purchases[] relations loaded unless they are lazy. u can also make a mapper directly over the table-join-select: class CusPay: pass m= Mapper( CusPay, customers_table.join( purchases_table).join( payments_table).select( payments_table.c.pay_type=='cash') ) for a in m.query(): print a or something of sorts, avoiding any extra columns/relation/whatever. > Currently my code looks like this: > for customer in customers: > for purchase in customer.purchases: > if purchase.payment.pay_type == 'cash': > print "%s, %s" % (customer.name, > purchase.payment.payid) u are not using any db-filtering, u are filtering all by hand in python. so u're instantiating all them customers, then for each loading all the purchases, then printing those =='cash' - essentialy loading whole db. ciao svilen --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
