Bug 31787 - [DOCS] Add description and examples to membership_expiry.pl about --where option
Summary: [DOCS] Add description and examples to membership_expiry.pl about --where option
Status: Needs documenting
Alias: None
Product: Koha
Classification: Unclassified
Component: Documentation (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Marie-Luce Laflamme
QA Contact: Testopia
Depends on:
Reported: 2022-10-14 09:48 UTC by David Nind
Modified: 2023-08-22 14:43 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact: David Nind
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Note You need to log in before you can comment on or make changes to this bug.
Description David Nind 2022-10-14 09:48:46 UTC
An optional `--where` parameter was added to the `membership_expiry.pl` cronjob in 21.11.

This allows for arbitrarily complex SQL where statements to be passed to the script to filter affected patrons.

Add some additional information about this, and some examples on how to construct SQL queries, for example: to restrict to patrons in a specific category.

See IRC discussion: http://irc.koha-community.org/koha/2022-10-14#i_2456049

See bug 28456.

Topic affected: https://koha-community.org/manual/22.11/en/html/cron_jobs.html#notify-patrons-of-expiration
Comment 1 Anke Bruns 2023-06-22 09:08:06 UTC
Filtering for categorycode with the where parameter doesn't work:

koha-foreach /usr/share/koha/bin/cronjobs/membership_expiry.pl -n -c -v --where="categorycode='STUD'"

results in no mails being sent even though there are expiring memberships within the time covered by the cronjob.

We tried every combination of single and double quotes we could think of.

We also tried the above command with "me.categorycode" instead of categorycode.
Comment 2 Marie-Luce Laflamme 2023-07-20 14:31:11 UTC
I've also had a hard time testing the categorycode argument. However, David’s description had the answer in the IRC discussion link.

We need to add a "me" before the categorycode, so it should look like this:
   --where="me.categorycode = 'CODE' "


to run send email only for one category, try:
./misc/cronjobs/membership_expiry.pl -c -v -n --before 100 --after 100 --where="me.categorycode = 'S'"

to exclude a category, try:
./misc/cronjobs/membership_expiry.pl -c -where="me.categorycode !='YA'"
Comment 3 Marie-Luce Laflamme 2023-07-20 19:35:11 UTC
I've added the info in the manual - merge request pending
Comment 4 Anke Bruns 2023-07-21 07:25:40 UTC
Yes, we tried the "me" but it didn't help, either.
Comment 5 Marie-Luce Laflamme 2023-08-22 14:43:10 UTC
The cron probably doesn't like the dot (.) in the argument. My colleague fix this issue by adding a "\" before and after the argument.

For example, our cron to exclude some categories look like this:
/misc/cronjobs/membership_expiry.pl -c -where=\"me.categorycode NOT IN ('EM','ADM','SU', 'TA')\"