What is the DBIx::Class syntax for the CASE WHEN ... THEN SQL syntax?

Question

Does anyone know what's the DBIx::Class equivalent of an SQL query such as:

SELECT cdr_id,
CASE WHEN service_id = 'GPRS' THEN 'KB' WHEN service_id = 'SMS' THEN 'SMS' END AS unit
FROM ...

Thanks

Answer 1

my $rs = $schema->resultset( 'table' )->
    search_rs( {} ,
               { '+columns' => {
                     unit => 
                         \do { "CASE WHEN me.service_id='GPRS' THEN 'KB' " .
                                "WHEN me.service_id='SMS' THEN 'SMS' END" }
               } ) ;

Something along this line should work.

Answer 2

Another way to deal with complex queries is to define them in a DBIx::Class::ResultSource::View like so:

package My::Schema::Result::ComplexQuery
use strict;
use warnings;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
__PACKAGE__->table('tablename');
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(
    q[
      SELECT cdr_id, 
      CASE WHEN service_id = 'GPRS' THEN 'KB' WHEN service_id = 'SMS' THEN 'SMS' END AS unit
      FROM table
    ]
);

then you can call it as you would call dbix::classes normally and you'll get a DBIx::Class::ResultSet object (which will not allow updates or delete, though):

my $pruned_cdr = $schema->resultset('ComplexQuery')->search({}, { ... });

The nice thing about this approach is that it allows complex queries (like when you have multiple complex joins or unions, sub selects etc) to be hidden from your code into a ResultSource::View, so you hide the mix of SQL syntax and objects