Bug 40886 - Patron circ messages not sorted in chronological order
Summary: Patron circ messages not sorted in chronological order
Status: Needs documenting
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Laura Escamilla
QA Contact: Lucas Gass (lukeg)
URL:
Keywords: rel_25_05_candidate
Depends on:
Blocks:
 
Reported: 2025-09-26 15:33 UTC by Laura Escamilla
Modified: 2025-10-31 15:40 UTC (History)
14 users (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This fixes a problem where circulation messages were not order chronologically in the staff interface.
Version(s) released in:
25.11.00,25.05.05
Circulation function:


Attachments
Bug 40886: Ensure patron messages display in proper chronological order (2.65 KB, patch)
2025-09-26 15:42 UTC, Laura Escamilla
Details | Diff | Splinter Review
Bug 40886: Ensure patron messages display in proper chronological order (2.70 KB, patch)
2025-09-30 20:23 UTC, Andrew Fuerste-Henry
Details | Diff | Splinter Review
Bug 40886: Ensure patron messages display in proper chronological order (2.76 KB, patch)
2025-10-04 13:41 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Laura Escamilla 2025-09-26 15:33:23 UTC
When viewing patron messages in the staff interface (e.g., on the circulation page), messages are currently ordered by message_type only. This causes them to display out of chronological order, making it difficult for staff to quickly identify the most recent messages.
Comment 1 Laura Escamilla 2025-09-26 15:42:12 UTC
Created attachment 186983 [details] [review]
Bug 40886: Ensure patron messages display in proper chronological order

1. Create several random patron circ messages with different dates. I used the following sql for my ktd:
    -- INSERT INTO messages (borrowernumber, branchcode, message_type, message, message_date)
SELECT
    51 AS borrowernumber,
    'CPL' AS branchcode,
    ELT(FLOOR(1 + (RAND() * 3)), 'L','B','E') AS message_type,  -- L=Staff note, B=OPAC, E=Email (adjust to your codes)
    CONCAT('Random test message ', FLOOR(RAND()*1000)) AS message,
    DATE_ADD(CURDATE(), INTERVAL -FLOOR(RAND() * 1825) DAY) AS message_date  -- random day in last ~5 years (5*365)
FROM (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) AS x;

2. View the patron account and notice that messages are sorted by type, but not by date.
3. Apply the patch and restart_all
4. Refresh the patron account page and clear the browser cache if necessary.
5. View the patron account again and notice that the messages are still sorted by type, but they are also sorted in descending order (newest to oldest).
6. Sign off and have a great day!
Comment 2 Andrew Fuerste-Henry 2025-09-30 18:33:58 UTC
A note on testing, one should not expect to have anything in the messages table with message_type=E. "E" is an option in add_message.pl, but selecting it tells Koha to put the message in the message_queue rather than in the messages table. 

An entry in the messages table with message_type "E" will end up styled as an OPAC message when viewing the patron record, which can make the sorting by type and then date appear to be incorrect.
Comment 3 Andrew Fuerste-Henry 2025-09-30 20:23:27 UTC
Created attachment 187136 [details] [review]
Bug 40886: Ensure patron messages display in proper chronological order

1. Create several random patron circ messages with different dates. I used the following sql for my ktd:
    -- INSERT INTO messages (borrowernumber, branchcode, message_type, message, message_date)
SELECT
    51 AS borrowernumber,
    'CPL' AS branchcode,
    ELT(FLOOR(1 + (RAND() * 3)), 'L','B','E') AS message_type,  -- L=Staff note, B=OPAC, E=Email (adjust to your codes)
    CONCAT('Random test message ', FLOOR(RAND()*1000)) AS message,
    DATE_ADD(CURDATE(), INTERVAL -FLOOR(RAND() * 1825) DAY) AS message_date  -- random day in last ~5 years (5*365)
FROM (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) AS x;

2. View the patron account and notice that messages are sorted by type, but not by date.
3. Apply the patch and restart_all
4. Refresh the patron account page and clear the browser cache if necessary.
5. View the patron account again and notice that the messages are still sorted by type, but they are also sorted in descending order (newest to oldest).
6. Sign off and have a great day!

Signed-off-by: Jen Tormey <jntormey@dmpl.org>
Comment 4 Lucas Gass (lukeg) 2025-10-04 13:41:12 UTC
Created attachment 187427 [details] [review]
Bug 40886: Ensure patron messages display in proper chronological order

1. Create several random patron circ messages with different dates. I used the following sql for my ktd:
    -- INSERT INTO messages (borrowernumber, branchcode, message_type, message, message_date)
SELECT
    51 AS borrowernumber,
    'CPL' AS branchcode,
    ELT(FLOOR(1 + (RAND() * 3)), 'L','B','E') AS message_type,  -- L=Staff note, B=OPAC, E=Email (adjust to your codes)
    CONCAT('Random test message ', FLOOR(RAND()*1000)) AS message,
    DATE_ADD(CURDATE(), INTERVAL -FLOOR(RAND() * 1825) DAY) AS message_date  -- random day in last ~5 years (5*365)
FROM (
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) AS x;

2. View the patron account and notice that messages are sorted by type, but not by date.
3. Apply the patch and restart_all
4. Refresh the patron account page and clear the browser cache if necessary.
5. View the patron account again and notice that the messages are still sorted by type, but they are also sorted in descending order (newest to oldest).
6. Sign off and have a great day!

Signed-off-by: Jen Tormey <jntormey@dmpl.org>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 5 Lucas Gass (lukeg) 2025-10-04 13:41:58 UTC
This patch fixes the problem described and is trivial. This is also hurting many libraries, I am taking the liberty to PQA here
Comment 6 Lucas Gass (lukeg) 2025-10-06 15:16:52 UTC
Nice work everyone!

Pushed to main for 25.11
Comment 7 Eric Swenson 2025-10-20 22:06:30 UTC
Could this be backported to 25.05?
Comment 8 Paul Derscheid 2025-10-23 21:11:20 UTC
Nice work everyone!

Pushed to 25.05.x
Comment 9 Baptiste Wojtkowski (bwoj) 2025-10-31 15:40:44 UTC
Code not present in 24.11.x, fixing a bug introduced after 24.11.x, not backported