[FIXED] SQLAlchemy select multiple columns like inner join

Issue

I have database with users, companies and their articles. Multiple users can be inside in one company and users can create articles. Now, i m try to make users can view articles of their company. I select all articles by company_id and attach username with add_column, but in result, i have duplicated records:

[(<Article 1>, <User 1>), (<Article 1>, <User 2>), (<Article 2>, <User 1>), (<Article 2>, <User 2>)]

Need to be something like :

[(<Article 1>, <User 1>), (<Article 2>, <User 2>)]  - this articles with one article_company_id

My code:

user_id = current_user.id
# Here i get company_id of current user
company_id = User.query.filter_by(id=user_id).first().company_id
# Here i want to load all articles for company
article_list = Article.query.add_entity(User).filter_by(article_company_id=company_id).all()
        

DB models:

class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(100))
    name = db.Column(db.String(1000))
    company_id = db.Column(db.String(100))
    created = db.Column(db.String(100))
    rights_id = db.Column(db.String(100))
    gravatar = db.Column(db.String(100))
    

class Company(db.Model):
    __tablename__ = 'company'
    id = db.Column(db.Integer, primary_key=True)
    company_admin_id = db.Column(db.Integer)
    company_name = db.Column(db.String(100))


class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True)
    article_owner_id = db.Column(db.Integer, ForeignKey('user.id'))
    article_company_id = db.Column(db.Integer, ForeignKey('company.id'))
    article_creation_date = db.Column(db.String(100))
    article_status = db.Column(db.String(100))
    article_data = db.Column(db.JSON(100))
    article_name = db.Column(db.String(100))
 

As you can see in Screenshot i have 2 articles with one article_company_id, and as result i wanna get this two records but with their owner_name from Users table. How can i do this?

Solution

Problem with using the filter only on article_company_id is that it won't filter out the user either you include the user_id in the filter or you can use joins here and select only the columns that you need,

for example

res = (
    db.session.query(Article.article_name, 
                     User.name)
    .join(User, Company)
    .all()
)

print(res[0].name)

Answered By - Talha Junaid