From 846887af956d0a8fccbf26ae649a092b13233429 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Wed, 17 Dec 2025 07:38:32 +0000 Subject: [PATCH] Bug 36633: Add article-ignoring sort support to REST API This patch adds support for ignoring leading articles (a, an, the, etc.) when sorting via the Koha REST API using an /ignore-articles suffix modifier on _order_by parameters. We add a new system preference 'ArticleSortStopwords' (default: "a|an|the") expecting a pipe-delimited list of articles to ignore when sorting. Usage examples: GET /api/v1/biblios?_order_by=+biblio.title/ignore-articles GET /api/v1/biblios?_order_by=-biblio.title/ignore-articles Test plan: 1. Apply patch and run database update 2. Run: prove t/db_dependent/Koha/REST/Plugin/Objects.t 3. Verify all tests pass 4. Test via REST API with /ignore-articles modifier 5. Configure ArticleSortStopwords system preference 6. Verify sorting ignores configured articles correctly --- Koha/REST/Plugin/Query.pm | 97 ++++++++++++++++--- .../data/mysql/atomicupdate/bug_36633.pl | 26 +++++ t/db_dependent/Koha/REST/Plugin/Objects.t | 76 ++++++++++++++- 3 files changed, 184 insertions(+), 15 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_36633.pl diff --git a/Koha/REST/Plugin/Query.pm b/Koha/REST/Plugin/Query.pm index 5544d02ead4..dbdc47156fe 100644 --- a/Koha/REST/Plugin/Query.pm +++ b/Koha/REST/Plugin/Query.pm @@ -22,6 +22,7 @@ use List::MoreUtils qw( any ); use Scalar::Util qw( reftype ); use JSON qw( decode_json ); +use C4::Context; use Koha::Exceptions; use Koha::Exceptions::REST; @@ -354,14 +355,24 @@ sub _reserved_words { =head3 _build_order_atom - my $order_atom = _build_order_atom( $string ); + my $order_atom = _build_order_atom({ + string => $string, + result_set => $result_set + }); Parses I<$string> and outputs data valid for using in SQL::Abstract order_by attribute according to the following rules: - string -> I - +string -> I<{ -asc => string }> - -string -> I<{ -desc => string }> + string -> I + +string -> I<{ -asc => string }> + -string -> I<{ -desc => string }> + +string/ignore-articles -> I<{ -asc => \[ REGEXP_REPLACE(...) ] }> + -string/ignore-articles -> I<{ -desc => \[ REGEXP_REPLACE(...) ] }> + +The C modifier strips leading articles (based on the +ArticleSortStopwords system preference) when sorting. Articles are matched +case-insensitively and only when followed by a space, preventing false matches +like "Theater" or "Another". =cut @@ -370,29 +381,87 @@ sub _build_order_atom { my $string = $args->{string}; my $result_set = $args->{result_set}; + # Step 1: Extract direction operator + my $direction = ''; + if ( $string =~ s/^(\+)// ) { + $direction = 'asc'; + } elsif ( $string =~ s/^(\-)// ) { + $direction = 'desc'; + } elsif ( $string =~ s/^(\s)// ) { + $direction = 'asc'; + } + + # Step 2: Check for /ignore-articles modifier + my $ignore_articles = 0; + if ( $string =~ s/\/ignore-articles$// ) { + $ignore_articles = 1; + } + + # Step 3: Map API field name to database column my $param = $string; - $param =~ s/^(\+|\-|\s)//; if ($result_set) { my $model_param = _from_api_param( $param, $result_set ); $param = $model_param if defined $model_param; } - if ( $string =~ m/^\+/ - or $string =~ m/^\s/ ) - { - # asc order operator present - return { -asc => $param }; - } elsif ( $string =~ m/^\-/ ) { + # Step 4: Apply article-ignoring transformation if requested + if ($ignore_articles) { + $param = _build_article_ignoring_expression($param); + } - # desc order operator present + # Step 5: Return with appropriate direction + if ( $direction eq 'asc' ) { + return { -asc => $param }; + } elsif ( $direction eq 'desc' ) { return { -desc => $param }; } else { - - # no order operator present return $param; } } +=head3 _build_article_ignoring_expression + + my $expression = _build_article_ignoring_expression( $column_name ); + +Takes a column name and returns a SQL literal expression that strips leading +articles based on the ArticleSortStopwords system preference. + +Articles are matched case-insensitively and only when followed by a space, +preventing false matches like "Theater" or "Another". + +Returns a DBIx::Class literal SQL expression: C<\[ $sql, @bind_values ]> + +If the system preference is empty or not configured, returns the column unchanged. + +=cut + +sub _build_article_ignoring_expression { + my ($column) = @_; + + # Retrieve stopwords from system preference + my $stopwords = C4::Context->preference('ArticleSortStopwords') || ''; + + # If no stopwords configured, return the column unchanged + return $column unless $stopwords; + + # Trim whitespace + $stopwords =~ s/^\s+|\s+$//g; + return $column unless $stopwords; + + # Build the REGEXP_REPLACE expression + # We lowercase both the column and the pattern for case-insensitive matching + # Pattern: ^(article1|article2|article3) + # Space after closing paren ensures we only match "The Book" not "Theater" + # We use lowercase pattern because we're applying LOWER() to the column + $stopwords = lc($stopwords); + my $pattern = "^($stopwords) "; + + # Return DBIx::Class literal SQL expression + # Column name must be in the SQL string, not as a bind parameter + # REGEXP_REPLACE(subject, pattern, replace) - basic syntax for MariaDB 10.0.5+ + return \[ "REGEXP_REPLACE(LOWER($column), ?, '')", $pattern ]; +} + =head3 _parse_embed my $embed = _parse_embed( $string ); diff --git a/installer/data/mysql/atomicupdate/bug_36633.pl b/installer/data/mysql/atomicupdate/bug_36633.pl new file mode 100644 index 00000000000..b57fd5c0b43 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_36633.pl @@ -0,0 +1,26 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); + +return { + bug_number => "36633", + description => "Add ArticleSortStopwords system preference for REST API article-ignoring sort", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + $dbh->do( + q{ + INSERT IGNORE INTO systempreferences (variable, value, options, explanation, type) + VALUES ( + 'ArticleSortStopwords', + 'a|an|the', + NULL, + '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.', + 'Free' + ) + } + ); + + say_success( $out, "Added new system preference 'ArticleSortStopwords'" ); + }, +}; diff --git a/t/db_dependent/Koha/REST/Plugin/Objects.t b/t/db_dependent/Koha/REST/Plugin/Objects.t index 55494b90da5..ab3e0c9c4e3 100755 --- a/t/db_dependent/Koha/REST/Plugin/Objects.t +++ b/t/db_dependent/Koha/REST/Plugin/Objects.t @@ -145,7 +145,7 @@ get '/cities/:city_id/rs' => sub { # The tests use Test::NoWarnings; -use Test::More tests => 20; +use Test::More tests => 21; use Test::Mojo; use t::lib::Mocks; @@ -286,6 +286,80 @@ subtest 'objects.search helper, sorting on mapped column' => sub { $schema->storage->txn_rollback; }; +subtest 'objects.search helper, sorting with /ignore-articles modifier' => sub { + + plan tests => 52; + + $schema->storage->txn_begin; + + # Set the ArticleSortStopwords preference + t::lib::Mocks::mock_preference( 'ArticleSortStopwords', 'a|an|the' ); + + # Clean slate + Koha::Cities->delete; + + # Create test data with various article patterns + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'The Matrix', city_country => 'USA' } } ); + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'A Clockwork Orange', city_country => 'UK' } } ); + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'An American Tale', city_country => 'USA' } } ); + $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Batman', city_country => 'USA' } } ); + $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Theater', city_country => 'UK' } } ); + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'Another Day', city_country => 'UK' } } ); + + my $t = Test::Mojo->new; + + # Test 1: Normal sort (without /ignore-articles) - alphabetical by full name + $t->get_ok('/cities?_order_by=+name&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5') + ->json_is( '/0/name' => 'A Clockwork Orange' )->json_is( '/1/name' => 'An American Tale' ) + ->json_is( '/2/name' => 'Another Day' )->json_is( '/3/name' => 'Batman' )->json_is( '/4/name' => 'The Matrix' ) + ->json_is( '/5/name' => 'Theater' )->json_hasnt('/6'); + + # Test 2: Article-ignoring sort (ascending) + # Expected order: American, Another, Batman, Clockwork, Matrix, Theater + $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5') + ->json_is( '/0/name' => 'An American Tale' )->json_is( '/1/name' => 'Another Day' ) + ->json_is( '/2/name' => 'Batman' )->json_is( '/3/name' => 'A Clockwork Orange' ) + ->json_is( '/4/name' => 'The Matrix' )->json_is( '/5/name' => 'Theater' )->json_hasnt('/6'); + + # Test 3: Article-ignoring sort (descending) + $t->get_ok('/cities?_order_by=-name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/5') + ->json_is( '/0/name' => 'Theater' )->json_is( '/1/name' => 'The Matrix' ) + ->json_is( '/2/name' => 'A Clockwork Orange' )->json_is( '/3/name' => 'Batman' ) + ->json_is( '/4/name' => 'Another Day' )->json_is( '/5/name' => 'An American Tale' )->json_hasnt('/6'); + + # Test 4: Multiple sort columns with /ignore-articles on first column + Koha::Cities->delete; + $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'The Bronx', city_country => 'USA' } } ); + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'A Brooklyn', city_country => 'USA' } } ); + $builder->build_object( + { class => 'Koha::Cities', value => { city_name => 'The Bronx', city_country => 'Argentina' } } ); + + $t->get_ok('/cities?_order_by=+name/ignore-articles,-country&_per_page=10')->status_is(200)->json_has('/0') + ->json_has('/2')->json_is( '/0/name' => 'The Bronx' )->json_is( '/0/country' => 'USA' ) + ->json_is( '/1/name' => 'The Bronx' )->json_is( '/1/country' => 'Argentina' ) + ->json_is( '/2/name' => 'A Brooklyn' )->json_is( '/2/country' => 'USA' )->json_hasnt('/3'); + + # Test 5: Empty stopwords preference - should not fail, just sort normally + t::lib::Mocks::mock_preference( 'ArticleSortStopwords', '' ); + Koha::Cities->delete; + $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'The Zoo', city_country => 'USA' } } ); + $builder->build_object( { class => 'Koha::Cities', value => { city_name => 'Zoo', city_country => 'UK' } } ); + + $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0')->json_has('/1') + ->json_hasnt('/2'); + + # Test 6: Whitespace-only stopwords preference + t::lib::Mocks::mock_preference( 'ArticleSortStopwords', ' ' ); + $t->get_ok('/cities?_order_by=+name/ignore-articles&_per_page=10')->status_is(200)->json_has('/0'); + + $schema->storage->txn_rollback; +}; + subtest 'objects.search helper, encoding' => sub { plan tests => 5; -- 2.52.0