[storm] error on ResultSet.count( ) after group_by( )

[James Henstridge]

On Fri, Jan 23, 2009 at 4:02 AM, Eduardo Willians <edujurista at gmail.com> wrote:
> Ok, if this is project decision is ok for me, I thought something was
> wrong, and actually I see no reason for that, even with the point you
> exposed, but I'm not a geek.
>
>> You later try to group on one column (Ctb.op_id) which I assume is not
>> the primary key.  If you have two rows with the same value for op_id
>> but different primary keys, then it is ambiguous as to what should be
>> returned.
>
> By the way, let me ask. I used group_by( ) because I saw that I would
> get the result I want, but reading your explanation seems that
> group_by( ) doesn't work for my purpose. Here is the thing:
>
> class Ctb(object):
>    __storm_table__ = 'credtb'
>    __storm_primary__ = ("op_id", "sequent")
>    op_id = Int( )
>    sequent = Int( )
>    nominal = Float( )
>    expiration = Date( )
>    spc = Bool( )
>
> result = store.find(Ctb).order_by(Ctb.expiration)
>
> The result has many reapted 'op_id' values. I want filter result to
> remain only one value for each 'op_id' and this row must be the lowest
> 'expiration' by each op_id value.

Okay.  The SQL you were generating won't do that though (you can use
storm.tracer.debug(True) to see the statements being used).

> Is there a technical way to do it?

It'd probably require a sub-select.  In SQL terms, probably something like:

select * from credtb where (op_id, expiration) in
  (select op_id, min(expiration) from credtb group by op_id);

(this is assuming that expiration values are unique for particular
op_id values).  I guess this could be expressed using Storm as:

from storm.expr import In, Min, Select

subselect = Select(columns=[Ctb.op_id, Min(Ctb.expiration)],
tables=[Ctb], group_by=Ctb.op_id)
result = store.find(Ctb, In((Ctb.op_id, Ctb.expiration), subselect))

You can probably modify this to fit your needs.

James.