Part IV - Querying the Database

Techinically, parts I-III have already setup a query of the database - albeit, a trivial one. In this entry, we'll add the functionality to allow users to specify some more complicated queries. This is a good time to make a new git branch so that you don't mess up your master. Something like git checkout -b form-query will do. Now, before we start, let's quickly recall what we're working with, and what we'll add:

project
│   app.py
│   procfile
│   requirements.txt
│   .env
│
└───static
│   │   app.js
│   │
│   └───partials
│       │   list.html
│
└───templates
    │   index.html

app.py

contains

  • SQLAlchemy db model for courses SQL table
  • flask_restful api resource class with get method for querying the database
  • Initialization of api and Blueprints for setting the route prefixes for the api
  • marshmallow schema for dumping query results
  • flask route for rendering the index page

to add

  • a new flask_restful api resource for parsing the query endpoint

html

contains

  • index.html with a single element to grab the first entries in the database
  • list.html corresponding to the AngularJS state which will display the query results

to add

  • form.html which will contain input elements by which the user can specify a query

app.js

contains

  • resource factory that will get the static query we've defined
  • definition of states for initial index view and query display view
  • controller for sending the query results to the DOM

to add

  • new resource factory to interact with the new api resource we will be defining in the app.py
  • new controller pass query parameters from the form DOM to the back end api

The Form

Let's start with making the form by which users will specify query parameters. We'll add some basic filters for total distance covered and total elevation change, and then a more complex filter for a starting and ending location.

cd into your project folder (which should trigger the activation of your virtual environment -- if not, activate it) and make the partial for the form: touch /static/partials/form.html. Since it's a partial, it's rendered within the index.html and we don't have to load any css or script assets. That said, I'm going to use Angular's Material, so that things look as pretty as possible, while still being lazy. That means loading some scripts into the index page. Add the following to index.html:

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/angular-material/1.1.3/angular-material.min.css">
<script src="https://code.angularjs.org/1.5.5/angular-animate.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/angular-material/1.1.3/angular-material.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.5.5/angular-aria.min.js"></script>

In the form.html we then need to use material's <md-input container> to wrap the input elements. We'll also need to specify a container within the AngularJS app.js that will house the data before passing it to the backend. appForm will be the json container housing all the information and functions relevant to form.html -- which may be a surprising number of things when all is said and done. Containing the form data, we'll use appForm.data as the container for the data; it will have elements corresponding to the parameters we're passing from the input form. Recall our table has columns for length_in_meters and elevation_gain_in_meters; to search within a range of values for each, we'll need min and max parameters. Note that specifying type=number in the input elements saves us the trouble of varifying the datatype being passed, because it won't allow anything other than numerical entries.

<form ng-submit="appForm.submit()">>
<md-content layout-gt-sm="row" layout-padding>
<div>
  <span class="md-headline">Distance the route covers:</span><br>
    <md-input-container>
      <label>minimum</label>
      <input type="number" ng-model="appForm.data.dist_min">
    </md-input-container>
    <md-input-container>
      <label>maximum</label>
      <input type="number" ng-model="appForm.data.dist_max">
    </md-input-container>
    <br>

  <span class="md-headline">Total elevation change along the route:</span><br>
    <md-input-container>
      <label>minimum</label>
      <input type="number" ng-model="appForm.data.elev_min">
    </md-input-container>
    <md-input-container>
      <label>maximum</label>
      <input type="number" ng-model="appForm.data.elev_max">
    </md-input-container>

  <input type='button' value='Reset' ng-click='appForm.reset()' />
  <input type='submit'/><br>

</div>
</md-content>
</form>

The above will give you input elements to specify min and max values for distance and elevation gain, along with submission and forms clearing buttons (which, you'll notice, reference functions that we'll have to define). This is a good time to try the code out and make sure everything is rendering. Recall that you can do this in your console by running python app.py and navigating your browser to localhost:5000. Speaking of good times, this is also a good time to commit/push your changes to your new branch.

passing data

Now that the form itself is created, we know that we're querying by 4 numeric parameters: dist_min, dist_max, elev_min, and elev_max. flask_restful has a built-in way to setup a CRUD, but since I was only setting this up to query, I used a dynamic endpoint that can parse query parameters, and that's what I'm going to cover here. The query will be specified similarly to the way it is already done, by hitting your-heroku-app.herokuapp.com/api/v1/courses.json, except now we want to pass parameters from the url. A query will look like your-heroku-app.herokuapp.com/api/v1/courses/dist_min=10&dist_max=50&elev_max=800.json. (you can choose to include the .json suffix at your leisure, but be consitent).

the resource factory (app.js)

This factory is, oddly, very similar to the previous one. Instead of allowing for an id to be passed to the api, we will pass an endpoint. The rest is the same. The factory looks like

.factory('queryFactory', function($resource) {
  return $resource('api/v2/courses/query/:endpoint.json',
    { endpoint:'@endpoint' },
    { 'query': {method: 'GET', isArray:true }},
    { update: {method: 'PATCH' }},
    { stripTrailingSlashes: false }
    );

the form controller (app.js)

This controller will get the data submitted from the form and use it to call the get that we defined in the Angular resource factory above. First, make the appForm.data container for the form inputs we specified in the form.html. Then, we need to turn this json blurb/dictionary into a string of the form dist_min=10&dist_max=50&elev_max=800 - this is called serialization, and can be done using the function httpParamSerializerJQLike (which depends on http), so we'll have to load both of these when declaring dependencies in the controller definition. This function will take all the attributes of a json blurb and serialized them in the form above: att_1=val_1&...&att_n=val_n. To keep it from passing null values, we'll have to write a little script to find the non-null attributes - it's really just an iteration on the keys of the appForm.data element with an if statement. This will all be contained in the function submit that is called when the submission button on the form is activated.

Delete the old controller and start over. Everything to do with the form will be in the formController.

angular.module('myApp.Controllers',[])
.controller("formController",["$state", "$scope", "$http", "$httpParamSerializerJQLike",
  function($state,$scope,$http,$httpParamSerializerJQLike){
    $scope.appForm = {
      data: {
        dist_min:null,
        dist_max:null,
        elev_min:null,
        elev_max:null
      },
      cleared: {
        dist_min:null,
        dist_max:null,
        elev_min:null,
        elev_max:null
      },
      submit: function(){
        nonz = {};
        keys = Object.keys($scope.appForm.data);
        for (var k in keys){
          if ($scope.appForm.data[keys[k]]){
            nonz[keys[k]]=$scope.appForm.data[keys[k]];
          }
        }
        endpoint = $httpParamSerializerJQLike(nonz);
        $state.go('routes.query',{end:endpoint}, {reload:'routes.query'});
      },
      reset: function(){
        angular.copy($scope.appForm.cleared, $scope.appForm.data);}
    }
}]);

the api resource

Just like the super simple query, we'll define a class inheriting from db.Model and a get method for it. This time the get will be more complicated, since it will have to parse dist_min=10&dist_max=50&elev_max=800, and things like it. To get will be passed the custom_input generated by Angular from the form.html inputs - we will make it look like the above.

Users will be able to access the api through the url, since that's how the front end does it, so we want to make sure that there are some precautions in place keeping bad queries from being passed. First we'll make sure that incoming queries have the proper parameters -- checking the datatypes of passed values will be done by the db.Model.

class customQuery(Resource):

    def get(self, custom_input):
        custom_input = custom_input.split('&')
        #make a dictionary whose keys are parameter names and values are passed values
        params = {}
        for x in custom_input:
            params[x.split('=')[0]] = x.split('=')[1]
        #check for invalid parameters passed by the user through the endpoint url
        invalids = [x for x in params.keys() if x not in set(['dist_max','dist_min','elev_max','elev_min'])]
        if not invalids:
            q = Routes.query
            try:
                #try adding the parameters as filters to the query. filters can be added recursively.
                if 'dist_min' in params.keys():
                    q = q.filter(Routes.length_in_meters>=float(params['dist_min']))
                if 'dist_max' in params.keys():
                    q = q.filter(Routes.length_in_meters<=float(params['dist_max']))
                if 'elev_min' in params.keys():
                    q = q.filter(Routes.elevation_gain_in_meters>=float(params['elev_min']))
                if 'elev_max' in params.keys():
                    q = q.filter(Routes.elevation_gain_in_meters<=float(params['elev_max']))
            except ValueError as err:
                resp = jsonify({"error":err.message, "status_code":403, "links":{"error":True}})
                return resp

            results_query = q.limit(100)
            results = schema.dump(results_query, many=True)

        else:
            resp = jsonify({"error":"You have specified the following invalid search parameters: {}.".format(', '.join(invalids)), "status_code":204, "links":{"error":True}})
            return resp

Now all that we need to do is add the resource to the api with a specified endpoint with the following line.

api.add_resource(queryRoutes, '/<custom_input>.json', endpoint='api/v1/courses/query')

Routing and stating

The next thing that we need to do is make sure that states exist to display the query form by default. This is done in the courses state by simply by replacing a the template line with templateUrl: '../static/partials/form.html'

Finally, make sure that you've added all the dependencies to your intitialization statement! This is something I had problems with numerous times, and it was super frustrating! Also, check and make sure that everything that needs scripts and css files loaded, gets loaded in the index.html - also super frustrating. In the app.js your initialization should look like

var app = angular.module('myApp',['ui.router', 'ngResource', 'myApp.Services', 'myApp.Controllers', 'ngAria', 'ngAnimate', 'ngMaterial']);

This is a big commit, and almost has to be done that way, since no piece will work without the others. Test your app as often as you can (again, problematic here) and commit often as well.

Again, this project is still in development. Code can be found on my github.