DBIx Class Concatenating columns

[epoch1]

Hi Monks,

I'm trying to concatenate some columns using DBIx Class but I can't figure out how to do it.

I have a database that has separate date and time columns that I need to concatenate into a single field, Id usually use something like

select col1, col3, CONCAT(date, ' ', time) AS startTime...

Here's my select code:

  my $rs = $self->db->resultset('MODELCLASS')->search(undef, {
    select => [
        'col1', 'col2', 'date' . ' ' . 'time'
      ],
      as => [ qw/
        id title time
      /],
      rows => 10
    });

Can anyone tell me how I can do this?

Cheers

[Corion]

I believe that the "SQL abstraction" provided by DBIx::Class is just SQL::Abstract. If that is true, then you can supply function calls as in the documentation:

\$sql_string

So maybe the following works:

  my $rs = $self->db->resultset('MODELCLASS')->search(undef, {
    select => [
        'col1', 'col2', \"concat( date, ' ', time )",
      ],
      as => [ qw/
        id title time
      /],
      rows => 10
    });

[thanos1983]

Hello epoch1,

I do not know match about DBIx::Class but a possible solution to your problem could be DBIx::Class::Manual::Joining.

From the manual "USING JOINS":

  $schema->resultset('CD')->search(
    { 'Title' => 'Funky CD',
      'tracks.Name' => { like => 'T%' }
    },
    { join      => 'tracks',
      order_by  => ['tracks.id'],
    }
  );

Take a look, it might be what you are looking for.

Update: another similar question/solution with join (Can I join the column names with search_related in DBIx?).

Hope this helps.

[hippo]

An SQL JOIN is not a concatenation.

[Anonymous Monk]

If you would use plain ANSI SQL you would not have these problems. You spend more time trying to make your objects do what you what then actually getting work done, and the end result is usually harder to maintain and extend much less understand.