Re: TEMP column created via set_sql and MySQL DATE_FORMAT function returns 0 in Class::DBI but valid data from command-line

[prev] [thread] [next] [Date index for 2005/02/15]

On Mon, 2005-02-14 at 14:31 -0800, Terrence Brannon wrote:
> I am using mysql's DATE_FORMAT function to make a datetime column
> human-readable.

The common approach is make this into a DateTime or Time::Piece object
and use the formatting those provide.

> Cutting and pasting the SQL in a set_sql statement from
> a Class::DBI package works fine. However, when I attempt to access this
> column as a TEMP column, the date comes back as double zeros.

I don't see anything obviously wrong.  You probably need to do some more
debugging here and see what values are getting passed to
_ids_to_objects() when you call your search method.

> __PACKAGE__->table('battle_t'); 
> __PACKAGE__->columns( Primary => 'battle_id' ); 
>  
> __PACKAGE__->columns( 
>   Essential => qw/winning_player_id losing_player_id creation_datetime
> battle_result/ 
>  ); 
>  
> __PACKAGE__->columns( 
>   TEMP => qw/battle_date/ 
>  ); 
>  
> __PACKAGE__->set_sql(recent_results => qq{ 
>    SELECT DATE_FORMAT(creation_datetime,'%b %d') as battle_date,
> battle_t.*, wp.screen_name as winning_player,lp.screen_name as
> losing_player  
>      FROM battle_t INNER JOIN player_t wp ON
> (winning_player_id=wp.player_id)  
>           INNER JOIN player_t lp ON (losing_player_id=lp.player_id) 
>  ORDER BY creation_datetime DESC 
>     LIMIT 40 
> });

One piece of advice here:
You are basically lying to Class::DBI about the columns in your table
here in order to get it treat what is really a reporting query as if it
were a table.  This will probably cause trouble somewhere.  Class::DBI
is generally not a good tool for this sort of reporting.

- Perrin