From 6c5d0681bfd1cfb6c9621c5afa3ef0322c04d278 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=UTF-8 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 --- 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 4089068b34d..03fd6da0ac3 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 edb51653146..59d4c62f297 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.34.1