martell has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks,

I would like to hear your thoughts on how to approach complex filtering queries in a REST API.

I've built in the past an internal company REST API in Catalyst using Catalyst::Controller::REST and DBIx::Class for database access. At the time there was little need for filtering on the request except for some trivial cases like GET http://../collection/?name=some_value or GET http://../collection/?date_from=20200101&date_to=20201231. Those cases I've built with a naive approach to translate the parameters towards valid search queries like $c->model(..)->search([shoot_date => ['-and', {'>=', $shoot_date_from},{'<=', $shoot_date_to} ]]);

Now the question has been asked to foresee more complex cases of filtering where several criteria can be combined. So, I was wondering what is a good strategy to build a more flexible way of encoding a filter query in the GET request and translate it into the appropriate SQL statements.

I've been searching a bit and i find some stuff on building flexible sql queries in DBIx::Class with SQL::Abstract. And if find some strategies to encode queries into an uri using for example Apache Lucene syntax, for exeample GET http://../collection?q=name:(value1 value2 value3) and date:[20200101 TO 20201231], and validate and translate the query by using Lucene::QueryParser. But I don't find anything that connects the two sides.

So, are there any good approaches to encode a flexible query into an uri, easily decode the query and encode it into a SQL statement usable to retrieve the records?

Kind regards

Replies are listed 'Best First'.
Re: Complex filter queries in a REST API
by bliako (Abbot) on Mar 25, 2021 at 11:25 UTC

    it's been a while since I quit using GET params for anything complex. I now usually POST a JSON string.

    So, why force a complex DB query into a GET params string when you can send your query as a JSON string to be converted to a Perl data structure and then directly imported into SQL::Abstract. There exists SQL::Abstract::FromQuery for even simpler data format.

    bw, bliako

[reply]

      REST mandates the use of GET for an operation which purely retrieves information. If you are using a POST just to retrieve information then your API is not REST.

      🦛

[reply]

        Good point, but JSON can be sent also by GET (URI encoded and paying attention to the max length). On the other hand, sending JSON can confuse or blind server-side caching if you ever need this with live-db queries.

[reply]
        Maybe so, but Baiko's point still holds. GET-parameters can quickly become unwieldy, whereas a JSON payload can be anything you want. It really isn't a matter of "doctrine." It's what works best for you.
[reply]
Re: Complex filter queries in a REST API
by perlfan (Parson) on Mar 25, 2021 at 17:09 UTC
    Some people are using graphql, but it's not REST. GET payloads defining filter options is not a bad idea, but with GET people get lazy and start appending query params. With that said, it might be sign that your REST design itself doesn't reflect reality given the need to augment the calls with complexity. But there is also nothing saying that a "query" can't be part of your model - just needs to be incorporated in as high a level as possible. (added) What this question (and the popularity of graphql) tells me is that the underlying data model and the REST model are not coherent with one another. What this also implies is that perhaps what is actually needed is a data abstraction layer since REST really should reveal nothing about the underlying data model itself (e.g., it should provide "data encapsulation" like trad OOP).
[reply]

      Graphql is bit to far out for my situation, but I agree with your observation that REST tends to expose too much of the underlying model to the client: The client becomes quickly tightly coupled with the underlying data model if you aren't paying attention. I've been bitten by that dog already :)

[reply]
Re: Complex filter queries in a REST API
by vincent_veyron (Beadle) on Mar 25, 2021 at 16:40 UTC

    hi

    If you can use Apache as a webserver, I'm a fan of mod_perl and libapreq. The latter enables you to write things like : 

        my $req = Apache2::Request->new( $r ) ;

    #récupérer les arguments
    my (%args, @args) ;

    #recherche des paramètres de la requête
    @args = $req->param ;

    for ( @args ) {

    $args{ $_ } = Encode::decode_utf8( $req->param($_) ) ;

    #les double-quotes et les <> viennent interférer avec le html
    $args{ $_ } =~ tr/<>"/'/ ;

    #cas de la barre espace, qui décale la colonne suivante; on suppri
+me
    $args{ $_ } =~ s/^\s+$// ;
    
    }

    [download]
    which gives you a nice hash with all the parameters sent.
    The former (mod_perl) enables you to act at all levels of the apache request cycle, and do pretty much what you want with the request. There is a bit of learning curve, but well worth the investment IMO, especially considering the fact that you know Perl already.

    https://marica.fr/
    Logiciel de gestion des sinistres assurances, des dossiers contentieux et des contrats pour le service juridique

[reply]
[d/l]

      Thanks for your suggestion, but getting the parameters as such was not my problem. I retrieve them without problems via $c->req->params within Catalyst.

      The question was about having a syntax that i could use for constructing complex conditions for a query in the uri, while avoiding to build myself a complex parser to validate the syntax and translate it to something that i could use in DBIx::Class

      Kind regards

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom