Thread: [SQLObject] I am confused by the example in FAQ
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <pet...@tp...> - 2007-11-28 22:15:32
|
Hi, I am trying to follow up the first example in: http://www.sqlobject.org/FAQ.html#how-can-i-do-a-left-join it should return same results as the following SQL select: SELECT customer.id, customer.first_name, customer.last_name, contact.id, contact.address FROM customer LEFT JOIN contact ON contact.customer_id = customer.id *Example given on the web is:* for customer in Customer.select(): print customer.firstName, customer.lastName for contact in customer.contacts: print ' ', contact.phoneNumber but my feeling is it should be something like: for customer in Customer.select(): print customer.firstName, customer.lastName for contact in Contact.select(Contact.q.customer.id==customer.id): print ' ', sale.aged Please let me know, how the given example works or what I am overlooking (missing). Regards Petr Jakes |
From: Oleg B. <ph...@ph...> - 2007-11-28 22:30:52
|
On Wed, Nov 28, 2007 at 11:15:29PM +0100, Petr Jake?? wrote: > *Example given on the web is:* > for contact in customer.contacts: > print ' ', contact.phoneNumber > > but my feeling is it should be something like: > > for contact in Contact.select(Contact.q.customer.id==customer.id): > print ' ', sale.aged MultipleJoin or RelatedJoin do this automagically, so you don't need to. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <pet...@tp...> - 2007-11-28 22:35:04
|
On Nov 28, 2007 11:30 PM, Oleg Broytmann wrote: > On Wed, Nov 28, 2007 at 11:15:29PM +0100, Petr Jake?? wrote: > > *Example given on the web is:* > > for contact in customer.contacts: > > print ' ', contact.phoneNumber > > > > but my feeling is it should be something like: > > > > for contact in Contact.select(Contact.q.customer.id==customer.id): > > print ' ', sale.aged > > MultipleJoin or RelatedJoin do this automagically, so you don't need to. But there is not MultipleJoin or RelatedJoin in the given example .... Petr > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > |
From: Oleg B. <ph...@ph...> - 2007-11-28 22:40:26
|
On Wed, Nov 28, 2007 at 11:34:49PM +0100, Petr Jake?? wrote: > But there is not MultipleJoin or RelatedJoin in the given example .... But there are no descriptions for Customer and Contact tables/classes, and still we dare to call Customer.select() (-; What do you think customer.contacts is? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <pet...@tp...> - 2007-11-28 23:18:06
|
On Nov 28, 2007 11:40 PM, Oleg Broytmann wrote: > On Wed, Nov 28, 2007 at 11:34:49PM +0100, Petr Jake?? wrote: > > But there is not MultipleJoin or RelatedJoin in the given example .... > > But there are no descriptions for Customer and Contact tables/classes, > and still we dare to call Customer.select() (-; > What do you think customer.contacts is? I see, I am getting closer. What I am really do is I am trying to query existing tables. The code I allready have looks: class Customer(SQLObject): class sqlmeta: fromDatabase = True idName = "CUST_NO" sales = MultipleJoin('Sales') class Sales(SQLObject): class sqlmeta: fromDatabase = True idName = "PO_NUMBER" idType= str custNo = ForeignKey('Customer') Customer.sqlmeta.addJoin(MultipleJoin('Sales', joinMethodName='custNo')) for customer in Customer.select(): print customer.contactFirst, customer.contactLast for contact in customer.sales: pass Select on the master (Customer) table is OK, but than the code tries: 1/QueryAll: SELECT PO_NUMBER FROM sales WHERE customer_id = (1001) 1/QueryR : SELECT PO_NUMBER FROM sales WHERE customer_id = (1001) which si a wrong SELECT, because the field customer_id does not exists (the name is generated by the code, even idName = "CUST_NO" is defined in the class Customer). How can I "force" SQLObject to use CUST_NO field name instead the customer_id which is "generated" by the code? Thanks. Petr Jakes > > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > |
From: <pet...@tp...> - 2007-11-29 00:24:52
|
Finaly I was succesful after some googling. Sorry I did not it before asking my question, but I was confused by the example mentioned in the FAQ. To provide feedback to others it was necessary to add the name of the column where the "join" is made. class Customer(SQLObject): class sqlmeta: fromDatabase = True idName = "CUST_NO" sales = MultipleJoin('Sales', joinColumn='CUST_NO') # this is what I have to add to my code class Sales(SQLObject): class sqlmeta: fromDatabase = True idName = "PO_NUMBER" idType= str custNo = ForeignKey('CUST_NO') thanks and best regards Petr Jakes |
From: <pet...@tp...> - 2007-11-29 06:44:53
|
My hair-pulling continues... How can I add more conditions in to the final SELECT. I mean I am able to get the table objetcs: class Customer(SQLObject): class sqlmeta: fromDatabase = True idName = "CUST_NO" sales = MultipleJoin('Sales', joinColumn='CUST_NO') class Sales(SQLObject): class sqlmeta: fromDatabase = True idName = "PO_NUMBER" idType= str and than: for cust in Customer.select(Customer.q.city.startswith('T')): ...... Which generates: SELECT ...(ale tables names here).... FROM customer WHERE ((customer.city) LIKE ('T%')) but how can I add more conditions in to the final WHERE clause? I was trying for example: for cust in Customer.select(Customer.q.city.startswith('T') or Customer.q.city.startswith('Q') ): But this does not work. Thanks and regards Petr Jakes |
From: Oleg B. <ph...@ph...> - 2007-11-29 12:27:42
|
On Thu, Nov 29, 2007 at 07:44:50AM +0100, Petr Jake?? wrote: > for cust in Customer.select(Customer.q.city.startswith('T')): > ...... > > Which generates: > SELECT ...(ale tables names here).... FROM customer WHERE ((customer.city) > LIKE ('T%')) > > but how can I add more conditions in to the final WHERE clause? > I was trying for example: > > for cust in Customer.select(Customer.q.city.startswith('T') or > Customer.q.city.startswith('Q') ): You can combine conditions using AND or OR (import them from sqlobject). Moreover q-magic automatically calls them if you combine your conditions using Python operators || or && for cust in Customer.select(OR(Customer.q.city.startswith('T'), Customer.q.city.startswith('Q')) or for cust in Customer.select(Customer.q.city.startswith('T') || Customer.q.city.startswith('Q') Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |