Flask Sqlalchemy: Query a database with a foreign key-CodePudding

I am still learning flask sqlalchemy but am working on a dashboard using flask, mysql workbench, and sqlalchemy. I would like to query two tables:

class User(db.Model):
    id = Column(Integer(), primary_key=True)
    name = Column(String(200))
    first_name = Column(String(200))

class Certificates(db.Model):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id", 
    ondelete="CASCADE"))
    report_sent = Column(Boolean, default=False)
    delivery_time = Column(DateTime)
    certificate_type = Column(String(32))

I would like to know the total number of users that has a certificate

I tried:

user_with_cert = db.session.query(func.count(User.id)).filter(Certificates.certificate_type != None).filter(Certificates.user_id == User.id).all()

The query above returned the total number of records in the Certificate table not the actual number of users with a certificate.

I also tried:

user_cert = (
            db.session.query(User, Certificates)
            .join(Certificates)
            .all()
        )

        for user, cert in b2c_cert:
            t = db.session.query(func.count(user.id)).filter(
                cert.user_id == user.id).filter(cert.certificate_type != None).all()
            print(t)

Please how can I achieve this goal?

CodePudding user response：

You need to use db.relationship('Child table', backref='parent') on your parent table where the backref property identifies the parent table. And db.ForeignKey(table.primary_key) class on the column from your child table.

Example:

class User(db.Model):
    id = Column(Integer(), primary_key=True)
    name = Column(String(200))
    first_name = Column(String(200))
    certificates = db.relationship('Certificates', backref='user')

class Certificates(db.Model):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id', 
    ondelete="CASCADE"))
    report_sent = Column(Boolean, default=False)
    delivery_time = Column(DateTime)
    certificate_type = Column(String(32))

Then you can just use

db.session.query(Certifiates).filter(Certifiates.user_id == user.id).all()

to get the results (I think that's the correct condition, it may not be that way).

CodePudding user response：

So I modified my table by adding a db.relationship to backref the parent in the parent table:

class User(db.Model):
    id = Column(Integer(), primary_key=True)
    name = Column(String(200))
    first_name = Column(String(200))
    certificates = db.relationship('Certificates', backref='user')

Present record in the Certificates.user_id is (1, 1, 2, 1, 2): I think the total number of record is 5 but the total number of users with certificate is 2 So I ran this query which gave me a distinct number of users with certificates:

users_with_cert = db.session.query(Certificates.user_id).distinct().count()

Please suggestions are welcomed, cos am thinking this query is right but only for the users assigned to a Certificates, maybe not necessarily the users with a certificate