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

Dimphia

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.

Yaupon

This solution works indeed. The only thing I regret is that it mixes SQL syntax with objects use. So, if we are forced to keep some SQL bits, is there a real interest in using DBIx::Class over simple DBI? – Dimphia

There is no other way I know of to include your desired case expression. It is not included in the object syntactic sugar of DBIx::Class. – Yaupon

@Dimphia DBIC isn't just a SQL Generator. It also generates useful objects, prunes join conditions, and more. – Faythe

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

Mescaline

Answer 3

Create a table "service_units" populated with:

service | unit
--------+-----
GPRS    | KB
SMS     | SMS

then

SELECT
    cdr.cdr_id, service_units.unit
FROM
    cdr INNER JOIN service_units ON cdr.service_id = service_units.service

then translate that into DBIx::Class speak.

Auricular