join a subquery in peewee

Question

I'm stuck in converting below query into peewee :

SELECT bID, taskCount 
FROM
    (
    SELECT
        block.id AS bID,
        Count( task.id ) AS taskCount 
    FROM
        block
        LEFT JOIN task ON task.block_id = block.id 
    WHERE
        block.id NOT IN ( ( SELECT task.block_id FROM task WHERE task.channel_id = '1' ) ) 
    GROUP BY
        block.id 
    ) AS A
    INNER JOIN ( SELECT task.block_id FROM task GROUP BY task.block_id ) AS B

I know it can be done but I can't write complete solution because I don't know what to do with aliases (BID,TaskCount) and then where exactly use joins !

here's what I wrote , obviously it's not working:

subquery1 = (Block.select(Block.id.alias('BID'),fn.COUNT(Task.id).alias('TaskCount'))
 .join(Task,JOIN.LEFT_OUTER,Task.block_id == Block.id)
 .where(Block.id.not_in(Task.select(Task.block_id).where(Task.channel_id=='1')))
 .group_by(Block.id)
 .alias('subquery1'))

subquery2 = (Task.select(Task.block_id).group_by(Task.block_id) )
query = subquery1.select(subquery1.c.BID,subquery1.c.TaskCount)
 .join(subquery2, on=(subquery1.c.BID == subquery2.c.block_id))

EDIT : I fixed some errors. But now what should I get from query object ?!! If I print query's rows I 'll face with :

peewee.InternalError: (1054, "Unknown column 'subquery1.BID' in 'field list'")

Answer 1

Finally, after a little up and down I noticed a problem, there was an extra part in my code that I deleted it.

subquery2 = (Task.select(Task.block_id).group_by(Task.block_id) )
query = subquery1.select(subquery1.c.BID,subquery1.c.TaskCount).join(subquery2, on=(subquery1.c.BID == Task.block_id))

and the correct part is :

subquery1 = (Block
.select(Block.id.alias('BID'),fn.COUNT(Task.id).alias('TaskCount')
                 ).join(Task,JOIN.LEFT_OUTER,Task.block_id == Block.id)
                 .where(Block.id.not_in(Task.select(Task.block_id).where(Task.channel_id=='1')))
                 .group_by(Block.id)
                 .alias('subquery1')).order_by(fn.COUNT(Task.id))