CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
Jake Lowen
Jake Lowen

Reputation: 929

flask-sqlalchemy: Query for records in one table with NO related records in another table (or records of a certain value)

I am writing a simple app that helps people call voters. I don't want my volunteers to bug any voter more than once. How can I get a list of voters who have not been called yet? I'm using flask with flask-sqlalchemy.

Voter model:

    class Voter(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(80))
        phone = db.Column(db.String(20))
        vanid = db.Column(db.String(20))
        address = db.Column(db.String(255))
        city = db.Column(db.String(255))
        zip_code = db.Column(db.String(20))
        lat  = db.Column(db.Float)
        lng = db.Column(db.Float)
        ...

Calls Model:

    class Call(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        voter_id = db.Column(db.Integer, db.ForeignKey('voter.id'))
        voter = db.relationship('Voter', backref=db.backref('calls', lazy='dynamic'))
        user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
        user = db.relationship('User', backref=db.backref('calls', lazy='dynamic'))
        time = db.Column(db.DateTime)
        status_code = db.Column(db.String(20))
        ...

call.status_code will contain values such as completed,busy,wrong_number, etc.

I want to get a list of all voters who are eligible to be called so that I can pick one to serve to the volunteer. (ie. call.status_code != completed etc. OR has no related call records)

If were to do this in raw SQL I would likely do:

Select [whatever]
FROM Voter v
LEFT JOIN Call c on c.voter_id = v.id
WHERE c.status_code IS NULL OR c.status_code = 'busy'

I can't figure it out in sqlalchemy. Any insights?

Upvotes: 3

Views: 3882

Answers (1)

Paweł Pogorzelski
Paweł Pogorzelski

Reputation: 651

You can probably use something like :

required_voters = db.session.query(Voter).outerjoin(Call).filter((Call.voter_id == None) | (Call.status_code != 'completed'))

EDIT

Proposed query :

vwcc = db.session.query(distinct(Call.voter_id)).filter(Call.status_code == 'completed').subquery()
this will select all unique voters with completed calls

now for the main part

required_voters = db.session.query(Voter).outerjoin(Call).outerjoin(vwcc,vwcc.voter_id == Voter.id) ).filter((vwcc.voter_id == None) & ((Call.voter_id == None) | (Call.status_code != 'completed')))

This I believe will select all Voters who : - don't have a call with completed status and - didn't have a call or their call didn't have completed status Remember that I write it from mind so the syntax may be somewhat off

Upvotes: 4

Related Questions