Hi all, I have exactly the same problem and I want to know if somebody found a way to do in-line selects ? Im not really keen to use executeSql.
Btw, does anybody know why when I .json my query results, aliases (made with .with_alias() are not used ? (sorry for this question not really linked to the first post) Thank you. Vincent. On Friday, April 1, 2011 3:49:33 PM UTC+13, Joaquin Orbe wrote: > > On Wed, Mar 30, 2011 at 6:56 PM, Massimo Di Pierro > <[email protected] <javascript:>> wrote: > > yes. I do not know how to do it with one query. > > > > On Mar 30, 3:18 pm, Joaquin Orbe <[email protected]> wrote: > >> On Sat, Mar 26, 2011 at 6:30 PM, Massimo Di Pierro > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> <[email protected]> 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 <[email protected]> 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. > > --
