8

How would I go about using aggregate functions in Loopback models? If I have a model backed by a mysql database, could I have Model1 with a hasMany relation to Model2 (with a given numeric property), and have a property in Model1 that grabs the SUM of that field from Model2? 


    {
        "Model1" : {
            "Relations" : {
                "model2s" : {
                    "type": "hasMany",
                    "model": "Model2",
                    "foreignKey": "model1Id"
                }
            },
            "Properties" : {
                "total" : {
                    "type": "Number"
                    [SUM of Model2 'amount' field]
                }
            }
        },
        "Model2" : {
            "Relations" : {
                "model1s" : {
                    "type": "belongsTo",
                    "model": "Model1",
                    "foreignKey": "model1Id"
                }
            },
            "Properties" : {
                "amount" : {
                    "type": "Number"
                }
            }
        }
    }

On a separate matter, what is the correct way to put a conditional in a model, so that the value returned by a getter depends on some expression? I want to return a value from a relation if it exists, otherwise return the one that exists on the primary model.

I have tried this (pseudocode):


    module.exports = function(MyModel) {
        MyModel.on('attached', function() {
            var app = MyModel.app;

            MyModel.getter['total'] = function() {
                return (this.model1Id ? this.model1.total : this.total);
            };
        });

    };

However, I end up getting a RangeError: Maximum call stack size exceeded error (similar to what is noted in this question). I'm assuming that is because it recursively calls the getter over and over, but I'm not sure of the way to resolve the issue.

Thanks in advance...

Community
  • 1
  • 1
jcq
  • 277
  • 1
  • 4
  • 10

5 Answers5

13

This can be done with Loopback's operational hooks.

Model1.observe('loaded', function (ctx, next) {
  if (ctx.instance) {
    var sum = 0;

    Model1.app.models.Model2.find({
      where: {
        model1Id: ctx.instance.id
      },
      fields: {
        givenNumericProperty: true
      }
    }, function (err, model2s) {
      if (err) return next(err);

      if (model2s.length) {
        model2s.forEach(function (model2) {
          sum += model2.givenNumericProperty;
        });

        ctx.instance.calculatedProperty = sum;
      }

      return next();
    });

  } else {
    return next();
  }
});
cloudlena
  • 865
  • 11
  • 14
  • Oh, that's a good idea to put it on the 'loaded' hook. I'll have to give that a try. Thanks! – jcq Aug 31 '15 at 22:03
  • 1
    This also works fine without the aggregation and just add simple dynamic properties. I use it to add a dynamic property that's expose over REST that calculates a recency propery based on a dateModified property. – Christiaan Westerbeek Mar 12 '16 at 14:01
  • 2
    I used 'loaded' to add calculated properties as well, but the downside is that you can't filter by these calculated properties. We have a collection of objects that have a "deletedAt" property which contains the date the item was deleted. I added a dynamic property called "deleted" which was true of the "deletedAt" property exists and is not falsy. Unfortunately I can't filter on the "deleted" property. – YeeHaw1234 Sep 23 '16 at 13:16
  • 1
    Is there a way to add a dynamic property and use a filter on it? – hyperloris Dec 01 '16 at 21:32
  • yeah, dynamic filter needed huh – Steve Pascoe Apr 05 '17 at 01:57
6

AFAIK loopback does not support aggregate functions/properties at the moment. Please open a github issue to track this as a feature request.

Note that accessing data of related models is an asynchronous operation, thus it's not possible to reliably implement a property (a getter function) to return the aggregated result.

Here is a mock-up showing how to correctly implementat a computed total:

MyModel.prototype.getTotal = function(cb) {
  if (!this.model1Id) {
    // No related model, return the total from this model.
    return cb(null, this.total);
  }

  // Fetch the related model and return its total
  this.model1(function(err, model1) {
    if (err)
      cb(err);
    else
      cb(null, model1.total);
  });
}

On a separate matter, what is the correct way to put a conditional in a model, so that the value returned by a getter depends on some expression? I end up getting a RangeError: Maximum call stack size exceeded error

As I explained in the answer you have linked to, this.total calls your custom getter function, which in turns calls this.total and so on.

The solution is to read the value from the internal data object:

MyModel.getter['total'] = function() {
   return this.__data.total;
};
Miroslav Bajtoš
  • 10,667
  • 1
  • 41
  • 99
  • I can't seem to see anything in `this.__data` — and if I spit out `this` to the console, I don't see a key like that. Am I using it in the wrong scope or something? I'm doing it in the context that I listed above (simply replacing what I had in the getter function with calls to this.__data as you indicated); is that not correct? – jcq Aug 22 '14 at 20:11
  • Note: [it is possible to implement an asynchronous getter function](http://stackoverflow.com/a/28860988/2102695) along the lines requested by OP. Loopback does not support such a function OOTB, however. – Michael.Lumley Mar 04 '15 at 17:32
3

You can try 3rd party plugins:

1) Loopback connector for aggregation: https://github.com/benkroeger/loopback-connector-aggregate

2) Loopback mixins for computed/calculated properties (works only when new model instance created): https://github.com/fullcube/loopback-ds-calculated-mixin https://github.com/fullcube/loopback-ds-computed-mixin

3) Loopback mixin for change tracking (launches on every update): https://github.com/fullcube/loopback-ds-changed-mixin

4) If you need a stats - here is another mixin: https://github.com/jonathan-casarrubias/loopback-stats-mixin

5) You can count related models: https://github.com/exromany/loopback-counts-mixin

6) You can automaticly denormalize and save related data and choose what fields will be stored (useful for caching): https://github.com/jbmarchetti/loopback-denormalize

7) If you need a computed properties for field mapping during import: https://github.com/jonathan-casarrubias/loopback-import-mixin

countTheRow
  • 141
  • 8
0

I'm not sure if you ever found what you were looking for, but I was searching for a similar feature and could not find it. After asking stack overflow, I wound up writing my own plugin, which is detailed at that link.

Community
  • 1
  • 1
Michael.Lumley
  • 2,345
  • 2
  • 31
  • 53
0

With loopback 3 you can do this:

  Report.observe("loaded", (ctx, next) => {  
    ctx.data.SOMEKEY = 'SOME VALUE'
    next();

});

Gabriel Cabral
  • 1
  • 1