0

I have made the following query

select
    id,
    name, 
    (select count(*) from users where company_id=companies.id) as num_candidates, 
    (select count(*) from users where company_id=companies.id and status=1) as num_evaluating ,
    (select count(*) from users where company_id=companies.id and created_at between '9/12/2019' and '9/19/2019') as num_last_week 
    from companies order by num_candidates DESC limit 10;

Specifically I'm having issue with the non standard columns that are taking a count. Currently I have roughly as a test (based on cobbling some examples together)

num_candidates = db.session.query(models.User.id).filter(models.User.company_id == models.Company.id).count().label("num_candidates")

query = db.session.query(models.Company.id, 
                     models.Company.name,
                     num_candidates).order_by("num_candidates").all()

But this of course gives me errors. Any ideas how I should properly construct the SQL Alchemy?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
J. Finn
  • 129
  • 1
  • 11
  • "But this of course gives me errors"---those error messages probably contain helpful information. What do they say? Please read [ask]. – ChrisGPT was on strike Sep 19 '19 at 16:22
  • `Query.count()` executes the query and returns an integer. My guess is that your error is an AttributeError: 'int' object has no attribute 'label' or such. The "non-standard columns" are scalar subqueries. Please see how to form them for example here: https://stackoverflow.com/questions/37392773/how-to-convert-sql-scalar-subquery-to-sqlalchemy-expression, https://stackoverflow.com/questions/43654336/generate-sql-with-subquery-as-a-column-in-select-statement-using-sqlalchemy – Ilja Everilä Sep 19 '19 at 17:22

0 Answers0