FAQ
I have a table of logged data, which is too granular to browse row by
row. What I want to do is display a row per hour of data, and include
average/min/max for each field.

I can do this in SQL, using group by with a formatted timestamp field
to group per hour, along with AVG(), MIN(), MAX() on each field to get
the values I'm after. I can replicate the query in DBIx by defining
'select' and 'as'. However, I can't access these, I'm assuming because
I've given them names which don't match my DBIx::Class' defined column
names. A dump of the DBIx::Class shows the values in there as part of
_column_data.

So, for example, if my class has a column "foo", as does my DB table,
then what I want to access is foo_avg, foo_min and foo_max.

Is there an easy way to get a search resultset to auto-create the new columns?
Is there a better way to accomplish what I'm after?

I'm using DBIx::Class within Catalyst, with a MySQL DB, and Template
Toolkit for presentation.

Thanks.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

Search Discussions

  • Андрей Костенко at Sep 21, 2009 at 12:14 pm
    You can create a view for this query. e.g.
    CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;
    On Mon, Sep 21, 2009 at 10:42 AM, Trevor Phillips wrote:

    I have a table of logged data, which is too granular to browse row by
    row. What I want to do is display a row per hour of data, and include
    average/min/max for each field.

    I can do this in SQL, using group by with a formatted timestamp field
    to group per hour, along with AVG(), MIN(), MAX() on each field to get
    the values I'm after. I can replicate the query in DBIx by defining
    'select' and 'as'. However, I can't access these, I'm assuming because
    I've given them names which don't match my DBIx::Class' defined column
    names. A dump of the DBIx::Class shows the values in there as part of
    _column_data.

    So, for example, if my class has a column "foo", as does my DB table,
    then what I want to access is foo_avg, foo_min and foo_max.

    Is there an easy way to get a search resultset to auto-create the new
    columns?
    Is there a better way to accomplish what I'm after?

    I'm using DBIx::Class within Catalyst, with a MySQL DB, and Template
    Toolkit for presentation.

    Thanks.

    --
    Trevor Phillips - http://dortamur.livejournal.com/
    "On nights such as this, evil deeds are done. And good deeds, of
    course. But mostly evil, on the whole."
    -- (Terry Pratchett, Wyrd Sisters)

    _______________________________________________
    List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
    Searchable Archive:
    http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090921/d1cb00a2/attachment.htm
  • Trevor Phillips at Sep 22, 2009 at 5:35 am

    2009/9/21 ?????? ???????? <andrey@kostenko.name>:
    You can create a view for this query. e.g.
    CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;
    I could, but it's not the query I'm having a problem with - it's
    mapping it to columns within the pre-existing DBIx::Class. I'd rather
    not have to create a whole new class simply for this single query
    against my data. I can see the data in the raw object - I just can't
    access it through the normal methods.

    Reading the manual, I should be able to get the value via get_column,
    but Catalyst/TT uses the column methods instead.

    Hmmm, I *can* use get_column within TT, but that seems to defeat the
    purpose of abstracting data from presentation.

    When getting the search results, I can map the rows with
    $_->get_columns(). That seems to be the easiest solution for now.

    --
    Trevor Phillips - http://dortamur.livejournal.com/
    "On nights such as this, evil deeds are done. And good deeds, of
    course. But mostly evil, on the whole."
    -- (Terry Pratchett, Wyrd Sisters)
  • Андрей Костенко at Sep 22, 2009 at 6:43 am
    You can call method ->get_column('some_value')->min from resultset.
    Also you can create your own ResultSet and add method

    sub get_min_malue {
    shift->get_column('some_column')->min;
    }

    So you can make such query:
    $c->model('DB::Table')->get_min_value;
    $c->model('DB::Table')->search( {type => 1} )->get_min_value;

    But you will make three requests for min, max and avg functions. Or you can
    create other method in your resultset object which returns hash with needed
    values.

    sub get_stats_data{
    return { min => 1, max => 2, avg => 3}
    }

    2009/9/22 Trevor Phillips <trevor.phillips@gmail.com>
    2009/9/21 �ндрей Ко�тенко <andrey@kostenko.name>:
    You can create a view for this query. e.g.
    CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;
    I could, but it's not the query I'm having a problem with - it's
    mapping it to columns within the pre-existing DBIx::Class. I'd rather
    not have to create a whole new class simply for this single query
    against my data. I can see the data in the raw object - I just can't
    access it through the normal methods.

    Reading the manual, I should be able to get the value via get_column,
    but Catalyst/TT uses the column methods instead.

    Hmmm, I *can* use get_column within TT, but that seems to defeat the
    purpose of abstracting data from presentation.

    When getting the search results, I can map the rows with
    $_->get_columns(). That seems to be the easiest solution for now.

    --
    Trevor Phillips - http://dortamur.livejournal.com/
    "On nights such as this, evil deeds are done. And good deeds, of
    course. But mostly evil, on the whole."
    -- (Terry Pratchett, Wyrd Sisters)

    _______________________________________________
    List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
    Searchable Archive:
    http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090922/99a50ac2/attachment.htm
  • Matt Whipple at Sep 22, 2009 at 6:48 pm

    Trevor Phillips wrote:
    2009/9/21 ?????? ???????? <andrey@kostenko.name>:
    You can create a view for this query. e.g.
    CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;
    I could, but it's not the query I'm having a problem with - it's
    mapping it to columns within the pre-existing DBIx::Class. I'd rather
    not have to create a whole new class simply for this single query
    against my data. I can see the data in the raw object - I just can't
    access it through the normal methods.

    Reading the manual, I should be able to get the value via get_column,
    but Catalyst/TT uses the column methods instead.

    Hmmm, I *can* use get_column within TT, but that seems to defeat the
    purpose of abstracting data from presentation.
    You could leave the accessor abstracted by creating the function within
    the result class. Along the lines of
    sub a_min { return shift->get_column('a_min'); };
    When getting the search results, I can map the rows with
    $_->get_columns(). That seems to be the easiest solution for now.

    --
    Trevor Phillips - http://dortamur.livejournal.com/
    "On nights such as this, evil deeds are done. And good deeds, of
    course. But mostly evil, on the whole."
    -- (Terry Pratchett, Wyrd Sisters)

    _______________________________________________
    List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
    IRC: irc.perl.org#dbix-class
    SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
    Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedSep 21, '09 at 7:42a
activeSep 22, '09 at 6:48p
posts5
users3
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2021 Grokbase