> On Oct 22, 2014, at 11:36 AM, Guido Winkelmann > <[email protected]> wrote: > > Hi, > > How can I put a subquery that yields exactly one value inside the select part > of the query without joining the subquery like another table? > > I have a table jobs and a table tasks. Every job has got any arbitrary number > (zero or more) of tasks. Tasks can be in one of four states: "queued", > "running", "done" and "failed". When querying the jobs table, I want to get > the counts of "queued", "running", "done" and "failed" tasks for every job, > and I want to be able to sort the output by any of those counts. The SQL I > want would look like this: > > ======= > SELECT > jobs_1.id AS jobs_1_id, > <more columns from jobs> > (SELECT count(*) FROM tasks t WHERE t.job_id=jobs_1.id AND t.state IS NULL) > AS t_queued, > (SELECT count(*) FROM tasks t WHERE t.job_id=jobs_1.id AND t.state=105) AS > t_running, > (SELECT count(*) FROM tasks t WHERE t.job_id=jobs_1.id AND t.state=106) AS > t_done, > (SELECT count(*) FROM tasks t WHERE t.job_id=jobs_1.id AND t.state=107) AS > t_failed > FROM > jobs AS jobs_1 > ORDER BY > t_running DESC; > ======
these are column-level, scalar subqueries (see http://docs.sqlalchemy.org/en/rel_0_9/glossary.html#term-subquery) and these are formed from a core select using as_scalar() - see http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#scalar-selects. The same idea works with the ORM at the Query level by passing a select() to session.query(), or if you already have a query() you can use its analogous as_scalar() method: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html?highlight=query.as_scalar#sqlalchemy.orm.query.Query.as_scalar. Basically, create a select() or query() that you want to be treated as a column, call as_scalar() or label() on it, and that's it, it's a column. Add it to the list of things you're querying from. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
