Joining two tables with one table having a different foreign key

Atrus

I'm attempting to join two tables together, however, I keep receving the errors of:

sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'recipe' and 'ingredient'.

and

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'recipe' and 'ingredient'.

class Recipe(db.Model):
    query_class = RecipeQuery
    __tablename__ = 'recipe'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.Text)
    description = db.Column(db.Text)
    directions = db.Column(db.Text)
    prep_time = db.Column(db.String(15))
    cook_time = db.Column(db.String(15))
    image = db.Column(db.Text)
    ingredients = db.relationship('Ingredient', secondary=ingredients)
    credit = db.Column(db.String)

    search_vector = db.Column(TSVectorType('name', 'description', 'directions'))

class Ingredient(db.Model):
    query_class = IngredientQuery
    __tablename__ = 'ingredient'

    id = db.Column(db.Integer, primary_key=True)
    original = db.Column(db.Text)
    name = db.Column(db.Integer, db.ForeignKey('ingredient_name.id'))
    amount = db.Column(db.String(10))
    unit = db.Column(db.String(20))
    modifiers = db.Column(db.Text)

    search_vector = db.Column(TSVectorType('original'))

ingredients = db.Table('ingredients',
    db.Column('recipe', db.Integer, db.ForeignKey('recipe.id')),
    db.Column('ingredient', db.Integer, db.ForeignKey('ingredient.id'))
    )

I've tried selecting the items three different ways, all fail with the same error.

try1 = db.session.query(models.Recipe).join(models.Ingredient, secondary=ingredients)
try2 = db.session.query(models.Recipe).join(models.Ingredient)
try3 = db.session.query(models.Recipe).join('ingredients')

It looks similar to the example given for a many-to-many relationship given at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html The only real difference being that Ingredient has a foreign key, which might be throwing it off? If so, I'm still not sure how to fix that issue.

Thanks

Dave Anderson

Does one of these work if you explicitly specify the relationship in the join?

db.session.query(models.Recipe).\
    join(models.Ingredient, models.Recipe.ingredients)

db.session.query(models.Recipe).\
    join(models.Recipe.ingredients)

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

Relating two foreign key columns in Table A to a primary key column in Table B

来自分类Dev

SQL query for selecting foreign-key rows with more than one link to the primary-key table

来自分类Dev

Relationship between tables with two foreign keys

来自分类Dev

ALTER TABLE语句与FOREIGN KEY约束冲突

来自分类Dev

Create ID automatically for all entries into tables with same foreign key

来自分类Dev

two foreign keys to same primary key select statement MYSQL

来自分类Dev

Left join multiple tables onto one table

来自分类Dev

INSERT语句与FOREIGN KEY SAME TABLE约束冲突

来自分类Dev

MYSQL:基于SELECT结果的ALTER TABLE DROP FOREIGN KEY

来自分类Dev

EF 7:INSERT语句与FOREIGN KEY SAME TABLE冲突

来自分类Dev

带有FOREIGN KEY错误的ORACLE CREATE TABLE

来自分类Dev

EF迁移:ALTER TABLE语句与FOREIGN KEY约束冲突

来自分类Dev

如何修复:UPDATE 语句与 FOREIGN KEY SAME TABLE 约束冲突

来自分类Dev

PostgreSQL insert into multiple tables, using foreign key from first insertion in the second insertion

来自分类Dev

Get result from two different tables using mysql

来自分类Dev

merging two left join on same table into one

来自分类Dev

How to dynamically count rows in a table from two another tables?

来自分类Dev

Using date from one table in a where clause with a column in a different table

来自分类Dev

与FOREIGN KEY约束冲突

来自分类Dev

filter by first foreign key

来自分类Dev

与FOREIGN KEY约束冲突

来自分类Dev

SQL FOREIGN KEY的

来自分类Dev

Rails建模has_one被另一个foreign_key过滤

来自分类Dev

当有多个带有Foreign_key的记录时,Rails has_one关联

来自分类Dev

Entity framework one-to-many relationship - how to rename foreign key name when it is named Id?

来自分类Dev

EF6:复合主键字段作为外键(ALTER TABLE语句与FOREIGN KEY约束冲突)

来自分类Dev

无法删除对象'dbo.Table1',因为它已被FOREIGN KEY约束引用

来自分类Dev

How to have one Flask app listen on two different ports?

来自分类Dev

Query to convert a key-value table into a human-readable one?

Related 相关文章

  1. 1

    Relating two foreign key columns in Table A to a primary key column in Table B

  2. 2

    SQL query for selecting foreign-key rows with more than one link to the primary-key table

  3. 3

    Relationship between tables with two foreign keys

  4. 4

    ALTER TABLE语句与FOREIGN KEY约束冲突

  5. 5

    Create ID automatically for all entries into tables with same foreign key

  6. 6

    two foreign keys to same primary key select statement MYSQL

  7. 7

    Left join multiple tables onto one table

  8. 8

    INSERT语句与FOREIGN KEY SAME TABLE约束冲突

  9. 9

    MYSQL:基于SELECT结果的ALTER TABLE DROP FOREIGN KEY

  10. 10

    EF 7:INSERT语句与FOREIGN KEY SAME TABLE冲突

  11. 11

    带有FOREIGN KEY错误的ORACLE CREATE TABLE

  12. 12

    EF迁移:ALTER TABLE语句与FOREIGN KEY约束冲突

  13. 13

    如何修复:UPDATE 语句与 FOREIGN KEY SAME TABLE 约束冲突

  14. 14

    PostgreSQL insert into multiple tables, using foreign key from first insertion in the second insertion

  15. 15

    Get result from two different tables using mysql

  16. 16

    merging two left join on same table into one

  17. 17

    How to dynamically count rows in a table from two another tables?

  18. 18

    Using date from one table in a where clause with a column in a different table

  19. 19

    与FOREIGN KEY约束冲突

  20. 20

    filter by first foreign key

  21. 21

    与FOREIGN KEY约束冲突

  22. 22

    SQL FOREIGN KEY的

  23. 23

    Rails建模has_one被另一个foreign_key过滤

  24. 24

    当有多个带有Foreign_key的记录时,Rails has_one关联

  25. 25

    Entity framework one-to-many relationship - how to rename foreign key name when it is named Id?

  26. 26

    EF6:复合主键字段作为外键(ALTER TABLE语句与FOREIGN KEY约束冲突)

  27. 27

    无法删除对象'dbo.Table1',因为它已被FOREIGN KEY约束引用

  28. 28

    How to have one Flask app listen on two different ports?

  29. 29

    Query to convert a key-value table into a human-readable one?

热门标签

归档