SQLAlchemy relationships: Find instances that have one or more attribute in common

Question

I'm not sure how to phrase this question, as I don't know the terminology around what it is I'm trying to achieve (mods, please feel free to suggest improvments).

I have two types of users, Student and Instructor. Both can have one or more Tags, which are basically a genre (rock, funk, etc). To associate Student and Instructor with a Tag I have an association table for each.

I have a last model, Lesson that have IDs for Student and Instructor. At the creation of a Lesson, only one of these IDs are set, depending on whether it´s a Student or an Instructor that creates it. If a Student creates a Lesson, I need to find all Instructors that have one or more Tags in common with the Student. How do I go about achieving this? I have a limited knowledge about database relationships, so any help is appreciated!

Here's the code for the models:

class Instructor(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    genres = db.relationship("Tag", secondary=instructor_tag_association_table, backref=db.backref("instructors", lazy="dynamic"))

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    genres = db.relationship("Tag", secondary=student_tag_association_table, backref=db.backref("students", lazy="dynamic"))

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

class Lesson(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    instructor_id = db.Column(db.Integer, db.ForeignKey("instructor.id"))
    student_id = db.Column(db.Integer, db.ForeignKey("student.id"))

# Association tables
instructor_tag_association_table = db.Table(
    "instructor_tags",
    db.Column("instructor_id", db.Integer, db.ForeignKey("instructor.id")),
    db.Column("tag_id", db.Integer, db.ForeignKey("tag.id")),
)
student_tag_association_table = db.Table(
    "student_tags",
    db.Column("student_id", db.Integer, db.ForeignKey("student.id")),
    db.Column("tag_id", db.Integer, db.ForeignKey("tag.id")),
)

I know this is wrong on several levels, but say I have the ID of a single Instructor, then I'm looking for a query like this to find lessons:

instructor_id = 1
instructor = Instructor.query.filter_by(id=instructor_id).first()
eligible_lessons = Lesson.query.filter(Lesson.student_id.has("genre in instructor.genre"))

Answer 1

My answer was inspired by this one.

If I've interpreted correctly, I think the crux of the problem is that you are looking for lessons that have a student but no instructor assigned, where there is an intersection between the lesson's student genres and the instructor's genres.

I added student and instructor relationships on your Lesson model to complement the Foreign Keys you already have created, which made it easier for me to generate some testing data if nothing else:

class Lesson(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    instructor_id = db.Column(db.Integer, db.ForeignKey("instructor.id"))
    student_id = db.Column(db.Integer, db.ForeignKey("student.id"))
    student = db.relationship(Student, uselist=False, backref='lessons')
    instructor = db.relationship(Instructor, uselist=False, backref='lessons')

Here's the testing data I generated:

import random

db.drop_all()
db.create_all()
# create a set of tags
tags = [Tag(genre=g) for g in ('Jazz', 'Funk', 'Rock', 'Classical', 'Metal')]
# create 10 students with 2 tags randomly assigned
students = [Student(genres=random.sample(tags, 2)) for _ in range(10)]
# create 2 instructors with 2 tags randomly assigned
instructors = [Instructor(genres=random.sample(tags, 2)) for _ in range(2)]
# create a lesson for each student
lessons = [Lesson(student=s) for s in students]
db.session.add_all(tags + students + instructors + lessons)
db.session.commit()

Then for the query, I queried Lesson joining on Student and student_tag_association_table so that I could find a Lesson with instructor_id == None and a Student with a tag_id that matches one of the Instructor's linked tag_ids:

# randomly select an instructor
instructor = random.choice(instructors)
possible_lessons = db.session.query(Lesson).\
    join(Student).\
    join(student_tag_association_table).\
    filter(and_(Lesson.instructor_id == None,
        or_(
            student_tag_association_table.c.tag_id.in_(
                g.id for g in instructor.genres
            )
        )
    )).all()

Then to test:

for lesson in possible_lessons:
    try:
        assert any([g in lesson.student.genres for g in instructor.genres])
    except AssertionError:
        print('uh oh')
    else:
        print('yes')

# yes
# yes
# yes
# yes
# yes

As the test data is random, you'll get a different number of yes outputs each time.