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.
