Vfrdtyky

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.

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()