On Wed, Mar 30, 2011 at 6:56 PM, Massimo Di Pierro <massimo.dipie...@gmail.com> wrote: > yes. I do not know how to do it with one query. > > On Mar 30, 3:18 pm, Joaquin Orbe <joaquino...@gmail.com> wrote: >> On Sat, Mar 26, 2011 at 6:30 PM, Massimo Di Pierro >> >> >> >> >> >> >> >> >> >> <massimo.dipie...@gmail.com> wrote: >> > You an have a nested select in place of a field. >> >> > I think you want: >> > count = db.sales_order.id.count() >> > query = db.sales_order.VendorID == db.vendor.id >> > rows1 = db(query) >> > (db.sales_order.Status==1).select(db.vendor.id,db.vendor.Name,count) >> > rows2 = db(query) >> > (db.sales_order.Status==2).select(db.vendor.id,db.vendor.Name,count) >> > rows3 = db(query) >> > (db.sales_order.Status==3).select(db.vendor.id,db.vendor.Name,count) >> >> > On Mar 26, 3:57 pm, Joaquin Orbe <joaquino...@gmail.com> wrote: >> >> Hi all, >> >> >> I'm trying to get a result for this query: >> >> >> select vendor.id, vendor.Name, >> >> (select count(*) from sales_order where [Status] = 1 and vendor.id = >> >> sales_order.VendorID), >> >> (select count(*) from sales_order where [Status] = 2 and vendor.id = >> >> sales_order.VendorID), >> >> (select count(*) from sales_order where [Status] = 3 and vendor.id = >> >> sales_order.VendorID) >> >> from vendor >> >> >> and for it I do: >> >> >> countOpen = db((db.sales_order.Status==1)&(db.sales_order.VendorID == >> >> db.vendor.id)).count() >> >> countProg = db((db.sales_order.Status==2)&(db.sales_order.VendorID == >> >> db.vendor.id)).count() >> >> countClosed = db((db.sales_order.Status==3)&(db.sales_order.VendorID >> >> == db.vendor.id)).count() >> >> >> rows = db().select(db.vendor.id, >> >> db.vendor.Name,countOpen,countProg,countClosed) >> >> >> but it's not working because I get >> >> >> (1, vendor1, 1, 4, 1) >> >> (2, vendor2, 1, 4, 1) >> >> >> instead of >> >> >> (1, vendor1, 1, 4, 1) >> >> (2, vendor2, 0, 0, 0) >> >> >> It seems the result of vendor1 is also applied to vendor2 (vendor2 >> >> does not have records in sales_order table). >> >> >> Does someone know how to solve it? >> >> >> Thanks in advance, >> >> Joaquin. >> >> Hi Massimo, >> sorry for my late answer. >> >> How should I applied your suggestion? As I can understand, your code >> seems to have three row results, and in my case I need only one. Is it >> correct my understanding? >> >> Thanks, >> Joaquin.
It's ok Massimo, thanks anyway. I've solved it with an executesql statement for the moment. BTW, I've been playing with dql.py trying to support this scenario but the code is a bit messy to publish here. I could get it work but I don't know if this could have impact in another place (I'm still a newbie). I'll post it here once I apply a cleaning on it. Thanks for all, Joaquin.
