0

I'm making a bug tracker structured such that each user can have multiple projects, and each project can record multiple bugs. I'd like users to be able to see how many new bugs were reported across all their projects since their last login.

I structured it such that there's a Users model, a Projects model, and Bugs model. I've included the three models with their relevant columns below:

class Users(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True, nullable = False)
    # ...
    last_login = db.Column(db.DateTime, nullable = False)
    # AS PARENT
    owned_projects = db.relationship('Projects', backref="project_owner")

class Projects(db.Model):
    id  = db.Column(db.Integer, primary_key = True, nullable = False)
    # ...
    # AS CHILD
    owner = db.Column(db.Integer, db.ForeignKey('users.id'))
    # AS PARENT
    bugs = db.relationship('Bugs', backref = 'containing_project')

class Bugs(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    # ...
    status = db.Column(db.String(20))
    report_date = db.Column(db.DateTime, index = True, default = dt.datetime.now())
    # AS CHILD
    project = db.Column(db.Integer, db.ForeignKey('projects.id'))

To filter a Bugs query for bugs reported since the user's last login, I can do a filter like Bugs.query.filter(Bugs.report_date > current_user.last_login). This shows ALL bugs from ALL users, but I'm having trouble constructing a query that filters it down to only bugs in projects owned by the user.

.filter(Bugs.containing_project in current_user.owned_projects) returns "<flask_sqlalchemy.BaseQuery object at 0x04E06988>", but I have no idea how to work with that. I read about .contains but that goes in the wrong direction, current_user.owned_projects.contains(Bugs.project) does not work.

I also tried .filter(Bugs.containing_project.owner == current_user.id), but got an error "AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Bugs.containing_project has an attribute 'owner'".

The only option I can think of now is to iterate through every single bug to find the ones that belong to a project owned by the user, but that would be nightmarish in terms of performance. Surely there's a .in method or something similar?

Please advise on how I can achieve this, thanks in advance!

Panpaper
  • 451
  • 1
  • 6
  • 16

1 Answers1

0

This code doesn't work?

.filter(Bugs.containing_project.any(Projects.id.in_([ proj['id'] for proj in current_user.owned_projects])

I refered this site.
SQLAlchemy: filter by membership in at least one many-to-many related table

And, if my answer doesn't work well, this site may help you.
SQLAlchemy how to filter by children in many to many

S.Hashiba
  • 615
  • 7
  • 15
  • Thanks! I changed your suggestion slightly - `.filter(Bugs.containing_project.has(Projects.id.in_([ proj.id for proj in current_user.owned_projects])))`. My models aren't using a many-to-many relationship, so `Projects` is not subscriptable, and I cannot use the `.any` method. Instead, I'm using `.has` and that seemed to work! For anyone else who might come across your answer, could you add 2 ")" to your code block to close off all brackets? Thanks again! – Panpaper Sep 26 '20 at 04:29