CODEWITHSUNDEEP
pythonormsqlalchemyflask-sqlalchemy
tzimme
tzimme

Reputation: 45

How to make 'tags' affect a relation(column_property)

I'm working on a project with Flask-SQLAlchemy.

The model looks like this: cars have components, components can have issues

car has a column_property 'needs_repair' which is true when a car's component has issues

 needs_repair = column_property(exists().where(and_(
        carcomponent.columns['car_id'] == id,
        carcomponent.columns['component_id'] == componentissue.columns['component_id']
    )))

I added a table for tags with a 'skip'-column, tags are assigned via a table issue_car_tag(ignoring components, only referencing specific car-issue-relations).

Now, i want needs_repair to be true if all assigned tags have skip = False or no tags are assigned

How do I extend the column_property to achieve this?

edit: Model/table definitions:

class Component(Base):
    id = db.Column(db.Integer, primary_key=True)
    [...]
    issues = db.relationship('ISsue', secondary=componentissue, lazy='dynamic',
        back_populates='components')

    cars = db.relationship('Car', lazy = 'dynamic', secondary=carcomponent,
        back_populates="component"

    broken = column_property(exists().where(componentissue.columns['component_id'] == id))

class Car(Base):
   id = db.Column(db.Integer, primary_key=True)
   [...] 
   components = db.relationship('Component', secondary=carcomponent,
        back_populates="cars", lazy='dynamic')
   needs_repair = column_property(exists().where(and_(
            carcomponent.columns['car_id'] == id,
            carcomponent.columns['component_id'] == componentissue.columns['component_id']
        )))

class Issue(Base):
    __tablename__ = "issues"
    [...]    
    components = db.relationship('Component' lazy = 'dynamic', secondary=componentissue,
        back_populates='issues')

class Tag(Base):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text, unique=True)
    description = db.Column(db.Text, nullable=False, default="")
    skip = db.Column(db.Boolean, default = False)

class Issue_Car_Tag(Base):
    id = db.Column(db.Integer, primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'))
    car_id = db.Column(db.Integer, db.ForeignKey('car.id'))
    issue_id = db.Column(db.Integer, db.ForeignKey('issue.id'))
    tag = db.relationship('Tag', backref=db.backref('issue_car_tags'))
    car = db.relationship('Car',  backref=db.backref('issue_car_tags'))
    issue = db.relationship('Issue',  backref=db.backref('issue_car_tags'))

Upvotes: 0

Views: 214

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

If you'd move the definition of Car after the definitions of Tag and Issue_Car_Tag or reference those tables in some other manner, you could produce the following query construction

func.coalesce(func.bool_and(not_(Tag.skip)), False).\
    select().\
    where(Tag.id == Issue_Car_Tag.tag_id).\
    where(Issue_Car_Tag.car_id == id).\
    as_scalar()

and use that in an OR with your existing check:

needs_repair = column_property(
    or_(func.coalesce(func.bool_and(not_(Tag.skip)), False).
            select().
            where(Tag.id == Issue_Car_Tag.tag_id).
            where(Issue_Car_Tag.car_id == id).
            as_scalar(),
        exists().where(and_(
            carcomponent.c.car_id == id,
            carcomponent.c.component_id == componentissue.c.component_id))))

The query selects tags related to a car using the association table issue_car_tag and aggregates the skip values, coalescing an empty result or all null values.

Note that this results in false if no tags are assigned, so you have to handle that separately. If I've understood your existing query correctly, this is handled by your EXISTS expression already. Put another way, the new query results in true if tags exist and all have skip set to false.

Upvotes: 1

Related Questions