[OPEN-ILS-DEV] Action Trigger - json filter - Joins

[Josh Stompro]

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:open-ils-dev-bounces at list.georgialibraries.org] 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:  miker at esilibrary.com  | web:  http://www.esilibrary.com


On Fri, Jun 3, 2016 at 4:17 PM, Josh Stompro <stomproj at exchange.larl.org> 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
>
>