CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Kinwolf
Kinwolf

Reputation: 785

SQLAlchemy: Trouble querying in manyto many relationship

New to SQLalchemy, an sql queries in general but hopefully this will be clear to someone :) In a Flask application, I have two models, User and classes, in a many to many relationship

Here is the models.py

user_to_classes = db.Table('user_to_classes', Base.metadata,
db.Column('class_id', db.Integer, db.ForeignKey('classes.id')),
db.Column('user_id', db.Integer, db.ForeignKey('users.id'))
)



class Classes(db.Model):
    __tablename__= 'classes'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    date = db.Column(db.DateTime)
    participants = db.relationship('User', secondary=user_to_classes, backref = db.backref('classes',lazy='dynamic'))
    classtype_id = db.Column(db.Integer, db.ForeignKey('classtype.id'))
    status = db.Column(db.Integer)  #1 = open, 0 = closed

class User(UserMixin,db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    email = db.Column(db.String(64),unique=True,index=True)
    firstname = db.Column(db.String(64))
    lastname = db.Column(db.String(64))
    fullname = db.Column(db.String(64), index=True)
    telephone = db.Column(db.String(64))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
    password_hash = db.Column(db.String(128))
    member_since = db.Column(db.DateTime(), default=datetime.utcnow)
    last_seen = db.Column(db.DateTime(), default=datetime.utcnow)
    notes = db.Column(db.Text())
    punchcard_passes = db.Column(db.Integer)

I am trying to know how many classes attended a user. I have no problem querying how many users participated in a class, but not the reverse as there is no value to query in the user model. Is it even possible? Not being fluent in SQL queries, I am not sure what to search for either on google. All the exemples I have seen do a one way query, and never the other way.

thanks!

Upvotes: 0

Views: 341

Answers (1)

Andrew_Lvov
Andrew_Lvov

Reputation: 4668

How about len(user.classes) ? Doesn't it work ?

In addition: don't name a class in plural, since an object of it represents only one class.

Upvotes: 2

Related Questions