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

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

=20

> -----Original Message-----
> From: Perrin Harkins [mailto:perrin@xxxx.xxx]=20
>=20
> 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=20
> > human-readable.
>=20
> The common approach is make this into a DateTime or=20
> Time::Piece object and use the formatting those provide.

I know "don't optimize before it's slow", but I tend to try to get as
much out of the database server as I can for speed and laziness reasons
:)

> > __PACKAGE__->columns(=20
> >   TEMP =3D> qw/battle_date/
> >  );
> > =20
> > __PACKAGE__->set_sql(recent_results =3D> qq{=20
> >    SELECT DATE_FORMAT(creation_datetime,'%b %d') as battle_date,=20
> > battle_t.*, wp.screen_name as winning_player,lp.screen_name as=20
> > losing_player
> >      FROM battle_t INNER JOIN player_t wp ON
> > (winning_player_id=3Dwp.player_id) =20
> >           INNER JOIN player_t lp ON=20
> (losing_player_id=3Dlp.player_id) =20
> > ORDER BY creation_datetime DESC
> >     LIMIT 40
> > });
>=20
> One piece of advice here:
> You are basically lying to Class::DBI about the columns in=20
> your table here in order to get it treat what is really a=20
> reporting query as if it were a table.  This will probably=20
> cause trouble somewhere.  Class::DBI is generally not a good=20
> tool for this sort of reporting.
>=20

Hmm, what I am doing here is very similar to the example in perldoc
Class::DBI

        Music::Artist->set_sql(most_cds =3D> qq{
                SELECT artist.id, COUNT(cd.id) AS cds <<<<-----
synthetic column!
                  FROM artist, cd
                 WHERE artist.id =3D cd.artist
                 GROUP BY artist.id
                 ORDER BY cds DESC
                 LIMIT 10
        });

        my @artists =3D Music::Artist->search_most_cds();

If you also need to access the 'cds' value returned from this query, the
best approach is to declare 'cds' to be a TEMP column. (See
Non-Persistent Fields below).

Where
http://cpan.uwinnipeg.ca/htdocs/Class-DBI/Class/DBI.html#nonpersistent_f
ields
Shows us:

Non-Persistent Fields

        Music::CD->columns(TEMP =3D> qw/nonpersistent/);

If you wish to have fields that act like columns in every other way, but
that don't actually exist in the database (and thus will not persist),
you can declare them as part of a column group of 'TEMP'.