An interview with David Knapp
Apex Designer is our Low Code platform for rapid application development. It’s built on a cluster of popular open standards including Angular, Node.js and Loopback. I sat down with our president, David Knapp, to talk about how he solved an interesting challenge around making Loopback perform a common database action, select distinct.
Q: Can you talk about the challenge of getting the “select distinct” into Loopback?
David: Apex Designer uses Loopback to generate APIs for the business objects in a project. Loopback does a great job of connecting the APIs to the backend database using Connectors but there is one notably missing operation: a “select distinct”. In relational databases, a select with the distinct keyword will return only unique values. A select distinct is very helpful when trying to get unique values for a user interface select or typeahead.
Q: That sounds difficult, if Loopback didn’t include the select distinct function natively?
David: Actually, because we built Apex Designer using open standards, we were able to take advantage of a lot of useful community add-ons.
Apex Designer lets you define new behaviors for business objects. These behaviors can define new API endpoints that are callable from the client. We could have added the distinct logic directly on each business object but that would be a lot of duplicate code. Instead we opted to use a Loopback “mixin”.
The Apex Designer Base Library (starting in version 1.1.1) includes the distinct mixin. You will see it on the Settings + Artifacts page with this path:
/loopback/server/mixins/distinct.jsTo add this to a business object, go to the business object’s page and add a behavior named “distinct”. Open the behavior and paste in this code:
module.exports = function(Model) {
require('../mixins/distinct')(Model)
}That’s all there is to it!
Q: That sounds pretty easy, but can what’s really happening behind the scenes?
David: While you don’t need to know how this works to use it, we can walk through the code behind this step by step.
Starting the Mixin
The first couple of lines in the distinct.js mixin file define the distinct function and its inputs:
module.exports = function (Model) {
Model.distinct = function (filter, cb) {Model is a reference to the model class. The second line adds the distinct function to the model with filter and a callback function as inputs.
Getting the Model Name and Connector
The next couple of lines get the model name and the connector:
let model = Model.definition.name;
let connector = Model.getConnector();The model name will be used in subsequent calls. The connector is a reference to the database connector related to the model.
Preparing the Filter
The filter is a variation of the standard Loopback filter (see Querying Data for more information).
filter = filter || {};
if (!filter.fields) {
let err = new Error('You must specify fields.');
err.status = 400;
return cb(err);
}
if (filter.include) {
let err = new Error('Include is not supported.');
err.status = 400;
return cb(err);
}
if (!filter.order) {
for (name in filter.fields) {
filter.order = name;
break;
}
}The first if ensures that the caller specifies the “fields” key (the user must specify at least one column to get a valid result). The second if ensures that the caller does not specify the “include” key. Include is not supported in the distinct method. Finally, if the user does not specify an order, we will use one of the field names. This ensures that Loopback won’t use it’s default order of “id” which typically won’t be in the list of fields.
Creating the SQL and Parameters
Here is the key to the distinct method. We could carefully construct the SQL but there are lots of edge cases and Loopback has done all the work for us already so we will just use that. The Loopback connector has lots of helpful methods including “buildSelect”:
let statement = connector.buildSelect(model, filter);
let words = statement.sql.split(' ');
words.splice(1,0,'distinct');
statement.sql = words.join(' ');As you can see, buildSelect is a function that takes in a model name and a filter and constructs a statement object that contains the SQL and the parameters. We all that function and then simply insert the “distinct” keyword after the select in the SQL.
Executing the SQL
Now that we have the SQL and parameters, we simply call the “execute” method on the connector to perform the query:
connector.execute(stmt.sql, stmt.params, function(err, results) {
if (err) {
return cb(err, []);
}
var objects = results.map(function(row) {
return connector.fromRow(model, row);
});
cb(null, objects);
});If there is an error, the callback is called to return the error. Results is an array containing one object for each row in the query result set. The property names in those objects are the column names from the database. The map function uses another connector utility “fromRow” to convert those column names to the corresponding property names. Finally the objects are returned to the caller
Exposing the Distinct Method
The last part of the distinct.js file uses the Loopback remoteMethod function to expose the distinct function as a REST endpoint:
Model.remoteMethod(
'distinct',
{
description: 'performs a select distinct.',
isStatic: true,
accepts: [
{
arg: 'filter',
type: 'object',
description: 'Loopback filter that must include "fields" and must not include "include".'
}
],
returns: {type: "array", root: true},
http: {verb: 'get'}
}
);The function name is passed as the first argument and the options are defined in the second argument (see Options for details). “isStatic” is true because the distinct method is not instance-specific. “accepts” defines the inputs with their type description (just filter in our case). “returns” defines the output type and “http” defines that this will be a “get”.
Summary
It’s easy to define new business object behaviors and share them across multiple objects using a Mixin. The Loopback connector has lots of helper methods for accessing the database. To discover more, you can read through the source code of the SQL connector here.
