[Dbix-class] DBIx::Class - Trying to get column names from a
$rs or $row
Howe, Tom (IT)
Tom.Howe at MorganStanley.com
Thu Nov 13 10:57:08 GMT 2008
Hi Matt,
Thanks for the response, however neither solutions are really satisfactory.
I'll give you a summary of what ive been doing and how I've tackled it.
Essentially Ive been creating a reporting framework to allow users to create reports on various datasets held in sqlite databases.
The idea is that users can create reports on the raw data or based on existing reports.
So the base reporting module looks as follows,
this is then subclassed by a base report for each db table
Which in turn is subclassed by user created reports.
---------------------------------------------------------------------
package Reporting::View;
use Moose::role;
has 'schema' => ( is => 'rw',lazy=>1, default => sub {
my $self = shift;
return Reporting::DB->connect('dbi:SQLite:'.$self->dbSource);
});
#
# default resultset, reports can manipulate
#
has 'resultset' => ( is => 'rw',lazy=>1, default => sub {
my $self = shift;
return $self->schema->resultset($self->dbTable);
});
#
# post processes the resultset
#
has 'processedResultset' => ( is =>'rw', lazy=>1, default => sub {
my $self = shift;
my $rs = $self->resultset;
# apply search /paging
# build whereclaus from cgi params
my $whereClause = map { ... } $self->cgi->params;
$rs = $rs->subselect({ $whereClaus }.. { paging..} ); # search on resultset
#
# add comments to report
$self->schema->storage->dbh->do(qq/ATTACH DATABASE "comments.db" as comments/);
$rs->result_class->might_have( comments => 'Reporting::Comments', { join claus });
$rs = $rs->subselect({ comments_fields}, { join => comments }.. ); # get comments for the report
.. Other post processing that may add/remove columns like delta between dates..
return $rs;
});
#
# called by Template Toolkit
#
sub createReport {
my $rs $self->processedResultset;
my @cols = $rs->columns();
my @rows;
while (my @row = $rs->cursor->next) {
push @rows, \@row;
}
# return a table object that can be rendered into html
return Table->new(\@cols, \@rows);
}
=================================================
A base report that displays raw data from Foo.db
------------------------------------------------
package Reporting::View::Foo;
use Moose;
with Reporting::View;
sub dbSource { 'Foo' };
sub dbTable { 'FooTable' };
sub reportName {' Raw foo report' };
=================================================
A sub report that displays only some data from Raw report
------------------------------------------------
package Reporting::View::Foo::View1;
use Moose;
extends Reporting::View::Foo;
sub reportName {' sub report of foo'};
around 'resultset' => sub {
my ($next,$self) = @_;
$self->$next
->search( { col1 => { in =>[]} }, { select => [ col1 as xxx col2 as yyy ] });
}
=================================================
A sub report that displays View1 using customised col names
------------------------------------------------
package Reporting::View::Foo::Raw::View1::Custom
use Moose;
extends 'Reporting::View::Foo::Raw::View1';
sub reportName {' sub report of foo'};
around 'resultset' => sub {
my ($next,$self) = @_;
$self->$next
->subselect( { xxx => { in =>[..]} }, { select => [ 'col1 as foo', 'col2 as bar'] });
}
=================================================
A sub report that displays customer report in a different layout
----------------------------------------------------------------------
package Reporting::View::Foo::Raw::View1::Custom::Alternate;
use Moose;
extends 'Reporting::View::Foo::Raw::View1::Custom';
sub reportName {' sub report of foo'};
around 'resultset' => sub {
my ($next,$self) = @_;
$self->$next
->roundMins(30, 'time', @groupbycols} #adjusts time value to 30 min intervals
->groupBy('count(*)','count', @groupbycols} #group by time col to show counts by 30 min interval
->pivot('time','0','count, at groupbycols; #turn the time col into multiple columns
}
----------------------------------------------------
The essense of this is to have a db that looks like
| Col1 | Col2 | Time |
And from it, create reports that looks like
| Col1 | Col2 | Time | Count |
And then something like this.
| myCol1 | myCol2 | 00:00 | 00:30 | 01:00 | ... | 23:30 |
All the user needs to do to create the report is create a module with relavent around 'resultset' method - later this may become a yaml file.
-------------------------------------------------------
Ive managed to get all this working - but ive had to hack the following
DBIx::Class::Storage::DBI
- created a selectSQL() method that returns the current SQL string and
- allows passing of 'compound' key for doing Unions/Excepts etc.
DBIx::Class::Resultset
- created subselect() that uses the selectSQL() above to populate the 'from' key.
- added 'compound' key to search so that you can supply compound => 'UNION => selectstatement'';
- added columns() to retrieve the columns from the current resultset.
- added groupBy(), pivot() and a couple of other helper methods that do useful SQL transformations
------------------------------------------------------------
It all ends up with some rather scary SQL - but it works rather nicely ;-)
SELECT "only_in", "db_server", "project", "00:00", "01:00", "02:00", "03:30", "04:00", "04:30", "05:00", "05:30", "06:00", "06:30", "07:00", "17:30", "19:00", "21:00", "21:30", "22:30" FROM (SELECT "only_in", "db_server", "project", sum("00:00") as "00:00", sum("01:00") as "01:00", sum("02:00") as "02:00", sum("03:30") as "03:30", sum("04:00") as "04:00", sum("04:30") as "04:30", sum("05:00") as "05:00", sum("05:30") as "05:30", sum("06:00") as "06:00", sum("06:30") as "06:30", sum("07:00") as "07:00", sum("17:30") as "17:30", sum("19:00") as "19:00", sum("21:00") as "21:00", sum("21:30") as "21:30", sum("22:30") as "22:30" FROM (SELECT "only_in", "db_server", "project", (case when time = "00:00" then count end) as '00:00', (case when time = "01:00" then count end) as '01:00', (case when time = "02:00" then count end) as '02:00', (case when time = "03:30" then count end) as '03:30', (case when time = "04:00" then count end) as '04:00', (case when time = "04:30" then count end) as '04:30', (case when time = "05:00" then count end) as '05:00', (case when time = "05:30" then count end) as '05:30', (case when time = "06:00" then count end) as '06:00', (case when time = "06:30" then count end) as '06:30', (case when time = "07:00" then count end) as '07:00', (case when time = "17:30" then count end) as '17:30', (case when time = "19:00" then count end) as '19:00', (case when time = "21:00" then count end) as '21:00', (case when time = "21:30" then count end) as '21:30', (case when time = "22:30" then count end) as '22:30' FROM (SELECT "only_in", "db_server", "project", "time", count(*) as count FROM (SELECT "only_in", "db_server", "project", "time" FROM (SELECT '2008-11-13' as only_in, "db_server", "project", "time" FROM (SELECT "db_server", "project", "time" FROM (SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time) me EXCEPT SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM old.DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time) me) me UNION SELECT '2008-11-11' as only_in, "db_server", "project", "time" FROM (SELECT "db_server", "project", "time" FROM (SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM old.DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time) me EXCEPT SELECT db_server, project, strftime("%H:%M", strftime("%H:00",time), "+" || (strftime("%M",time)*1/30*30) || " minutes") as time FROM DBusage me WHERE ( strftime('%H',time) IS NOT NULL ) GROUP BY db_server, project, time) me) me GROUP BY db_server, project, time) me) me GROUP BY db_server, project) me WHERE ( ( ( ( ( db_server LIKE '%CDDB%' ) OR ( project LIKE '%CDDB%' ) ) ) ) ) LIMIT 100
-----------------------------------------------------------
Since our version 0.8 is broken (no useful errors), ive done this using 0.70050.
My code is pretty hacky so not worth submitting but If you are interested in what I did I can provide more info.
Regards, Tom
> -----Original Message-----
> From: Matt S Trout [mailto:dbix-class at trout.me.uk]
> Sent: 12 November 2008 20:40
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] DBIx::Class - Trying to get column
> names from a $rs or $row
>
> On Tue, Oct 14, 2008 at 07:59:44PM +0100, Howe, Tom (IT) wrote:
> > Hi, first post here
> >
> > I have a question regarding getting column names from a
> DBIx ::Class $resultset or $row.
> >
> > I have a DBIx::Class resultset which I'll call $rset
> >
> > I need to get the column names of the resultset in the
> correct order.
> >
> > I was advised by a coleague to use $rset->result_source->columns;
> >
> > This have me the column names of the table that was the
> original source of the resultset which initially worked ok.
> >
> > However, now that I am doing some selects on the table the
> resultset
> > only contains a subset of the table columns
> >
> > I tried doing
> >
> > my $row = $rset->first;
> > %row = $row->get_columns;
>
> my @cols = grep { exists $row{$_} } $rset->result_source->columns;
>
> or, really, if you're supplying a subset of columns, just
> hang onto the array you supplied for that.
>
> > my @cols = keys %row;
> >
> > But now I dont have them in the correct order.
> >
> > Is there anyway to get an (ordered) list of the column
> names from the
> > $resultset or the $row
>
> --
> Matt S Trout Need help with your Catalyst or
> DBIx::Class project?
> Technical Director
> http://www.shadowcat.co.uk/catalyst/
> Shadowcat Systems Ltd. Want a managed development or
> deployment platform?
> http://chainsawblues.vox.com/
> http://www.shadowcat.co.uk/servers/
>
> _______________________________________________
> 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
>
--------------------------------------------------------
NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20081113/bc7d7bba/attachment-0001.htm
More information about the DBIx-Class
mailing list