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

Question

Does anyone know what DBIx :: Class is the equivalent of an SQL query, for example:

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 solve complex queries is to define them in DBIx :: Class :: ResultSource :: View like this:

 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 usual, you will call dbix :: classes, and you will get a DBIx :: Class :: ResultSet object (which will not allow updating or deleting):

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

The good thing about this approach is that it allows you to create complex queries (for example, when you have several complex joins or unions, sub selects, etc.) that will be hidden from your code in ResultSource :: View, so you are hiding the combination of SQL syntax and objects

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 this to DBIx :: Class.