[Dbix-class] Column aliases and the ambiguity of 'as'

[Matt S Trout]

Andrew Bramble wrote:
> Hello All,
> 
> I'm still playing around with DBIC but lovin it. 
> As pointed out to me today (thanks castaway) in #dbix-class , the 'as' 
> attributes to a resultset search have no bearing on the query used , only on 
> the returned objects which are give an accessor method described by the 'as' 
> attribute. I kicked myself when going back to read the BIG hint in the 
> ResultSet docs that say 'it will fail miserably' when used in this way. 
> Frustrating because the below search would be just great except for the fact 
> that the select columns cannot possible be referred to in the 'having' 
> clause. I had a crack at resolving this myself today in 
> DBIx::Class::Storage::DBI but it all fell down in _recurse_fields 

Don't worry about this until the refactored query generator lands in the 
subselect branch, too many dragons.

> the select produced by the below search looks like
> SELECT id , IFNULL(MIN(rights.permit,0)), 
> IFNULL(MIN(inherited_rights.permit,0)) FROM ......
> 
> When it would be preferred to have 
> SELECT id AS id, IFNULL(MIN(rights.permit,0)) AS explicit_allow   .. .etc
> 
> I realise it is late and am starting to babble. 
> Would this type of behaviour be useful to anyone else ? 
> 
> my $m= $s->resultset('Meta')->search( undef,
>     {
>             join=>[ qw/rights inherited_rights/ ],
>             select=>[
>                 'id',
>                 { ifnull=>[ {min=>'rights.permit' },,0 ] } ,
>                 { ifnull=>[ {min=>'inherited_rights.permit'}, 0 ]  },
>             ],
>             as => [
>                 'id', 'explicit_allow', 'inherited_allow'
>             ],
>             order_by=>'explicit_allow',
>             group_by=>[qw/me.id/],
>             having=>{
>                  explicit_allow=>1,
>              }
>     }
> );

Try having => \"IFNULL(MIN(rights.permit,0))" etc.

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +