RE: [sqlalchemy] Simple Join failing

2010-10-05 Thread King Simon-NFHD78 
Warwick Prince wrote:
 
 Hi All
 
 I have what I hope is a very simple question;
 
 Just started experimenting with joins, so I tried a very basic test
 and got a fail that I don't understand.  It appears that SA is
 creating bad SQL, but I'm sure it's something I'm missing..  Here's
 what I did;
 
 I have two tables.  products and product_prices.  There is a one to
 many relationship based on Foreign Keys of Group and Code   Both
 tables have columns Group and Code and they are also the primary of
 each.
 
 I do this;
 
 e = an Engine (MySQL connector)
 m = MetaData(e)
 
 prod = Table('products', m, autoload=True)
 price = Table('product_prices, m, autoload=True
 # These tables are both fine and load correctly
 
 # I want to build up my query generatively, so..
 
 # Note that I'm selecting specific columns, and both sets of Foreign
 Keys are in the selected columns (not that I believe I should need to
 do that)
 q = prod.select().with_only_columns(['products.Group',
 'products.Code', 'product_prices.Group', 'product_prices.Code',
 'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
 
 q = q.join(price)
 
 # I get this error;
 ArgumentError: Can't find any foreign key relationships between
 'Select object' and 'product_prices'.(They do exists BTW)
 
 So, I remove my .with_only_columns and try again
 
 q = prod.select()


Here you are creating a Select object (ie SELECT all columns FROM
products)


 
 q = q.join(price)
 

Now you are joining that Select object with another table

ie. (SELECT all columns FROM products) JOIN price ON join condition

The extra parentheses are there because you are joining a SELECT with a
table.

Instead, you want to join the tables together:

  prod.join(price)

To select from that, you can use the standalone select function:

http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
xpression.select

eg.

select([products.c.Group, products.c.Code, price.c.ListPriceEx],
   from_obj=[prod.join(price)])

Hope that helps,

Simon



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Simple Join failing

2010-10-05 Thread Warwick Prince 
Hi Simon

Thanks for that - I knew it was something wrong with the approach but simply
could not pick it!Back to the test bench for another go :-)

Cheers
Warwick

P.S.  OK - I have to ask - when and how (why?) do I do the .join on the
query? ;-)


On 5 October 2010 19:41, King Simon-NFHD78 simon.k...@motorola.com wrote:

 Warwick Prince wrote:
 
  Hi All
 
  I have what I hope is a very simple question;
 
  Just started experimenting with joins, so I tried a very basic test
  and got a fail that I don't understand.  It appears that SA is
  creating bad SQL, but I'm sure it's something I'm missing..  Here's
  what I did;
 
  I have two tables.  products and product_prices.  There is a one to
  many relationship based on Foreign Keys of Group and Code   Both
  tables have columns Group and Code and they are also the primary of
  each.
 
  I do this;
 
  e = an Engine (MySQL connector)
  m = MetaData(e)
 
  prod = Table('products', m, autoload=True)
  price = Table('product_prices, m, autoload=True
  # These tables are both fine and load correctly
 
  # I want to build up my query generatively, so..
 
  # Note that I'm selecting specific columns, and both sets of Foreign
  Keys are in the selected columns (not that I believe I should need to
  do that)
  q = prod.select().with_only_columns(['products.Group',
  'products.Code', 'product_prices.Group', 'product_prices.Code',
  'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
 
  q = q.join(price)
 
  # I get this error;
  ArgumentError: Can't find any foreign key relationships between
  'Select object' and 'product_prices'.(They do exists BTW)
 
  So, I remove my .with_only_columns and try again
 
  q = prod.select()


 Here you are creating a Select object (ie SELECT all columns FROM
 products)


 
  q = q.join(price)
 

 Now you are joining that Select object with another table

 ie. (SELECT all columns FROM products) JOIN price ON join condition

 The extra parentheses are there because you are joining a SELECT with a
 table.

 Instead, you want to join the tables together:

  prod.join(price)

 To select from that, you can use the standalone select function:

 http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
 xpression.select

 eg.

 select([products.c.Group, products.c.Code, price.c.ListPriceEx],
   from_obj=[prod.join(price)])

 Hope that helps,

 Simon



 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

RE: [sqlalchemy] Simple Join failing

2010-10-05 Thread King Simon-NFHD78 
Warwick Prince wrote:
 
 Hi Simon
 
 Thanks for that - I knew it was something wrong with the approach but
 simply could not pick it!Back to the test bench for another go :-)
 
 Cheers
 Warwick
 
 P.S.  OK - I have to ask - when and how (why?) do I do the .join on
 the query? ;-)
 

In SQL, you can treat a query just like a table, so you can join 2
queries together, or join a query to another table. For example:

SELECT *
FROM
   (SELECT a, b FROM table_1) as q1
 INNER JOIN
   (SELECT c, d FROM table_2) as q2
 ON q1.b = q2.c

That example is not very helpful - it could easily be rewritten as a
single SELECT, but I hope you see that the subqueries can be as
complicated as you like.

The object that you were originally producing with your 'q.join(price)'
wasn't a Select object, but a Join - something that you can select from.
You could write something like this:

# JOIN the price table with a query on the products table:
j = prod.select().join(price)

# SELECT from that JOIN:
q = select(some_columns, from_obj=[j])

This almost certainly isn't what you wanted in your situation, but there
are plenty of cases where subqueries are very useful.

Hope that helps,

Simon

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Simple Join failing

2010-10-05 Thread Warwick Prince 
Hi Simon

Thanks for your help.  It's amazing what a tiny hint in the right direction
can do..  Between these emails, I've made a proof on concept, and am now
implementing the code in the real app.   So easy when I'm not blocked by a
warped vision of what I'm doing.

Funny, looking back at the docs I can now clearly see TABLE.join T A B L
E.join, not query.join...I'm SURE that wasn't there before  ;-D

Thanks again.

Cheers
Warwick


On 5 October 2010 20:39, King Simon-NFHD78 simon.k...@motorola.com wrote:

 Warwick Prince wrote:
 
  Hi Simon
 
  Thanks for that - I knew it was something wrong with the approach but
  simply could not pick it!Back to the test bench for another go :-)
 
  Cheers
  Warwick
 
  P.S.  OK - I have to ask - when and how (why?) do I do the .join on
  the query? ;-)
 

 In SQL, you can treat a query just like a table, so you can join 2
 queries together, or join a query to another table. For example:

 SELECT *
 FROM
   (SELECT a, b FROM table_1) as q1
 INNER JOIN
   (SELECT c, d FROM table_2) as q2
 ON q1.b = q2.c

 That example is not very helpful - it could easily be rewritten as a
 single SELECT, but I hope you see that the subqueries can be as
 complicated as you like.

 The object that you were originally producing with your 'q.join(price)'
 wasn't a Select object, but a Join - something that you can select from.
 You could write something like this:

 # JOIN the price table with a query on the products table:
 j = prod.select().join(price)

 # SELECT from that JOIN:
 q = select(some_columns, from_obj=[j])

 This almost certainly isn't what you wanted in your situation, but there
 are plenty of cases where subqueries are very useful.

 Hope that helps,

 Simon

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.