modelsails.jssails-postgresql

Multiple tables for a single model in Sails.js?


I currently have a website written in Perl, using Mojolicious and Mojo::Pg. One of the things it does is pull in and display data from my Ninja Block's temperature sensors. I have four tables in Postgres, outdoor_temperature, outdoor_humidity, and the same again for indoors. The tables look like this:

 Column |  Type   | Modifiers 
--------+---------+-----------
 time   | bigint  | not null
 value  | numeric | not null
 date   | date    | not null

Where time is an epoch value in milliseconds (one record per minute), value is the temperature or humidity, and date is just that (so every record for a given day has the same value in the date column).

I'm rewriting the site using Node and Sails.js, mostly just for the learning opportunity, and I'm kind of stuck on the best way to do things here. In its Perl incarnation, I have a single subroutine for each Ninja Block-related function (display the current temperatures for today, display the highest/lowest for a given day, etc.) and I pass in which location it's for (indoor or outdoor) and everything goes from there, like this:

sub current {
    my ( $self, $table ) = @_;
    return $self->pg->db->query( "select time, value from $table where time = (select max(time) from $table)" )->hash;
}

But with Sails/Waterline, it seems to be that a model consists of just one table. There's no joins between any of the tables in my Perl version, just some semi-complex SQL queries on each individual table that I can accomplish in Sails with .query, but I'm not sure how to retain this same setup under Sails without needing to duplicate a bunch of code when checking both the indoor and outdoor tables.

Or am I thinking about this totally wrongly and there's a significantly better way to accomplish it all?


Solution

  • I ended up going with a service that sits in between the controller and the model. The controller function looks like this (using the same example as above):

    currentTemperature: function(req, res) {
        ninjaBlockService.getCurrentTemperature(req.param('location'), function(data) {
            return res.json(data);
        });
    }
    

    And the service function:

    getCurrentTemperature: function(location, callback) {
        var query = "select time, value from " + location + "_temperature where time = (select max(time) from " + location + "_temperature)";
    
        var locations = {
            outdoor: function() {
                NinjaBlockOutdoorTemperature.query(query, function(err, results) {
                    return callback(results.rows);
                });
            },
            indoor: function() {
                NinjaBlockIndoorTemperature.query(query, function(err, results) {
                    return callback(results.rows);
                });
            }
        };
    
        locations[location]();
    }
    

    It could probably be a bit more elegant, but it's working nicely for me.

    So as it turns out if you're using Waterline's PostgreSQL native .query, you don't even need multiple models for multiple tables. I've added a new model called NinjaBlock that has no attributes or anything, and can run my queries regardless of which table they're in. So the function above now looks like this instead:

    getCurrentTemperature: function(location, callback) {
        var query = "select time, value from " + location + "_temperature where time = (select max(time) from " + location + "_temperature)";
    
        NinjaBlock.query(query, function(err, results) {
                    return callback(results.rows);
        });
    }
    

    Simple!