Thanks Mike, that does help.
I was going the route of processing the hooks, then running queries on the
pending events and deleting or setting to invalid the events that I didn't
want. I'll try this out for excluding based on email being null and active
being false.
I wonder if the fact that the opt in feature uses an "-exists" means that I
cannot use this trick at the same time as using the opt-in feature? It looks
like any "-exists" key that I create would get overwritten in that case.
Would it be possible to add numbered keys for some features? Like
"-exists1","-or1"? So that more than one exists/or grouping is possible?
I was trying to create a json query that produces a where clause like this, but
because of the unique key issue, I don't think it is possible.
where
checkin_time is null
and (stop_fines='MAXFINES' OR stop_fines is null)
and
(
( duration_rule='7_days_0_renew_1_graceday'
and due_date < pg_catalog.interval_pl_timestamptz( '-1 days', 'now' )
)
or
( duration_rule!='7_days_0_renew_1_graceday'
and due_date < pg_catalog.interval_pl_timestamptz( '-14 days', 'now' )
)
)
But maybe I need to restructure it so there is only one main "-or" group?
where
checkin_time is null
and
(
( duration_rule='7_days_0_renew_1_graceday'
and due_date < pg_catalog.interval_pl_timestamptz( '-1 days', 'now' )
and (stop_fines='MAXFINES' OR stop_fines is null)
)
or
( duration_rule!='7_days_0_renew_1_graceday'
and due_date < pg_catalog.interval_pl_timestamptz( '-14 days', 'now' )
and (stop_fines='MAXFINES' OR stop_fines is null)
)
)
Josh Stompro - LARL IT Director
-----Original Message-----
From: Open-ils-dev [mailto:[email protected]] On
Behalf Of Mike Rylander
Sent: Saturday, October 15, 2016 1:57 PM
To: Evergreen Development Discussion List
Subject: Re: [OPEN-ILS-DEV] Action Trigger - json filter - Joins
Josh,
JOINs in the FROM clause are explicitly restricted, because they can lead to
multiple copies of a target object being retrieved. Instead of adding
contortions to the code to avoid that, there are other ways to test "out" from
the target. You probably want to use a correlated EXISTS subquery, a la
https://wiki.evergreen-ils.org/doku.php?id=documentation:tutorials:json_query#exists_with_subqueries
(second example). In fact, it looks like there's a good example of the syntax
in that sub, right around line 489:
http://git.evergreen-ils.org/?p=Evergreen.git;a=blob;f=Open-ILS/src/perlmods/lib/OpenILS/Application/Trigger.pm;hb=HEAD#l489
HTH,
--
Mike Rylander
| President
| Equinox Software, Inc. / Open Your Library | phone: 1-877-OPEN-ILS
(673-6457) | email: [email protected] | web: http://www.esilibrary.com
On Fri, Jun 3, 2016 at 4:17 PM, Josh Stompro <[email protected]> wrote:
> Hello all, is it currently possible to do a join with the json filter
> file that the action_trigger_runner.pl uses? I would like to join the
> actor.usr table in the checkout.due hook, so I can limit based on the
> first letter of the patron’s last name. I’m looking to gradually send
> out bills over the course of a week or two based on users last names.
>
>
>
> I think the answer is no, it looks like the filter key is only for the
> where clause.
>
>
>
> I tried adding in a join like this
>
> {
>
> "checkout.due" :
>
> { "context_org" : "circ_lib",
>
> "filter" :
>
> { "checkin_time" : null,
>
> "-or" :
>
> [ { "stop_fines" : ["MAXFINES"] },
>
> { "stop_fines" : null }
>
> ],
>
> "xact_start" : {"<":"2015-09-01 00:00:00-05"},
>
> "join" : { "au" :{ "field":"id",
>
> "fkey":"usr",
>
> "filter":{"family_name":
>
> {"~*":"^a"}
>
> }
>
> }
>
> }
>
> }
>
> }
>
> }
>
>
>
> But that threw an error, looks like the join needs to be up a level
> from what I can access.
>
>
>
> Would anyone else be interested in allowing joins in the json filter file?
> Maybe OpenILS/Application/Trigger.pm create_batch_events() could grab the
> join data from the filter hash and place it with the other join to
> allow this ability?
>
> http://git.evergreen-ils.org/?p=Evergreen.git;a=blob;f=Open-ILS/src/pe
> rlmods/lib/OpenILS/Application/Trigger.pm;hb=HEAD#l385
>
>
>
>
>
> Josh
>
>
>
>
>
>
>
>
>
> Lake Agassiz Regional Library - Moorhead MN larl.org
>
> Josh Stompro | Office 218.233.3757 EXT-139
>
> LARL IT Director | Cell 218.790.2110
>
>
