sqlobject-discuss

[SQLObject] How do I do a LEFT JOIN

[Mark M. <ma...@di...>]

Hi all,

I am super-excited about this package, but I can't seem to figure out how 
do execute a simple LEFT JOIN SQL query.  Am I missing somehting basic 
here?  I have my two tables set up, but I can't issue the SQL directly with 
the .select() method because it throws that pesky WHERE in at the end.  
Here is what I am getting:

class Customer(SQLObject):
    _table = "customer"
    _idName = "custid"
    Company     = StringCol(length=30, dbName='company', default="")
    Street      = StringCol(length=35, dbName='street', default="")
    City        = StringCol(length=20, dbName='city', default="")
    Province    = StringCol(length=15, dbName='province', default="")
    PostalCode  = StringCol(length=7, dbName='pcode', default="")
    Contacts    = MultipleJoin('Contact', joinColumn='custid', 
orderBy=['LastName', '-Title', 'FirstName'])

class Contact(SQLObject):
    _table = "contacts"
    _idName = "contactid"
    LastName    = StringCol(length=15, dbName='last_name', default="")
    FirstName   = StringCol(length=15, dbName='first_name', default="")
    Title       = StringCol(length=25, dbName='title', default="")
    Customer    = ForeignKey('Customer', dbName='custid', default=0)

if __name__ == '__main__':
    all = Customer.select("""JOIN ON Contacts USING (custid)""")

Gives me:

Select:  SELECT customer.custid, customer.province, customer.city, 
customer.stre
et, customer.pcode, customer.company FROM customer WHERE JOIN ON Contacts 
USING
(custid)

Can anyone point me in the right direction?  I need the LEFT JOIN behaviour 
here, not a simple equi-join, as there may be Customers defined, but no 
Contacts.

-- 
Thanks a lot,
Mark.

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

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

On Mon, 2003-07-07 at 15:27, Mark Melvin wrote:
> Hi all,
> 
> I am super-excited about this package, but I can't seem to figure out how 
> do execute a simple LEFT JOIN SQL query.  Am I missing somehting basic 
> here?  I have my two tables set up, but I can't issue the SQL directly with 
> the .select() method because it throws that pesky WHERE in at the end.  
> Here is what I am getting:

I'm glad you are excited about it.  You can't really do a left join, but
you can get the same thing iteratively, like:

for customer in Customer.select():
    contacts = customer.Contacts

You can't fetch the customers and the contacts together -- SQLObject
only knows how to select instances of one class at a time (this is made
up for by the fact it's easy to get related instances, as in
"customer.Contacts").

  Ian

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

[Mark M. <ma...@di...>]

On 07 Jul 2003 15:59:57 -0500, Ian Bicking <ia...@co...> wrote:

> On Mon, 2003-07-07 at 15:27, Mark Melvin wrote:
>> Hi all,
>>
>> I am super-excited about this package, but I can't seem to figure out 
>> how do execute a simple LEFT JOIN SQL query.  Am I missing somehting 
>> basic here?  I have my two tables set up, but I can't issue the SQL 
>> directly with the .select() method because it throws that pesky WHERE in 
>> at the end.  Here is what I am getting:
>
> I'm glad you are excited about it.  You can't really do a left join, but
> you can get the same thing iteratively, like:
>
> for customer in Customer.select():
> contacts = customer.Contacts

Thanks for the reply.  This seems to connect and do a single SQL query for 
each item in customer.Contacts.  I assume this will be incredibly slow for 
large record sets?  Hmmm...seeing as most of my data is going to be fetched 
this way (the out of the ordinary case being a customer with no Contacts) 
this may not be doable for me.  Darn.  I spent all day looking at 
implementing this...it is so much cleaner than coding SQL queries into my 
GUI.

-- 
Mark.

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

[SQLObject] Change to Converters.py breaks selects

[Edmund L. <el...@in...>]

The changes to Converter.py to refactor it has broken operations on the 
results of select operations involving queries...

Example:

 >>> p = Person.select(Person.q.id == 1)
 >>> p
<SQLObject.SQLObject.SelectResults object at 0x816960c>
 >>> len(p)
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "SQLObject/SQLObject.py", line 1084, in __len__
     count = conn.countSelect(self)
   File "SQLObject/DBConnection.py", line 149, in countSelect
     q = "SELECT COUNT(*) FROM %s WHERE %s" % \
   File "SQLObject/DBConnection.py", line 175, in whereClauseForSelect
     q = str(select.clause)
   File "SQLObject/SQLBuilder.py", line 157, in __str__
     return self.sqlRepr()
   File "SQLObject/SQLBuilder.py", line 219, in sqlRepr
     return "(%s %s %s)" % (sqlRepr(self.expr1), self.op, 
sqlRepr(self.expr2))
   File "SQLObject/Converters.py", line 99, in sqlRepr
     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <type 'instance'> for co_person.id