SQLAlchemy subquery comparison

Question

I have the following set of tables:

class Job(db.Model):
    __tablename__ = 'jobs'
    id = db.Column(db.Integer, primary_key=True)


class Informant(db.Model):
    __tablename__ = 'informants'
    id = db.Column(db.Integer, primary_key=True)
    job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'))
    max_students = db.Column(db.Integer)        


class Student(db.Model):
    __tablename__ = 'students'
    id = db.Column(db.Integer, primary_key=True)


queues = db.Table('queues',
                  db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
                  db.Column('job_id', db.Integer, db.ForeignKey('jobs.id')),
                  PrimaryKeyConstraint('student_id', 'job_id'))

Now I need to obtain something like:

SELECT jobs.id
FROM jobs
WHERE (
    SELECT SUM(informants.max_students)
    FROM informants
    WHERE informants.job_id = jobs.id
) <= (
    SELECT COUNT(1)
    FROM queues
    WHERE queues.job_id = jobs.id
)

So basically I search the jobs with an amount of students that exceed the maximal capacity, the sum of the related informants' capacities. Is there a clean way to do this in SQLAlchemy? I tried the following:

db.session.query(Job.id).filter( \
    db.session.query(db.func.sum(Informant.max_students)). \
        filter(Informant.job_id == Job.id) <= \
    db.session.query(db.func.count(1)).select_from(queues). \
        filter(queues.c.job_id == Job.id))

This yields something like SELECT jobs.id FROM jobs WHERE 0 = 1. Is there something I'm missing, because I have successfully used similar queries before. Or am I better of using db.engine.execute to execute the raw SQL?

Why don't you move 'max_students' to Job? The query will be much easier after that. — antonio_antuan
– antonio_antuan, Commented Jul 10, 2016 at 17:45
Because an informant chooses the maximal amount of students he wants to serve. Students should be placed on a queue (for new informants with the same job) if no more informants with the full informant's job are available. I know the query is rather complex, maybe I'll just set a flag if a job is open for queuing. Students choose informants in the first place, and they need to know whether an informant is full. If that is the case, they are placed in a queue for the informant's job. — Y4sper
– Y4sper, Commented Jul 10, 2016 at 18:33
Well, what's next? I think that you can place the column max_students into jobs table. If job_id is not unique in informants, well, you can sum them into one row in jobs table. You do anyway, but now - by SUM into your query. I don't see necessity of being informants table. — antonio_antuan
– antonio_antuan, Commented Jul 10, 2016 at 19:44

Y4sper · Accepted Answer · 2016-07-10 18:47:40Z

2

I was close to having the right answer. The piece that was missing is an as_scalar method on both subqueries. So the final query is:

db.session.query(Job.id).filter( \
    db.session.query(db.func.sum(Informant.max_students)). \
        filter(Informant.job_id == Job.id).as_scalar() <= \
    db.session.query(db.func.count(1)).select_from(queues). \
        filter(queues.c.job_id == Job.id).as_scalar())

answered Jul 10, 2016 at 18:47

Y4sper

1271 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RazerM Over a year ago

You can mark your own answer as the correct solution.

Collectives™ on Stack Overflow

SQLAlchemy subquery comparison

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related