How to query with like() when using many-to-many relationships in SQLAlchemy?

user_514149 0
Viewed 40

I have the folloing many-to-many relationship defined in SQLAlchemy:

training_ids_association_table = db.Table(
    "training_ids_association",
    db.Model.metadata,
    Column("training_id", Integer, ForeignKey("training_sessions.id")),
    Column("ids_id", Integer, ForeignKey("image_data_sets.id")),
)

class ImageDataSet(db.Model):
    __tablename__ = "image_data_sets"
    id = Column(Integer, primary_key=True)
    tags = Column(String)
    trainings = relationship("TrainingSession", secondary=training_ids_association_table, back_populates="image_data_sets")

class TrainingSession(db.Model):
    __tablename__ = "training_sessions"
    id = Column(Integer, primary_key=True)
    image_data_sets = relationship("ImageDataSet", secondary=training_ids_association_table, back_populates="trainings")

Note the field ImageDataSet.tags, which can contain a list of string items (i.e. tags), separated by a slash character. If possible I would rather stick to that format instead of creating a new table just for these tags.

What I want now is to query table TrainingSession for all entries that have a certain tag set ub their related ImageDataSet's. Now, if an ImageDataSet has only one tag saved in the tags field, then the following works:

TrainingSession.query.filter(TrainingSession.image_data_sets.any(tags=find_tag))

However, as soon as there are multiple tags in the tags field (e.g. something like "tag1/tag2/tag3"), then of course this filter above does not work any more. So I tried it with a like:

.filter(TrainingSession.image_data_sets.like(f'%{find_tag}%'))

But this leads to an NotImplementedError in SQLAlchemy. So is there a way to achieve what I am trying to do here, or do I necessarily need another table for the tags per ImageDataSet?

sqlalchemy python
1 Answers

You can apply any filters on related model columns if you join this model first:

query = session.query(TrainingSession). \
    join(TrainingSession.image_data_sets). \
    filter(ImageDataSet.tags.like(f"%{find_tag}%"))

This query is translated to the following SQL statement:

SELECT training_sessions.id FROM training_sessions
JOIN training_ids_association ON training_sessions.id = training_ids_association.training_id
JOIN image_data_sets ON image_data_sets.id = training_ids_association.ids_id 
WHERE image_data_sets.tags LIKE %(find_tag)s

Note that you may stumble to a problem with storing tags as strings with separators. If some records have tags tag1, tag12, tag123 they will all pass the filter LIKE '%tag1%'.

It would be better to switch to ARRAY column if your database supports this column type (PostgreSQL for example). Your column may be defined like this:

tags = Column(ARRAY(String))

And the query may look like this:

query = session.query(TrainingSession). \
   join(TrainingSession.image_data_sets). \
   filter(ImageDataSet.tags.any(find_tag))
user_66825170
Related