Re: [web2py] Re: in-line select for counting purpose
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 massimo@gmail.com javascript: 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. --
Re: [web2py] Re: in-line select for counting purpose
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.
Re: [web2py] Re: in-line select for counting purpose
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.
[web2py] Re: in-line select for counting purpose
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.
[web2py] Re: in-line select for counting purpose
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.
