DBIx Class Concatenating columns

epoch1 has asked for the wisdom of the Perl Monks concerning the following question:

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...
[download]

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
    });
[download]

Can anyone tell me how I can do this?

Cheers

Comment on DBIx Class Concatenating columns Select or Download Code

Replies are listed 'Best First'.
Re: DBIx Class Concatenating columns by Corion (Patriarch) on Mar 06, 2017 at 16:59 UTC
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` [download] 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 });` [download]	[reply] [d/l] [select]
Re: DBIx Class Concatenating columns by thanos1983 (Parson) on Mar 06, 2017 at 16:49 UTC
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'], } );` [download] 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. Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^2: DBIx Class Concatenating columns by hippo (Archbishop) on Mar 06, 2017 at 17:04 UTC
An SQL JOIN is not a concatenation.	[reply]
Re: DBIx Class Concatenating columns by Anonymous Monk on Mar 06, 2017 at 18:02 UTC
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.	[reply]