Flask SQLAlchemy query join?

Hi,
I am working on my Flask Capstone Project.
I have an issue with an advanced query.
I created two table (Colleagues and Admins).
They have one-to-one relationship.
Admins table include colleagues_id as foreign_key.

Colleagues model:
class Colleagues(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    user_name = db.Column(db.String(32), unique = True, index = True)
    email = db.Column(db.String(128), unique = True, index = True)
    first_name = db.Column(db.String(32), nullable = False, index = True)
    last_name = db.Column(db.String(64), nullable = False, index = True)
    avatar = db.Column(db.String(8), nullable = True)
    position = db.Column(db.String(2048), index = True)
    password_hash = db.Column(db.String(128))
    company_id = db.Column(db.Integer, db.ForeignKey("company.id"))
    # one-to-many relation: colleague < ideas
    ideas = db.relationship(
        "Ideas", 
        backref = "colleague", 
        lazy = "dynamic", 
        cascade = "all, delete, delete-orphan"  # cascading deletion
    )

    def __repr__(self):
        return f"""User name: {self.user_name}
First name: {self.first_name}
Last name: {self.last_name}
ID: {self.id}
Email: {self.email}
Position: {self.position}"""

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)
    
    def fullname(self):
        return f"{self.first_name} {self.last_name}"
Admins model:
class Admins(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    
    # privilegs to change name, logo, email, joiner password
    update_company = db.Column(db.Boolean, unique = False, default = False, index = True)
    
    # privilegs to add-remove colleagues to the admin-team with privilegs and update existed privilegs
    update_privilegs = db.Column(db.Boolean, unique = False, default = False, index = True)
    
    # privileges to register-update-delete colleagues 
    update_colleague = db.Column(db.Boolean, unique = False, default = False, index = True)
    
    # privilegs to create-update-delete idea box and post
    update_box = db.Column(db.Boolean, unique = False, default = False, index = True)
    
    colleague_id = db.Column(db.Integer, db.ForeignKey("colleagues.id"))
    
    # one-to-one relation: admin - colleague
    colleague = db.relationship(
        "Colleagues", 
        backref = "admin", 
        lazy = "select", 
        uselist = False    # set one-to-one relationship
    )
    # one-to-many relation: admin < boxes
    boxes = db.relationship(
        "Boxes", 
        backref = "admin", 
        lazy = "dynamic", 
        # cascade = "all, delete, delete-orphan"
    )
    
    def __repr__(self):
        return f"""Admin name: {self.colleague.user_name}
Admin ID: {self.id}
Colleague ID: {self.colleague.id}
Email: {self.colleague.email}
Privilegs:
    - Update Company: {self.update_company}
    - Update Privilegs: {self.update_privilegs}
    - Update Colleagues: {self.update_colleague}
    - Update Idea Box: {self.update_box}"""

I try write a query to get all colleagues from the Colleagues who not in the Admins table.

My code is the following:
    admins = Admins.query.all()
    colleagues = Colleagues.query.all()

    for admin in admins:
        print(admin.colleague_id)
        for colleague in colleagues:
            if admin.colleague_id == colleague.id:
                colleagues.remove(colleague)

This solution works but how to write a query with the above result?

Thanks for help

lendoo

Hi,

Have you tried practicing in the terminal for structuring the query?
If you load your db into a flask shell it should let you play around and refine your idea of how it should be structured.

You would need to look at what kind/type of joins (inner, left, right, outer) are available, and then choice the one that suits your needs. There are plenty of blogs/tutorials which explains the different kind of joins.

I haven’t taken the Codecademy course, so I don’t know if it’s in the curriculum, but filter sounds like what you want to do:

https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.filter

1 Like

Filter alone won’t get you there, a .join() will also be required

Filter on Colleagues table is not enough, given the information about admins is stored in a different table.

After a great difficulty figure out I successfully wrote my first join query using Flask.

Example

SQL query:

f"""
SELECT  *
FROM colleagues
JOIN admins
ON colleagues.id = admins.colleague_id 
AND colleagues.company_id = {company_id};
"""

SQLAlchemy:

company_admins = db.session.query(Colleagues, Admins).filter(
        Colleagues.id == Admins.colleague_id,
        Colleagues.company_id == company_id
    ).all()

Both return the expected rows.