[Dbix-class] search / select query (was Re: Hi All, First Question )

[Steven Mackenzie]

Jess Robinson wrote:

>On Tue, 17 Oct 2006, tiziano at e-tip.net wrote:
>Hi All,
>
>>
>>'ve got a table, called estimate,containing 4 fields : id, ref, revision,
>>status.
>>id is the primary key,
>>ref is the number of the estimate in format (number/year)
>>revision is a int that specifies the revision of the estimate.
>>Now... in table can be more than one rows with same ref but several
>>revision numbers
>>I've done something like resultset({ %search },{ group_by => [qw /ref /]}
>>but in some cases this give me back the row with the lowest id for that
>>group.
>>Example:
>>1,1/2006,0,0  <--- this is the row returned by the query
>>2,1/2006,1,0
>>3,1/2006,3,0
>>4,1/2006,4,0
>>
>>but... i need the one with id = 4
>>    
>>
>
>Use select => and instead of just the plain columns, have: { max => 
>'revision'} for that field.. which will get you the maximal revision 
>number per ref.
>
>  
>

I had solved a simillar problem by doing something like

      $most_recent_details_row =
        $data->resultset('MarketedPropertyDetailHistory')->search_literal
        (
          "ref = ? and revision =
            (select max( revision )
             from MarketedPropertyDetailHistory
             where ref = me.ref
             group by ref)",
             $ref
        )->single();

Jess, Thanks for your example, which makes sense if the group by is
retained.

However,  the doc for select=> gives this example:
$rs = $schema->resultset('Employee')->search( undef, {
  select => [
    'name', { count => 'employeeid' }, { sum => 'salary' }
  ]
});

Should this have a group by, or does SQL::Abstract just guess the group
by that you need?

Steven