CODEWITHSUNDEEP
perlsortingdbix-class
dequid
dequid

Reputation: 441

DBIx::Class: How can I sort on multiple substrings of a column?

I have a SQLite-database with table with a document number following this schema:

16-145-45
16-127-30
16-141-42
16-122-14
15-090-04
15-089-15
15-089-05

I'd like to sort the ResultSet on the first and last part of the number, like this. First, all documents starting with the highest two-digit prefix (16) sorted by the last 2 digits and then the same with the next block, and so on.

16-145-45
16-141-42
16-127-30
16-122-14
15-089-15
15-089-05
15-090-04

Is there a way to do this in DBIx::Class with some sort of custom order_by clause, or what would be the approach?

I have tried the following, which does not work, because the middle part of the number is also considered for sorting:

my @rs = $self->search(undef,
    {
        order_by => { -desc => 'me.number' }
    }
);

Upvotes: 2

Views: 626

Answers (3)

nwellnhof
nwellnhof

Reputation: 33658

If you want the database to sort the results, you have to use literal SQL.

Here's an example for Postgres (I added a space after the backslash to fix the syntax highlighting):

my @rs = $self->search(undef,
    {
        order_by => \ "split_part(number, '-', 1) || split_part(number, '-', 3) DESC",
    }
);

Or, by creating an output column with the +select result set attribute:

my @rs = $self->search(undef,
    {
        '+select' => [
            { sort_key => \ "split_part(number, '-', 1) || split_part(number, '-', 3)" },
        ],
        '+as' => [ qw(sort_key) ],  # Make sort key accessible from DBIC.
        order_by => { -desc => 'sort_key' },
    }
);

Another approach is to retreive the whole unsorted result set, and sort it on the client side. DBIC doesn't have any specific features to help you with that, so simply use Perl's sort function.

Upvotes: 2

Borodin
Borodin

Reputation: 126762

You need to extract additional columns from the result set which are equal to the value of the function that you want to sort by. Then you can just put those columns in an order_by clause as normal

This assumes that your document number field is called docnum. It fetches all the columns from Table plus the two substrings of docnum called docnum1 and docnum3

my $rs = $schema->resultset('Table')->search(undef,
    { 
        '+select' => [
            { substr => [ 'docnum', 1, 2 ], -as => 'docnum1' },
            { substr => [ 'docnum', -2 ],   -as => 'docnum3' },
        ],
        order_by => [ { -desc => 'docnum1' }, { -desc => 'docnum3' } ],
    }
);

Upvotes: 1

dequid
dequid

Reputation: 441

Since the answer from @nwellnhof works like a charm, I just wanted to provide the corresponding syntax for SQLite, which does not know the split_part() function.

# SQL for filtering the doc number in SQLite

my @rs = $self->search(undef,
    {
        order_by => \ "SUBSTR(me.number, 1, 2) || SUBSTR(me.number, -2, 2) DESC"
    }
);

Upvotes: 1

Related Questions