View | Details | Raw Unified | Return to bug 36633
Collapse All | Expand All

(-)a/Koha/REST/Plugin/Query.pm (-14 / +83 lines)
Lines 22-27 use List::MoreUtils qw( any ); Link Here
22
use Scalar::Util    qw( reftype );
22
use Scalar::Util    qw( reftype );
23
use JSON            qw( decode_json );
23
use JSON            qw( decode_json );
24
24
25
use C4::Context;
25
use Koha::Exceptions;
26
use Koha::Exceptions;
26
use Koha::Exceptions::REST;
27
use Koha::Exceptions::REST;
27
28
Lines 354-367 sub _reserved_words { Link Here
354
355
355
=head3 _build_order_atom
356
=head3 _build_order_atom
356
357
357
    my $order_atom = _build_order_atom( $string );
358
    my $order_atom = _build_order_atom({
359
        string     => $string,
360
        result_set => $result_set
361
    });
358
362
359
Parses I<$string> and outputs data valid for using in SQL::Abstract order_by attribute
363
Parses I<$string> and outputs data valid for using in SQL::Abstract order_by attribute
360
according to the following rules:
364
according to the following rules:
361
365
362
     string -> I<string>
366
     string                         -> I<string>
363
    +string -> I<{ -asc => string }>
367
    +string                         -> I<{ -asc => string }>
364
    -string -> I<{ -desc => string }>
368
    -string                         -> I<{ -desc => string }>
369
    +string/ignore-articles         -> I<{ -asc => \[ REGEXP_REPLACE(...) ] }>
370
    -string/ignore-articles         -> I<{ -desc => \[ REGEXP_REPLACE(...) ] }>
371
372
The C</ignore-articles> modifier strips leading articles (based on the
373
ArticleSortStopwords system preference) when sorting. Articles are matched
374
case-insensitively and only when followed by a space, preventing false matches
375
like "Theater" or "Another".
365
376
366
=cut
377
=cut
367
378
Lines 370-398 sub _build_order_atom { Link Here
370
    my $string     = $args->{string};
381
    my $string     = $args->{string};
371
    my $result_set = $args->{result_set};
382
    my $result_set = $args->{result_set};
372
383
384
    # Step 1: Extract direction operator
385
    my $direction = '';
386
    if ( $string =~ s/^(\+)// ) {
387
        $direction = 'asc';
388
    } elsif ( $string =~ s/^(\-)// ) {
389
        $direction = 'desc';
390
    } elsif ( $string =~ s/^(\s)// ) {
391
        $direction = 'asc';
392
    }
393
394
    # Step 2: Check for /ignore-articles modifier
395
    my $ignore_articles = 0;
396
    if ( $string =~ s/\/ignore-articles$// ) {
397
        $ignore_articles = 1;
398
    }
399
400
    # Step 3: Map API field name to database column
373
    my $param = $string;
401
    my $param = $string;
374
    $param =~ s/^(\+|\-|\s)//;
375
    if ($result_set) {
402
    if ($result_set) {
376
        my $model_param = _from_api_param( $param, $result_set );
403
        my $model_param = _from_api_param( $param, $result_set );
377
        $param = $model_param if defined $model_param;
404
        $param = $model_param if defined $model_param;
378
    }
405
    }
379
406
380
    if (   $string =~ m/^\+/
407
    # Step 4: Apply article-ignoring transformation if requested
381
        or $string =~ m/^\s/ )
408
    if ($ignore_articles) {
382
    {
409
        $param = _build_article_ignoring_expression($param);
383
        # asc order operator present
410
    }
384
        return { -asc => $param };
385
    } elsif ( $string =~ m/^\-/ ) {
386
411
387
        # desc order operator present
412
    # Step 5: Return with appropriate direction
413
    if ( $direction eq 'asc' ) {
414
        return { -asc => $param };
415
    } elsif ( $direction eq 'desc' ) {
388
        return { -desc => $param };
416
        return { -desc => $param };
389
    } else {
417
    } else {
390
391
        # no order operator present
392
        return $param;
418
        return $param;
393
    }
419
    }
394
}
420
}
395
421
422
=head3 _build_article_ignoring_expression
423
424
    my $expression = _build_article_ignoring_expression( $column_name );
425
426
Takes a column name and returns a SQL literal expression that strips leading
427
articles based on the ArticleSortStopwords system preference.
428
429
Articles are matched case-insensitively and only when followed by a space,
430
preventing false matches like "Theater" or "Another".
431
432
Returns a DBIx::Class literal SQL expression: C<\[ $sql, @bind_values ]>
433
434
If the system preference is empty or not configured, returns the column unchanged.
435
436
=cut
437
438
sub _build_article_ignoring_expression {
439
    my ($column) = @_;
440
441
    # Retrieve stopwords from system preference
442
    my $stopwords = C4::Context->preference('ArticleSortStopwords') || '';
443
444
    # If no stopwords configured, return the column unchanged
445
    return $column unless $stopwords;
446
447
    # Trim whitespace
448
    $stopwords =~ s/^\s+|\s+$//g;
449
    return $column unless $stopwords;
450
451
    # Build the REGEXP_REPLACE expression
452
    # We lowercase both the column and the pattern for case-insensitive matching
453
    # Pattern: ^(article1|article2|article3)
454
    # Space after closing paren ensures we only match "The Book" not "Theater"
455
    # We use lowercase pattern because we're applying LOWER() to the column
456
    $stopwords = lc($stopwords);
457
    my $pattern = "^($stopwords) ";
458
459
    # Return DBIx::Class literal SQL expression
460
    # Column name must be in the SQL string, not as a bind parameter
461
    # REGEXP_REPLACE(subject, pattern, replace) - basic syntax for MariaDB 10.0.5+
462
    return \[ "REGEXP_REPLACE(LOWER($column), ?, '')", $pattern ];
463
}
464
396
=head3 _parse_embed
465
=head3 _parse_embed
397
466
398
    my $embed = _parse_embed( $string );
467
    my $embed = _parse_embed( $string );
(-)a/installer/data/mysql/atomicupdate/bug_36633.pl (+26 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use Koha::Installer::Output qw(say_warning say_success say_info);
3
4
return {
5
    bug_number  => "36633",
6
    description => "Add ArticleSortStopwords system preference for REST API article-ignoring sort",
7
    up          => sub {
8
        my ($args) = @_;
9
        my ( $dbh, $out ) = @$args{qw(dbh out)};
10
11
        $dbh->do(
12
            q{
13
            INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type)
14
            VALUES (
15
                'ArticleSortStopwords',
16
                'a|an|the',
17
                NULL,
18
                'Pipe-delimited list of articles to ignore when sorting (e.g., a|an|the). Used by REST API /ignore-articles sort modifier and can be used by other sorting features. Articles are matched case-insensitively at the start of strings when followed by a space.',
19
                'Free'
20
            )
21
        }
22
        );
23
24
        say_success( $out, "Added new system preference 'ArticleSortStopwords'" );
25
    },
26
};
(-)a/t/db_dependent/Koha/REST/Plugin/Objects.t (-2 / +75 lines)
Lines 145-151 get '/cities/:city_id/rs' => sub { Link Here
145
145
146
# The tests
146
# The tests
147
use Test::NoWarnings;
147
use Test::NoWarnings;
148
use Test::More tests => 20;
148
use Test::More tests => 21;
149
use Test::Mojo;
149
use Test::Mojo;
150
150
151
use t::lib::Mocks;
151
use t::lib::Mocks;
Lines 286-291 subtest 'objects.search helper, sorting on mapped column' => sub { Link Here
286
    $schema->storage->txn_rollback;
286
    $schema->storage->txn_rollback;
287
};
287
};
288
288
289
subtest 'objects.search helper, sorting with /ignore-articles modifier' => sub {
290
291
    plan tests => 52;
292
293
    $schema->storage->txn_begin;
294
295
    # Set the ArticleSortStopwords preference
296
    t::lib::Mocks::mock_preference( 'ArticleSortStopwords', 'a|an|the' );
297
298
    # Clean slate
299
    Koha::Cities->delete;
300
301
    # Create test data with various article patterns
302
    $builder->build_object(
303
        { class => 'Koha::Cities', value => { city_name => 'The Matrix', city_country => 'USA' } } );
304
    $builder->build_object(
305
        { class => 'Koha::Cities', value => { city_name => 'A Clockwork Orange', city_country => 'UK' } } );
306
    $builder->build_object(
307
        { class => 'Koha::Cities', value => { city_name => 'An American Tale', city_country => 'USA' } } );
308
    $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Batman',  city_country => 'USA' } } );
309
    $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Theater', city_country => 'UK' } } );
310
    $builder->build_object(
311
        { class => 'Koha::Cities', value => { city_name => 'Another Day', city_country => 'UK' } } );
312
313
    my $t = Test::Mojo->new;
314
315
    # Test 1: Normal sort (without /ignore-articles) - alphabetical by full name
316
    $t->get_ok('/cities?_order_by=+name&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5')
317
        ->json_is( '/0/name' => 'A Clockwork Orange' )->json_is( '/1/name' => 'An American Tale' )
318
        ->json_is( '/2/name' => 'Another Day' )->json_is( '/3/name' => 'Batman' )->json_is( '/4/name' => 'The Matrix' )
319
        ->json_is( '/5/name' => 'Theater' )->json_hasnt('/6');
320
321
    # Test 2: Article-ignoring sort (ascending)
322
    # Expected order: American, Another, Batman, Clockwork, Matrix, Theater
323
    $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5')
324
        ->json_is( '/0/name' => 'An American Tale' )->json_is( '/1/name' => 'Another Day' )
325
        ->json_is( '/2/name' => 'Batman' )->json_is( '/3/name' => 'A Clockwork Orange' )
326
        ->json_is( '/4/name' => 'The Matrix' )->json_is( '/5/name' => 'Theater' )->json_hasnt('/6');
327
328
    # Test 3: Article-ignoring sort (descending)
329
    $t->get_ok('/cities?_order_by=-name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5')
330
        ->json_is( '/0/name' => 'Theater' )->json_is( '/1/name' => 'The Matrix' )
331
        ->json_is( '/2/name' => 'A Clockwork Orange' )->json_is( '/3/name' => 'Batman' )
332
        ->json_is( '/4/name' => 'Another Day' )->json_is( '/5/name' => 'An American Tale' )->json_hasnt('/6');
333
334
    # Test 4: Multiple sort columns with /ignore-articles on first column
335
    Koha::Cities->delete;
336
    $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'The Bronx', city_country => 'USA' } } );
337
    $builder->build_object(
338
        { class => 'Koha::Cities', value => { city_name => 'A Brooklyn', city_country => 'USA' } } );
339
    $builder->build_object(
340
        { class => 'Koha::Cities', value => { city_name => 'The Bronx', city_country => 'Argentina' } } );
341
342
    $t->get_ok('/cities?_order_by=+name/ignore-articles,-country&_per_page=10')->status_is(200)->json_has('/0')
343
        ->json_has('/2')->json_is( '/0/name' => 'The Bronx' )->json_is( '/0/country' => 'USA' )
344
        ->json_is( '/1/name' => 'The Bronx' )->json_is( '/1/country' => 'Argentina' )
345
        ->json_is( '/2/name' => 'A Brooklyn' )->json_is( '/2/country' => 'USA' )->json_hasnt('/3');
346
347
    # Test 5: Empty stopwords preference - should not fail, just sort normally
348
    t::lib::Mocks::mock_preference( 'ArticleSortStopwords', '' );
349
    Koha::Cities->delete;
350
    $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'The Zoo', city_country => 'USA' } } );
351
    $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Zoo',     city_country => 'UK' } } );
352
353
    $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/1')
354
        ->json_hasnt('/2');
355
356
    # Test 6: Whitespace-only stopwords preference
357
    t::lib::Mocks::mock_preference( 'ArticleSortStopwords', '   ' );
358
    $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0');
359
360
    $schema->storage->txn_rollback;
361
};
362
289
subtest 'objects.search helper, encoding' => sub {
363
subtest 'objects.search helper, encoding' => sub {
290
364
291
    plan tests => 5;
365
    plan tests => 5;
292
- 

Return to bug 36633