SQLAlchemy Many-To-Many join

Question

This is my models:Products and tags have Many-To-Many relationship

products_tags_table=db.Table('products_tags',
                        db.Column('product_id', db.Integer,db.ForeignKey('products.id'), nullable=False),
                        db.Column('tag_id',db.Integer,db.ForeignKey('tag.id'),nullable=False),
                        db.PrimaryKeyConstraint('product_id', 'tag_id')
                        )

class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column( db.Integer, primary_key = True)
    name = db.Column( db.String(256) )
    description = db.Column( db.Text() )
    background_img_url = db.Column( db.String(256) )
    products =db.relationship('Product',
                              secondary=products_tags_table,
                              backref='product_tags'
                             )  




class Product(db.Model):
    __tablename__ = "products"
    id = db.Column( db.Integer, primary_key = True)
    name = db.Column( db.String(256) )
    tags=db.relationship('ProductTag',
                        secondary=products_tags_table,
                        backref='tag_products'
                        )  

When I do the join Tag with Product. I got error:

class '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 'product_tags' and 'products'.

Here are my join code:

avai_tags = Tag.query.join(Product).order_by(ProductTag.name)

Am I missing something ?

Answer 1

First, in your products table, your second foreign key needs to property reference the table name "tags", so change 'tag.id' to 'tags.id'

products_tags_table=db.Table('products_tags',
                    db.Column('product_id', db.Integer,db.ForeignKey('products.id'), nullable=False),
                    db.Column('tag_id',db.Integer,db.ForeignKey('tags.id'),nullable=False), 
                                                                   #^Missing 's'
                    db.PrimaryKeyConstraint('product_id', 'tag_id')
                    )

And then in your products model, you want to accurately reference the name of the appropriate model. Change 'ProductTag' to 'Tag'

tags=db.relationship('Tag', # 'ProductTag' <- This is a relationship to the Tag table.
                    secondary=products_tags_table,
                    backref='tag_products'
                    )  

Then try your join like this

query = Tag.query.join(Product, Tag.products)

avail_tags = query.order_by(Tag.name).all()