Re: Re: in-line select for counting purpose
On Wed, Mar 30, 2011 at 6:56 PM, Massimo Di Pierro
<massimo.dipierro@...> wrote:
> yes. I do not know how to do it with one query.
>
> On Mar 30, 3:18 pm, Joaquin Orbe <joaquino...@...> wrote:
>> On Sat, Mar 26, 2011 at 6:30 PM, Massimo Di Pierro
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> <massimo.dipie...@...> 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...@...> 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.
RSS Feed