From 13947b955412412898030a220904cc67da2049f3 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 1 Feb 2024 09:48:31 +0100 Subject: [PATCH] Bug 33568: (follow-up) Fix ordering - WIP MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit This is not working. We are still getting the error Unable to programatically derive a required group_by from the supplied order_by criteria. To proceed either add an explicit group_by, or simplify your order_by to only include plain columns (supplied order_by: 'lpad_copynumber'). To recreate the problem you can go to detail.pl and search for something Without search the query is working. The problem appears when the query has WHERE parameters added and "GROUP BY" is added. DBIC is adding to the GROUP BY what is added to the select, but does not handle this situation (not a plain column). You can play with: 1. Remove from Koha::Items->search_ordered 475 'lpad_copynumber', => all good 2. Remove the following block from /usr/share/perl5/DBIx/Class/Storage/DBIHacks.pm 584 # only consider real columns (for functions the user got to do an explicit group_by) 585 my $chunk_ci; 586 if ( 587 @{$order_by[$o_idx]} != 1 588 or 589 # only declare an unknown *plain* identifier as "leftover" if we are called with 590 # aliastypes to examine. If there are none - we are still in _resolve_attrs, and 591 # can just assume the user knows what they want 592 ( ! ( $chunk_ci = $colinfos->{$order_by[$o_idx][0]} ) and $attrs->{_aliastypes} ) 593 ) { 594 push @$leftovers, $order_by[$o_idx][0]; 595 } => all good I don't know if we are doing something wrong here, but I have no idea how we can build the group_by explicitely. Is there another way to do this? Signed-off-by: Laurence Rault Signed-off-by: Emily Lamancusa Signed-off-by: Tomás Cohen Arazi Signed-off-by: Katrin Fischer --- Koha/Items.pm | 5 ++++- Koha/REST/V1/Biblios.pm | 1 + 2 files changed, 5 insertions(+), 1 deletion(-) diff --git a/Koha/Items.pm b/Koha/Items.pm index 4089068b34..03fd6da0ac 100644 --- a/Koha/Items.pm +++ b/Koha/Items.pm @@ -466,10 +466,13 @@ sub search_ordered { return $self->search( {}, { + '+select' => [{ + lpad => ['copynumber', \8, \"'0'"], '-as' => 'lpad_copynumber', + }], order_by => [ 'homebranch.branchname', 'me.enumchron', - \"LPAD( me.copynumber, 8, '0' )", + 'lpad_copynumber', {-desc => 'me.dateaccessioned'} ], join => ['homebranch'] diff --git a/Koha/REST/V1/Biblios.pm b/Koha/REST/V1/Biblios.pm index edb5165314..59d4c62f29 100644 --- a/Koha/REST/V1/Biblios.pm +++ b/Koha/REST/V1/Biblios.pm @@ -307,6 +307,7 @@ sub get_items { $items_rs = $items_rs->filter_by_bookable if $bookable_only; # FIXME We need to order_by serial.publisheddate if we have _order_by=+me.serial_issue_number my $items = $c->objects->search($items_rs); + delete $_->{lpad_copynumber} for @$items; return $c->render( status => 200, -- 2.39.2