Re: [SQLObject] Reducing queries

[David M. C. <da...@da...>]

On Tue, May 03, 2005 at 04:49:25PM -0700, Ben Bangert wrote:

> Is there anything currently in SQLObject, or in the works, that will pull
> and cache more of this data in advance, and hopefully turn it into one
> query? While I'm sure a lot of us have heard enough about Rails to make one

Didn't Michel or Oleg or somebody say they'd implemented OUTERJOIN in CVS?
I tried LEFTJOINOn, but that's broken.

The usual way is described in the FAQ:

http://www.sqlobject.org/docs/FAQ.html#how-can-i-do-a-left-join

Or you can do one SQL query and retrieve the objects by ID with .get() (not
tested and not benchmarked against the other methods):

result = Contact._connection.queryAll("""SELECT cs.id, cn.id FROM customer cs
                                     LEFT JOIN contact cn 
				     ON (cs.id=cn.customer_id)
				     WHERE cs.first_name LIKE 'J%'""")
customers = []				     
custContacts = {}
for customer_id, contact_id in result:
    if customer_id not in custContacts:
       customer = Customer.get(customer_id)
       customers.append(customer)
    if contact_id is not None:
        contact = Contact.get(contact_id)
    	custContacts.setdefault(customer_id, []).append(contact)
    else:
        custContacts[customer_id] = []
for customer in customers:
  print customer
  for contact in custContacts[customer.id]:
    print "  ", contact
    
Dave Cook