CODEWITHSUNDEEP
flasksqlalchemymany-to-manyflask-sqlalchemytagging
gcerar
gcerar

Reputation: 988

Flask-SQLAlchemy query many-to-many tagging with multiple requred tags

I have defined models:

tags = db.Table('tags',
                db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
                db.Column('photo_id', db.Integer, db.ForeignKey('photo.id')),
                )


class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique=True)


class Photo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    sha1sum = db.Column(db.LargeBinary(20), unique=True)
    ...
    tags = db.relationship('Tag', secondary=tags,
                           backref=db.backref('photos', lazy='dynamic'))

In flask controller/view I get array of input tags for example ['summer', 'selfie', ...].

Questions:

  1. Efficient query for photos which contain all of the requested tags?
  2. How can it be extended for search with incomplete tags such as ['summ', 'elfi', ...]?

Upvotes: 5

Views: 4042

Answers (1)

van
van

Reputation: 76992

Might not be the most efficient (if you have many tags to search), but very readable way to compose the query:

input_tags = ['selfie', 'summer']
q = db.session.query(Photo)
for tag in input_tags:
    q = q.filter(Photo.tags.any(Tag.name == tag))

For incomplete use startswith(..) instead of ==:

input_tags = ['sum', 'fu']
q = db.session.query(Photo)
for tag in input_tags:
    q = q.filter(Photo.tags.any(Tag.name.startswith(tag)))

Upvotes: 6

Related Questions