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

[Mike Rylander]

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/perlmods/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
>
>