DBIx::Class Temporary column

Question

I am using DBIx::Class and I have a query like this:

$groups = $c->model('DB::Project')->search(
{ "sessions.user_id"=>$c->user->id,done_yn=>'y' },
{
    select   => ["name", "id",\'SUM(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time)) as total_time'], #\''
    join         => 'sessions',
  }
);

I'd like to be able to get the value of SUM(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time)), but because this is not a real column in the table, referencing total_time for a DBIx::Class::Row object doesn't seem to work. Does anyone know how I can get these temporary columns? Thanks!

I know what you mean. There are upsides and downside, but I feel like I'm comfortable enough with SQL that this doesn't really help too much. It's just that I'm writing a catalyst app and so many things are integrated with `DBIx::Class` that I basically feel like I have to use it. — srchulo, Aug 14 '12 at 22:30

score 2 · Accepted Answer · answered Aug 14 '12 at 23:00

The select docs describe perfectly how to achieve what you're trying to accomplish. It's also recommended to avoid literal SQL when possible, you can use { sum => \'UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time)' } instead. The 'as' in the literal SQL isn't required to give the column a name, you have to use either the as search attribute or better the columns shortcut instead of select+as.

DBIx::Class Temporary column

1 Answers1