Express SQL Join and Many-to-Many filter in SQLAlchemy format

Question

Im trying to learn SQLAlchemy best practice for a) doing what I need and b) doing it in the most efficient way.

I have currently have 3 tables, defining a many-to-many relationship between club and person.

class Club(db.Model)
   __tablename__ = 'club'
   id = db.Column(db.Integer, primary_key=True)
   type = db.Column(db.String)

class Person(db.Model)
   __tablename__ = 'person'
   id = db.Column(db.Integer, primary_key=True)

club_person_assoc = Table('club_person_ass', db.Model.metadata,
    Column('club_id', db.Integer, db.ForeignKey('club.id')),
    Column('person_id', db.Integer, db.ForeignKey('person.id'))
)

What is a good way of returning: all clubs that a particular person (person.id==1) is a member of, conditioned on a type of club (club.type='sports')

In SQL I can express the statement directly as:

SELECT *
  FROM (
    SELECT *
      FROM club
      WHERE club.type = 'sports'
  ) AS lef
INNER JOIN (
    SELECT *
      FROM club_person_assoc
      WHERE club_person_assoc.person_id = 1
    ) AS rig
  ON lef.id = rig.club_id;

I can fudge it using python list comprehension by doing:

class Person(db.Model)
       ...
       clubs = db.relationship('Club', secondary=club_person_assoc)

# later using list comprehension
filtered = [club for club in person_x.clubs if club.type == 'sports']

but the above of course feels contrived and slower than a native DB query.

If you have any recommendations for good locations to learn this stuff besides the docs, in a more tutorial style I would appreciate the comment.

Answer 1

I don't have a tutorial recommendation but doing a list comprehension is the exact opposite of doing it in the most efficient way.

The way of doing this would be something like:

Session().query(Club.Id).join(Person).filter(Club.type == "sports").filter(Person.id == 1).all()