Re: [SQLObject] How do I do a LEFT JOIN

[Ian B. <ia...@co...>]

On Mon, 2003-07-07 at 17:22, Mark Melvin wrote:
> I am by no means a SQL master, but do you think it will even come close?  I 
> mean, if you compare the following SQL statement:
> 
>                 "SELECT customer.company, contacts.last_name, 
> contacts.first_name, " \
>                 "customer.phone, customer.street, customer.city, 
> customer.province, " \
>                 "customer.pcode, customer.custid " \
>                 "FROM customer LEFT JOIN contacts ON 
> customer.custid=contacts.custid " \
>                 "ORDER BY customer.company, contacts.last_name, 
> contacts.first_name")

It's not a question about whether it will be the same speed -- it's a
question about whether it will be fast enough for what you are doing. 
If the database isn't a bottleneck, then performance is fine, even if it
is somewhat slower than it might have been.  And there's several parts
of the query as well, not all of which are faster with the left join. 
For instance, you have to reassemble the results into a set of customers
with contacts, since you'll get duplicate customer information back.

If you really wanted to do it quickly, you might be able to do something
like:

customers = Customer.select()
contacts = {}
for contact in Contact.select():
    contacts.setdefault(contact.customerID, []).append(contact)

Then it's two queries instead of one, and you can't use the accessor for
your customers (you have to use contacts[customer.id]), but performance
should be similar.  You can even select portions of the database if you
want, with something like:

query = Customer.q.firstName == 'John'
customers = Customer.select(query)
for contact in Contact.select(AND(Contact.q.customerID = Customer.q.id, query)):
    ...

Hmmm... this technique just occurred to me.  I'll copy it to the mailing
list for other's reference.  I think I need to start a FAQ too.

  Ian