9

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

smonff
  • 3,399
  • 3
  • 36
  • 46
galli2000
  • 478
  • 5
  • 14

3 Answers3

7
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.

Frew Schmidt
  • 9,364
  • 16
  • 64
  • 86
dgw
  • 13,418
  • 11
  • 56
  • 54
  • 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? – galli2000 Feb 08 '12 at 22:50
  • 1
    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. – dgw Feb 08 '12 at 22:55
  • 1
    @galli2000 DBIC isn't just a SQL Generator. It also generates useful objects, prunes join conditions, and more. – Frew Schmidt May 03 '13 at 12:46
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

ashraf
  • 537
  • 7
  • 16
-1

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.

zgpmax
  • 2,777
  • 15
  • 22