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)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Joining 2 tables which have the same foreign key from same table but one is a one to many

inner join two foreign key in one table

Joining table to union of two tables?

sql - Joining one table to two more tables

Joining the result of two statistical tables in one table in R

How to join 3 tables into one with these tables having foreign key to a fourth table?

Will one Foreign key to two tables cause confusion?

How to name two columns in join tables with two same foreign key at one table in SQL?

Missing FROM clause when joining two tables on a foreign key

UPDATE set table with 3 different tables foreign key and primary key

Merging two tables into one both the table having different where clause

One primary key in one table linked to two foreign keys in two different tables?

joining two tables, select values from two with second table having no records

using a limit on one table when joining two tables in mysql

SQL Server Table Design with Table having foreign key to two other tables

Joining two tables with reference to foreign key

Joining tables on foreign key

joining two tables and produce the table with same value in two different rows

Joining two tables together with multiple results in one table

Joining two tables with different values in one column

Insert into two tables with one table having an auto increment primary key and that same key is used as a foreign key in another table

mysql - How to perform joining of of two junctional tables in case where one of them has foreign key of another?

Primary Key/Foreign Key for a table with two junction tables

Joining two different tables with a common third table on a common column

how to create a select statement with 2 foreign keys in one table pointing to two different tables

Joining two tables on fields having different names but same meaning

Join tables without primary key, foreign key that will also return the null values when joining the table

Joining two tables having two same columns

How to avoid matching just one row in the second table twice with two different rows in the first table when joining tables