Flask / SQLAlchemy - Request tables with many-to-many relationships

Question

I used Flask and SQLAlchemy to create an application based on a database. Here is the classes that I have defined:

models.py

 class HasTag(db.Model):
    tagged_document_id = db.Column(db.Integer, db.ForeignKey('Document.id'), primary_key=True)
    document_tag_id = db.Column(db.Integer, db.ForeignKey('Tag.id'), primary_key=True)

class Document(db.Model):
    id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True, autoincrement=True)
    title = db.Column(db.Text)
    tag = db.relationship("Tag",
                    secondary=HasTag,
                    back_populates="tagged_document",
                    lazy="dynamic")

class Tag(db.Model):
    id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True, autoincrement=True)
    label = db.Column(db.String, nullable=False)
    tagged_document = db.relationship("Document",
                    secondary=HasTag,
                    back_populates="tag",
                    lazy="dynamic")

In the application, I have an advanced search form where it is possible to do a full text search through the different fields of the Document table.

routes.py

@app.route("/search")
def search():
    keyword = request.args.get("keyword", None)   
    query = Document.query

    if keyword:
        query = Document.query.filter(or_(
            Document.title.like("%{}%".format(keyword)),
            ...
        ))

The thing is, I'd like to be able to search the keyword given by the user also in the label of the tag. I tried something like:

    if keyword:
        query = Document.query.join(Tag).filter(or_(
            Document.title.like("%{}%".format(keyword)),
            ...,
            Tag.label.like("%{}%".format(keyword))
        ))

But I get this error: AttributeError: 'HasTag' object has no attribute 'foreign_keys'

Can you help me? Thanks!

https://stackoverflow.com/questions/19205290/flask-sqlalchemy-model-has-no-attribute-foreign-keys might help — E.Serra, Mar 18 '19 at 11:28
check the accepted answer here https://stackoverflow.com/questions/6044309/sqlalchemy-how-to-join-several-tables-by-one-query — Rabin Lama Dong, Mar 18 '19 at 11:48

score 1 · Answer 1 · answered Mar 18 '19 at 11:51

I have a similar structure in one of my projects, and this is how I define relatioship:

leagues = db.relationship("League", 
                          secondary=LeagueTeamAssociation.__tablename__,
                          back_populates="teams")

So, You need to provide table name to secondary parameter, either using above syntax (You'll need to add __tablename__ to your HasTag class) or using string "has_tag" (provided that this is the name of the table in the database).

Flask / SQLAlchemy - Request tables with many-to-many relationships

1 Answers1