Getting result rows as array with DBIx::Class?

Question

Is it possible to get a plain arrayrefs as rows of a DBIx::Class search?

I can do this

my $rs = $db->resultset("SomeTable")->search({});

my $results = $dbh->selectall_arrayref("select * from " . $rs->as_query->$*->[0]);

and it works, but it looks barbarous.

The benefit is I have the columns in order, and it's column-name independent, which in case of a crosstab is nice.

Are there any cleaner approaches? and no, the HashRefInflator result class (or any hash-based approach) won't work, as I have (without some other trick I am not aware of) no guarantee of column order - as they are hashes, no guarantee of key ordering.

Answer 1

I recommend using a new method in your Result class here, rather than one in your ResultSet class. I say this for reasons of scalability: you probably don't want to inflate your entire resultset all at once because that would only be feasible for very tiny resultsets that all fit into memory at once.

For example, let's assume there's an as_arrayref result class method that does the thing you're asking for and that $some_resultset holds your resultset object. Then if you really wanted to have all those arrayrefs at once, you could simply do:

my @many_rows = map { $_->as_arrayref } $some_resultset->all; 

Or

my $ref_to_many_rows = [ map { $_->as_arrayref } $some_resultset->all ];

More likely you will step through your resultset one result at a time, and produce an arrayref of values per result object one at a time as you go.

For example, let's assume there's an as_arrayref method that does the thing you're asking for and that $some_resultset holds your resultset object.

while (my $result = $some_resultset->next) {
    my $arrayref = $result->as_arrayref;
    printf "[%s],\n", join ", " => @$arrayref;
}

That still won't scale for large resultsets, but you can always "page" the resultset so that it does so:

use constant MAX_INCREMENTAL_RESULTSET_LIMIT => 25;

my $limit      = MAX_INCREMENTAL_RESULTSET_LIMIT;
my $count      = $resultset->count;
my $page_count = 1 + ($count / $limit);

for (my $page = 0; $page < $page_count; $page++) {
    my $paged_rs = $resultset->search(undef, { page => $page, rows => $limit });
    while (my $result = $paged_rs->next) {
        my $arrayref = $result->as_arrayref;
        printf "[%s],\n", join ", " => map { qq("$_") } @arrayref;
    }
}

That version now scales no matter how large your resultset.

So what does your as_arrayref result class method look like?

Remember that the get_columns result class method returns a pairlist of key–value pairs. That makes something like as_hashref as simple as:

sub as_hashref {
    my($db_result) = @_;
    my %hash = $db_result->get_columns;
    return \%hash;
}

STRONG WARNING! That's unsafe as written because it doesn't croak when invoked with incorrect arguments. So you should write that as:

use Carp;

sub as_hashref {
    croak "expected no arguments beyond db result invocant" unless @_ == 1;
    my($db_result) = @_;
    my %hash = $db_result->get_columns;
    return \%hash;
}

Or, if you're using subroutine signatures, then:

use feature "signatures";

sub as_hashref ($db_result) {
    my %hash = $db_result->get_columns;
    return \%hash;
}

or even just

sub as_hashref ($db_result) {
    return { $db_result->get_columns };
}

Given that the columns result class method returns a list of that result object's column names in order, your new as_arrayref result class method could just be either this with croak:

sub as_arrayref {
    croak "expected no arguments beyond db result invocant" unless @_ == 1;
    my($db_result) = @_;
    my @keys = $db_result->columns;
    my %hash = $db_result->get_columns;
    my @vals = @hash{@keys};
    return \@vals;
}

Or this with signatures:

sub as_arrayref ($db_result) {
    my @keys = $db_result->columns;
    my %hash = $db_result->get_columns;
    my @vals = @hash{@keys};
    return \@vals;
}

One thing I've done here is assume that you want the literal column values from your db, not objects automatically inflated by DBIx. That's not necessarily a good assumption, but even if that's what you want, we could make it clearer by choosing better method names.

So let's fix the name by letting you have an explicitly named as_deflated_arrayref method on the one hand or an as_inflated_arrayref method on the other. That removes all question of inflation that method names like as_hashref or as_arrayref would leave unresolved in the eyes of the reader.

That means you'd have some pair like:

sub as_deflated_hashref ($db_result) {
    return { $db_result->get_columns };
}

sub as_inflated_hashref ($db_result) {
    return { $db_result->get_inflated_columns };
}

Which leads to methods like these for the deflated version:

sub as_deflated_values ($db_result) {
    my @keys    = $db_result->columns;
    my $hashref = $db_result->as_deflated_hashref;
    my @vals    = @$hashref{@keys};
    return wantarray ? @vals : \@vals;
}

sub as_deflated_arrayref ($db_result) {
    return scalar $db_result->as_deflated_values;
}

And these for the inflated versions:

sub as_inflated_values ($db_result) {
    my @keys    = $db_result->columns;
    my $hashref = $db_result->as_inflated_hashref;
    my @vals    = @$hashref{@keys};
    return wantarray ? @vals : \@vals;
}

sub as_inflated_arrayref ($db_result) {
    return scalar $db_result->as_inflated_values;
}

I'm sure you get the general idea here, so you should now be able to adapt these various approaches to your own code.

Answer 2

You could add a method to your ResultSet class, that converts the result set to an arrayref of arrayrefs.

package My::Schema::ResultSet::SomeTable;

use strict;
use warnings;

# To ensure the columns are in a nice order
my %column_idxs = (
    column_one => 0,
    column_two => 1
);

sub to_arrayrefs {
    my $self = shift;

    my @rows;
    for ($self->all) {
        my %row = $_->get_inflated_columns;
        my @row;

        for (keys %row) {
            $row[$column_idxs{$_}] = $row{$_};
        }

        push @rows, \@row;
    }

    return \@rows;
}

1;

Then in your code you can use it like:

my $rows = $db->resultset("SomeTable")->search({})->to_arrayrefs;