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.