MySQL Table Subqueries

Learning to use table subqueries in MySQL has really helped to advance my understanding of SQL and to demonstrate its versatility. I recently refactored a project to make use of a SQL query to derive the number of available pieces of gear from an inventory database. At the time when someone is making a reservation for gear, they need to know how much of each item is available. The quantity changes as equipment accumulates damage from wear and tear.


Three tables describe the equipment inventory. The EquipTitle table has an entry of each type of gear. The related EquipUnique table has a row representing each piece of gear. For example, we have 20 identical Canon XA10 video cameras. When people make a request for an XA10 camera, they don’t care which of the 20 they receive, the choice is simply “XA10 Camera” (the record in the EquipTitle) and they can tap on it to increase the quantity (up to the maximum number of records in the EquipUnique table). But a complication to the available quantities are service issues that force the staff to pull an item out of circulation. Issues with pieces of gear are tracked in the ServiceIssue table, which maintains a relationship to the EquipUnqiue table. In this simple example, the inventory includes one camera (camera A) and two microphones (mics A and B).

EquipTitle

id name
1 Fancy Camera
2 Cool Microphone


EquipUnique

id EquipTitle_id identifier
1 1 A
2 2 A
3 2 B


ServiceIssue

id EquipUnique_id status desc
1 1 3 Flip out screen is loose
2 3 3 Handle scratched
3 3 5 Power Capsule Broken
4 3 6 Audio has static


Status indicates the severity of an issue. The Fancy Camera has a minor issue that needs to be documented, but doesn’t affect its availability. One of the Cool Microphones (Mic B) is a hot mess with one minor issue and two serious issues (status 5 or higher) that prevent it from being in circulation — a bad power capsule and a loose internal connection that’s causing static.

The result should be the following table rows:

1
2
3
4
id | name | available
----------------------------------
1 | "Fancy Camera" | 1
2 | "Cool Microphone" | 1

The query needs to recognize that the issue attached to the camera should be ignored, there are no issues on Mic A which indicate that it’s fully available and Mic B needs to be evaluated based on the maximum status of its many issues.

Here is the MySQL query I used to derive that result:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT ETitle.id,
ETitle.name,
IFNULL(EUnique.available, 0)
FROM EquipTitle AS ETitle
LEFT JOIN (
SELECT EquipTitle_id,
COUNT(id) AS available
FROM EquipUnique
LEFT JOIN(
SELECT EquipUniqueItem_id,
MAX(status) AS maxStatus
FROM ServiceIssue
GROUP by EquipUnique_id
) AS MaxIssue
ON EquipUnique.id = MaxIssue.EquipUnique_id
WHERE maxStatus < 5 OR maxStatus IS NULL
GROUP BY EquipTitle_id
) AS EUnique
ON ETitle.id = EUnique.EquipTitle_id

To work through this, I constructed it in steps. Let’s start with a list of all EquipTitles (returning columns name and id):

1
2
SELECT ETitle.id, ETitle.name
FROM EquipTitle AS ETitle

Now let’s add to that a count of EquipUnique items for each EquipTitle. A simple join to related EquipUniques is going to expand the number of rows to include all records in EquipUniques.

1
2
3
4
SELECT ETitle.id, ETitle.name, EUnique.key_id
FROM EquipTitle AS ETitle
JOIN EquipUnique AS EUnique
ON ETitle.id = EUnique.EquipTitle.id

This is not what I want.

However a subquery that generates a virtual table will do the trick

1
2
3
4
5
6
7
8
9
SELECT ETitle.id, ETitle.name, EUnique.count
FROM EquipTitle AS ETitle
JOIN (
SELECT EquipTitle_id
COUNT(id) AS count
FROM EquipUnique
GROUP BY EquipTitle_id
) AS EUnique
ON ETitle.id = EUnique.EquipTitle_id

EUnique is now a derived table and the fields SELECTED are available in the outer part of the query. Aliasing the derived table is required.

The final step is limiting the count of EquipUniques to just the ones that are below the “serious” status threshold in service issues. One of the nice things about working with subqueries is that they nest easily. Instead of adding more complexity to the query I’ve go so far, I’ll demonstrate the service issue subquery as a stand-alone query.

1
2
3
4
SELECT EquipUnique_id,
MAX(status) AS maxStatus
FROM ServiceIssue
GROUP by EquipUnique_id

This generates rows of EquipUnique items with the maximum status value from any related ServiceIssue records. The final step is inserting this into our previous query as another JOIN and filtering by the maxStatus value with a WHERE clause. I want to be sure to include gear that has no issues so the WHERE clause allows maxStatus to be NULL as well.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- SELECT ETitle.id, ETitle.name, EUnique.count
-- FROM EquipTitle AS ETitle
-- JOIN (
-- SELECT EquipTitle_id
-- COUNT(id) AS count
-- FROM EquipUnique
LEFT JOIN(
SELECT EquipUniqueItem_id,
MAX(status) AS maxStatus
FROM ServiceIssue
GROUP by EquipUnique_id
) AS MaxIssue
ON EquipUnique.id = MaxIssue.EquipUnique_id
WHERE maxStatus < 5 OR maxStatus IS NULL
-- GROUP BY EquipTitle_id
-- ) AS EUnique
-- ON ETitle.id = EUnique.EquipTitle_id

As the final touch, we’ll change “count” to “available” to better represent the meaning of the column and add in a default value of 0.

1
2
3
SELECT ETitle.id,
ETitle.name,
IFNULL(EUnique.available, 0)

This is only a demonstration of subqueries used in a JOIN clause. They can be used in other parts of a SQL query. This approach happens to solve the problem I had on my plate and I like how easy it is to reason about when the subquery is so neatly self-contained as a SQL query unto itself.

Looking For Properties On Null and Uncaught Errors In Asynchronous Code

I see a common mistake among students learning JavaScript when they try to write a conditional to test the value of an object’s property but the object itself is null. Instead of testing the condition of the property, the expression throws an error.


Conditional testing for a property on NULL

The assumption is made is that the condition will evaluate as false instead of throwing an error.

1
2
3
4
5
6
const foo = null;
if (foo.bar) {
console.log('if');
} else {
console.log('else');
}

No console logs happen. The following error is thrown:
Uncaught TypeError: Cannot read property 'bar' of null


Testing with NOT operator doesn’t work either

1
2
3
4
const foo = null;
if (!foo.bar) {
console.log('if');
}

No console logs happen. The same error is thrown:
Uncaught TypeError: Cannot read property 'bar' of null


Troubleshooting the problematic code above seems clear enough. The error message is useful, it tells us the cause of the problem. What we also see proven true is the following disclaimer about thrown errors…

From MDN:

Execution of the current function will stop (the statements after throw won’t be executed), and control will be passed to the first catch block in the call stack. If no catch block exists among caller functions, the program will terminate.

Uncaught errors will terminate the program.

The correct way to use a conditional for testing for the property of an object that could be NULL is to include a test for the object as well.

Use Logical AND, progress from parent to child

1
2
3
4
5
6
const foo = null;
if (foo && foo.bar){
console.log('if');
}else{
console.log('else');
}

Successfully logs else;


Or guard against the NULL case by using Logical OR

1
2
3
4
const foo = null;
if (!foo || !foo.bar){
console.log('if');
}

Successfully logs if;


Depending on the scenario, another solution is to catch the error.

Using TRY & CATCH

1
2
3
4
5
6
const foo = null;
try {
console.log(foo.bar);
} catch(err) {
console.log('catch');
}

Successfully logs catch
NOTE: even if you don’t use it, the err variable as the parameter for catch() is NOT optional!


An error that doesn’t appear to stop the execution of code

I think the nature of the problem gets concealed when promises come into play. Instead of an error showing up in the browser, the error will go to the catch block of a promise.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
const promise = new Promise( (resolve, reject) => {
console.log('inside the promise');
resolve('promise result');
});
promise
.then( result => {
console.log('inside .then() with: ', result);
const foo = null;
if (foo.bar) {
console.log('if');
} else {
console.log('else');
}
// Code after the offending IF doesn't execute
})
.catch( reject => {
console.log('code inside the catch executes');
});

inside the promise
inside .then() with: promise result
code inside the catch executes
Notice that the functions in both .then() and .catch() were invoked. There are no uncaught errors so subsequent code will continue to execute as normal.


Or if a catch block doesn’t exist, an uncaught error is thrown but code will appear to execute after the conditional, potentially hiding the cause of the error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
const promise = new Promise( (resolve, reject) => {
console.log('inside the promise');
resolve('promise result');
});
promise
.then( result => {
console.log('inside .then() with: ', result);
const foo = null;
if (foo.bar) {
console.log('if');
} else {
console.log('else');
}
// Code after the offending IF doesn't execute
})
console.log('outside the promise');

inside the promise
outside the promise
inside .then() with: promise result
After the logs, the following error is appears in the console:
Uncaught (in promise) TypeError: Cannot read property 'bar' of null

It’s the same error we saw before and it stops the execution of code inside the .then() function, but since it was asynchronous it doesn’t stop the execution of code below the promise invocation. If there is a lot of code following the promise and if the promise is an Ajax call (for example) that takes a sizable amount of time before invoking the .then() function, then the offending error won’t be as easy to track down. If I saw this error message I would search for “bar” and then look at any promises missing catches.

Image Picker with Simple Cropping

Image Picker is the result of a larger project I’ve been working on that needed an interface for allowing a user to select and upload an image to a CloudKit database as part of a web app for maintaining a list of movie screenings. I pulled out the relevant code to make this stand-alone HTML/CSS/JavaScript module.

My task is to give the user an easy way to select an image from their file system. But I need to keep things efficient for the CloudKit database and I have a specific design spec for how the images are displayed in the final product. Users are free to choose any image but the width and height must meet a minimum pixel size and the final image must adhere to a specific aspect ratio. In my case, I want square images at 440px by 440px.

I jumped in and started working with HTML’s <input> tag using the type: file. The input appears as a button with a label that identifies a chosen file. It’s an easy-to-implement feature but it comes with drawbacks. The developer has almost no ability to style the button and label it.

1
<input type="file" name="file" onchange="fileReadHandler(this.files)">

But a button isn’t enough, I also want a drop zone to allow a user to drag and drop an image file. Drag events are another HMTL5 feature that are relatively easy to implement. I added drag event handlers to a drop zone <div>. The ondrop handler passes in an event with a dataTransfer.files property.

1
2
3
4
<div ondragenter="event.stopPropagation(); event.preventDefault();"
ondragover="event.stopPropagation(); event.preventDefault();"
ondrop="event.preventDefault(); event.stopPropagation(); onDrop(event);">
</div>

Conveniently, the ondrop event can piggyback on the file-input onchange event handler function. A newly instantiated fileReader object will process the file as an arrayBuffer.

1
2
3
4
5
6
7
8
9
10
11
12
13
function onDrop(event) {
if (event.dataTransfer.files.length < 1) return;
fileReadHandler(event.dataTransfer.files);
function fileReadHandler(files){}
var fileReader = new FileReader();
fileReader.onloadend = element => {
const imageData = element.target.result;
doStuffWithImageData(imageData);
};
fileReader.readAsArrayBuffer(files[0]);
}

The user needs to be given an opportunity to see the original image as well as the final (cropped) version and make some adjustment to get the best composition from the original. There are a lot of examples online of JavaScript libraries for image cropping and resizing. But in my case they were overkill. I didn’t need the ability to grab and stretch the corners of a resize window or move an overlay in 360 degrees. I just need a simple overlay that conforms to the aspect ratio I desire and gives the user the ability to slide it across their image either up & down or side to side, whichever is the larger dimension.

The overlay slides side to side and highlights the final cropped composition.

Most of my time went into this aspect of the work. It involves two overlapping <canvas> elements (one to hold the selected image and the other to render an overlay box to indicate the cropped composition) and a container <div> to manage the mouse or touch events to move the overlay box.

1
2
3
4
5
6
7
8
9
10
11
12
<div class="l-position-absolute"
onmousedown="event.preventDefault(); event.stopPropagation(); interactionStart(event)"
onmousemove="event.preventDefault(); event.stopPropagation(); interactionMove(event)"
onmouseup="interactionEnd(event)"
onmouseleave="interactionCancel(event)"
ontouchstart="event.preventDefault(); event.stopPropagation(); interactionStart(event)"
ontouchmove="event.preventDefault(); event.stopPropagation(); interactionMove(event)"
ontouchend="interactionEnd(event)"
ontouchcancel="interactionCancel(event)">
<canvas id="canvas-image" class="l-position-absolute canvas-image variable-size"></canvas>
<canvas id="canvas-overlay" class="l-position-absolute canvas-overlay variable-size"></canvas>
</div>

When the image is loaded, the <canvas> and <div> change size to accommodate the aspect ratio of the selected image. A portrait oriented image will increase the height while a landscape oriented image will increase the width of the tags. It requires a final function to read the pixel dimensions from the selected files. No small task as you may know if you’ve ever tried to read the technical spec for evaluating the raw data of a JPEG. There’s a possible EXIF IFD header marker with an optional SubIFD marker and 16 available Start Of Frame markers — each of which may or may not have pixel dimension data that could refer to the actual image or it could refer to the thumbnail image (augghhhh!).

For all the effort the final product seems to have been worth it. The complete HTML/CSS/JavaScript files are at Codepen and GitHub. So far it handles any PNG or JPEG file I throw at it. I’ll add TIFF and GIF formats when I get around to it.

CloudKit JS Sample Code Error

While implementing Apple’s CloudKit Catalog, I discovered an error in the sample code for the saveRecords method under the Records item.

The example demoSaveRecords method takes a lot of possible arguments.

1
2
3
4
5
function demoSaveRecords(
databaseScope,recordName,recordChangeTag,recordType,zoneName,
forRecordName,forRecordChangeTag,publicPermission,ownerRecordName,
participants,parentRecordName,fields,createShortGUID
)

Using these parameters, the demoSaveRecords method implementation constructs a record object and an options argument that are passed to the saveRecords method on CloudKit.Database.

Within the method’s code is this offending block…

1
2
3
4
5
// Convert the fields to the appropriate format.
record.fields = Object.keys(fields).reduce(function(obj,key) {
obj[key] = { value: fields[key] };
return obj;
},{});

The is intended to create a property on the record object using the keys and values of the fields object passed in as a parameter. Presumably, the fields object you pass into the method will look like this example provided in the the CloudKit JS Reference documentation:

1
2
3
4
5
6
7
8
9
10
11
12
var record = {
recordName: '115',
recordType: 'Artwork',
fields: {
title: {
value: 'MacKerricher State Park'
},
address: {
value: 'Fort Bragg, CA'
}
}
};

Likewise, if you were to simply copy the fields property off of an existing record and provide it as the method parameter, this example is exactly how the fields property is constructed.

The problem is that the code block in Apple’s demoSaveRecords function adds an extra nested value: property to the mix. When the CloudKit.Database saveRecords method is expecting:

1
2
3
4
5
fields: {
title: {
value: 'MacKerricher State Park'
}
}

Instead it gets:

1
2
3
4
5
6
7
fields: {
title: {
value: {
value: 'MacKerricher State Park'
}
}
}

The evidence is a 400 Bad Request error that likely reads:
missing required field ‘recordName’
or
Invalid value, expected type STRING but actual value was object of class com.apple.cloudkit.ws.application.common.data.UnknownTypeModel

The fix is to simply to replace…
obj[key] = { value: fields[key] };
with
obj[key] = fields[key];

Gear — Service Layer

Gear is an iPad app for managing equipment inventory and scheduling (a more complete description is on a previous blog post).

This post provides a description of the app’s service layer, the mechanism for its modules and view controllers to connect to its data store. Although Gear is a native iOS app, it requires a persistent network connection to load and alter information — the data store exists on a web server using a LAMP stack. This post won’t go into the details of the server side code, it is an overview of the service layer class in the app’s Objective-C code.

EQRWebData

EQRWebData is the app’s service layer class (EQR is the namespace prefix). It is the sole class that makes a network connection to the web server hosting the data store. Here’s what the interface looks like:

It defines a delegate protocol and property:

1
2
3
4
5
6
7
8
@protocol EQRWebDataDelegate <NSObject>
-(void)addAsyncDataItem:(id)currentThing toSelector:(SEL)action;
@end
@interface EQRWebData : NSObject <NSXMLParserDelegate>{
__weak id <EQRWebDataDelegate> delegateDataFeed;
}
@property (nonatomic, weak) id <EQRWebDataDelegate> delegateDataFeed;

And it has three public methods, one of which is the constructor:

1
2
3
4
5
+(EQRWebData *)newWebData;
-(void)query:(NSString *)link parameters:(NSArray *)para class:(NSString *)classString selector:(SEL)action completion:(CompletionBlockWithBool)completeBlock;
-(void)queryForSingle:(NSString *)link parameters:(NSArray *)para completion:(CompletionBlockWithUnknownObject)completeBlock;

The methods query:parameters:class:selector:completion: and queryForSingle:parameters:completion are the two ways that controllers make queries to the data store. query: takes a Selector as an argument and responds by creating a stream of XML data. That data is given to the method’s caller using the method named by the Selector. It’s also passed a completion block so the caller knows when the data stream has ended. Whereas, queryForSingle: is used to get a single object or when the caller is creating or altering an existing model object. queryForSingle: doesn’t create a data stream, instead it responds by returning an object as the parameter to a completion block. Both methods are asynchronous and are usually invoked inside a queue created by Grand Central Dispatch. Here is an example of a query being invoked:

1
2
3
4
dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0ul);
dispatch_async(queue, ^{
[webData query:@"getScheduleItemsWithBeginDate" parameters:paramArray class:@"EQRScheduleRequestItem" selector:@"addScheduleItem" completion:^(BOOL success) { ... }];
});

The API endpoints

Any module calling on the service layer will include an endpoint to describe the exchange of data it is requesting. It’s passed as an NSString to the query method as its first argument. Currently I have 90 endpoints (yikes!). Here is a random sampling:

  • alterPhoneInContact
  • alterScheduleEquipJoin
  • alterTransactionDaysForPrice
  • alterTransactionMarkAsPaid
  • deleteScheduleItem
  • getAllContactNames
  • getAllEquipTitleCosts
  • getClassCatalogEquipTitleItemJoins
  • setNewContact
  • setNewMiscJoin
  • setNewScheduleEquipJoin
  • setNewScheduleRequest

Most endpoints require some parameters like an object ID or the property values to be altered or created. The app’s service layer doesn’t need to know about the endpoints. It uses them as the API endpoints when calling on the web server. It’s on the web server that I have 90 different PHP scripts to tackle the business of the queries and send back an XML stream (but I’ll save the server side code for another day).

Parsing the XML data

The web server sends data back as an XML stream. As an NSXMLParserDelegate, EQRWebData implements the methods to form objects from the XML feed and deliver them back to the controllers. NSXMLParser is a Cocoa streaming parser, so it parses in an event-driven manner.

An NSXMLParser notifies its delegate about the items (elements, attributes, CDATA blocks, comments, and so on) that it encounters as it processes an XML document. It does not itself do anything with those parsed items except report them.

As objects are formed from the XML stream, EQRWebData hands them to its delegate, one at a time using the EQRWebDataDelegate method addAsycnDataItem.

1
2
3
4
5
6
7
8
9
10
#pragma mark - webData Delegate methods
-(void)addAsyncDataItem:(id)currentThing toSelector:(SEL)action{
if (![self respondsToSelector:action]){
return;
}
#pragma clang diagnostic push
#pragma clang diagnostic ignored "-Warc-performSelector-leaks"
[self performSelector:action withObject:currentThing];
#pragma clang diagnostic pop
}

performSelector is a Cocoa method that takes advantage of Objective-C’s dynamic binding. addAsyncDataItem is the listener function that disperses objects received from the service layer to the controller’s appropriate methods. When EQRWebData was initially queried, one of the method parameters was a selector which has been returned to the controller and it is now forwarding the data to that method (pragma clang diagnostic is used to suppress Xcode’s caution about an unrecognized selector).

With that, the service layer has done its job and the controller that invoked it has been given the queried data.

Looking Ahead

As I continue expanding on this app, these are the issues that I want to work on for the service layer.

  • Separate out the XML parsing. There is no reason for EQRWebData to both make network connections and parse XML data. I should make a separate class to employ the NSXMLParserDelegate methods.
  • Too many endpoints. Most of them are “get” methods and I only have about a dozen different models. But in the interest of keeping the network activity to a minimum, each model has several different “get” endpoints in order for the caller to specify the granularity of the data needed. Instead I should have one endpoint but with query parameters that allow callers to specify how much of the related data it needs to retrieve. Something I’ve heard described as expressive services.
  • Caching and syncing with CloudKit and Core Data. The dependency on a persistent network connection spells disaster when the network goes down. The app needs to cache its data and work from a local data store both for performance reasons and to cut down on the total network activity. I intend to accomplish this and to entirely replace the need for maintaining a web server LAMP stack by switching to CloudKit and Core Data. It my require a change in the data modeling since MySQL is a relational database and CloudKit uses a NoSQL data store. This approach will also allow me to make use of Apple’s remote notifications to ensure that multiple users stay up to date with each other.
  • Service layer protocol. With CloudKit in mind, it’s worth defining a protocol that declares the properties and methods expected of a service layer class. I started experimenting with CloudKit/Core Data by making a subclass of EQRWebData and overriding the query methods but this is a perfect situation to use a protocol instead of class inheritance.

Gear — Overview and Video Demo

Gear is an iPad app for managing the inventory and scheduling of video and audio gear at the Portland non-profit organization, Northwest Film Center. This post is an overview of the app’s purpose and functionality. The goal in making the app was to reduce the time it takes to schedule and check out gear, do so with fewer scheduling mistakes and keep the process simple and intuitive for the sake of training new staff and interns. The app is in use at Northwest Film Center and since introducing it, the time it takes to reserve and check out gear fell to 25% from what it was before. As an iPad app, it allows users the mobility of handling gear while moving from classroom to classroom as they are using it. The app can run on any number of iPads and all users can see and edit the current data (although it is a native iOS app, it requires a persistent network connection). The iPad also makes it possible to capture the signatures of customers and to scan QR codes attached to equipment.

The image above is the daily itinerary view. It displays the scheduled transactions for the day and enables a user to sort and filter the list, update the status of a reservation or make changes to it. The following image shows a page from monthly tracking sheet: a spreadsheet listing the complete inventory of gear, documenting the daily usage for each item. Colors are consistent between the views, they indicate the type of user: student, teacher, staff, etc.

A demo video of the iPad app Gear in action:

00:00 - 00:46 — Creating a new equipment request.
00:46 - 01:21 — Viewing and editing the gear reservations from the ‘day’ view.
01:21 - 01:39 — Marking gear for the request as prepped from the ‘day’ view.
01:39 - 02:08 — Viewing and editing the request from the ‘month’ view.
02:08 - 02:31 — Emailing a reservation confirmation to a customer from the ‘inbox’ view.
02:31 - 03:14 — Marking gear as checked out and capturing a customer signature from the ‘day’ view.
03:14 - 03:36 — Filtering the list of requests from the ‘day’ view.
Note that there is no sound on the video

Future posts will describe the design patterns and coding approaches I am using in the app. Gear was made to suit the needs of a specific organization. An updated version meant for wide release in the App Store is in progress.

Aggregation in MongoDB and Mongoose

For a recent project, I worked with my classmates from Code Fellows to create a web app to display movie box office analytics. This post demonstrates what I learned when I wanted to use aggregated data from MongoDB to display average box office grosses on a chart.

Our query to the server will (or will not) include some key/value pairs for filtering the result and the data returned will show the average income per movie screening over a six month period. The result will also have data for an alternate view that provides an overall summary of the query with averages and totals for admissions and attendance.

Although our source data came from MongoDB, a NoSQL database, we modeled our data similar to how we would with a relational database. Our Screenings collection contained instances of individual movie screenings, recording the day and time of the screening, attendance and admissions totals, and then a reference to an entry in the Movie collection that had details about the movie like title and genre.

Aggregation was introduced in Mongo version 2.2. Aggregate operations form a data processing pipeline. The documentation description reads as follows:

Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.

Mongoose’s implementation is basically a wrapper around the MongoDB methods, forming JavaScript methods. It acts as a pipeline by creating a promise which can in turn be acted on by any number of aggregation operations.

Let’s begin in the Screenings model. The following is a a static method on the Screenings collection Schema. Rather than writing out the entire function, I’ll break it up into chunks with some commentary.

1
screeningSchema.statics.getAggregateData = function aggMatchingCompany(title, genre) { ... }

The following five code blocks are the body of the above function.

I begin the aggregation with the project method to define the fields I want from the Screenings collection. Here I’m including all the fields in the Screenings schema, and even adding a few computed values that I’ll need. The movie field is a reference to a record in another collection. Project returns all of the records in the collection.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
const aggregateResult = this.aggregate([
{
$project:
{
movie: true,
attendanceTotal: true,
admissionsTotal: true,
concessionsTotal: true,
dateTime: { $subtract: ['$dateTime', 1000 * 60 * 60 * 7] },
seats: true,
format: true,
dayOfWeek: { $dayOfWeek: { $subtract: ['$dateTime', 1000 * 60 * 60 * 7] } },
hourOfDay: { $hour: { $subtract: ['$dateTime', 1000 * 60 * 60 * 7] } },
month: { $month: '$dateTime' },
},
},
]);

Next, I need to the title and genre information that is stored on the related entry in the Movies collection. Lookup is a method that effectively performs an outer left join between two collections (in RDBS terms). I’m making the connection between the movie field in my current data to the _id field in the Movies collection and the data is returned as a property on Screenings object titled movie_data.

1
2
3
4
5
6
7
aggregateResult.lookup({
from: 'movies',
localField: 'movie',
foreignField: '_id',
as: 'movie_data',
});
aggregateResult.unwind('$movie_data');

The result of lookup is an array. Unwind is called immediately afterwards to extract the object from the array. With a one-to-one relationship between the instance of a screening to a related Movie entry, unwind is just that simple. If it were a one-to-many relationship, we’d have more data processing options in the unwind.

Next, I’m testing to see if any filter queries were passed into the aggregation pipeline and I’m acting on those using match to constrain the set of all screenings to just the filtered set of screenings.

1
2
3
4
5
6
7
if (title) {
aggregateResult.match({ 'movie_data.title': title });
}
if (genre) {
aggregateResult.match({ 'movie_data.genres': { $in: [genre] } });
}

Grouping will pivot the data into the time sequence I need for a chart. Instead of returning all the raw screening data, this method will use the month field as the basis for grouping the data and returning a sum on admissions and attendance and also an average for those fields. With count, I’m also providing a count of the number of Screening records that were used to form the aggregated data.

1
2
3
4
5
6
7
8
aggregateResult.group({
_id: '$month',
count: { $sum: 1 },
admissions: { $sum: '$admissionsTotal' },
attendance: { $sum: '$attendanceTotal' },
avgAdm: { $avg: '$admissionsTotal' },
avgAtt: { $avg: '$attendanceTotal' },
});

Since this is just a plain old JavaScript promise, we can do any additional work with the data to shape it into the result we want. Here, I’m totaling and averaging the aggregated data to provide an optional grand summary object. And after that, a function to fill in any missing months and then sorting the months in sequential order.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
return aggregateResult.then(data => {
// Add summary totals
const totals = data.reduce((previous, current) => {
previous.admissions += current.admissions;
previous.attendance += current.attendance;
previous.count += current.count;
return previous;
}, { admissions: 0, attendance: 0, count: 0 });
totals.avgAdm = (totals.admissions / totals.count) || 0;
totals.avgAtt = (totals.attendance / totals.count) || 0;
// Polyfill any missing months
for (let i = 1; i < 8; i++) {
const index = data.findIndex((e) => e._id === i);
if (index === -1) {
data.push({
_id: i,
count: 0,
admissions: 0,
attendance: 0,
avgAdm: 0,
avgAtt: 0,
});
}
}
// Sort in ascending order
data.sort((a, b) => a._id > b._id);
return {
sequence: data,
totals,
};
});

The promise will resolve into an object with two properties: sequence and totals. The following is the server side route (using Express) that invokes the aggregation function.

1
2
3
4
5
.get('/aggregate', (req, res, next) => {
const { title, genre } = req.query;
Screening.getAggregateData(title, genre)
.then(data => res.json(data));
})

This is what it looks like from a client side service.

1
2
3
4
5
6
7
aggregate(params) {
return $http
.get(`${apiUrl}/screenings/aggregate`, params)
.then(r => {
return r.data;
});
},

The sequence object in the final data is passed into an instance of ChartJS as a dataset. ChartJS can even overlay datasets to easily compare multiple queries as lines or bar charts.

A demo of the finished project with mock data is online at ahbo.firebaseapp.com.
Here’s the project on GitHub.
MongoDB has some good documentation for its aggregation operations.
And here is the documentation for Mongoose.

Game Piece Collection View

Here is an example of UICollectionViews used as armies on a board game.

Each grouping of armies is a collection view – here are two collection views:

Generally collection views contain unique objects defined by some meaningful text. In this case, the data source method collectionView:cellForItemAtIndexPath: simply adds a UIView (SPYArmyView) to the cell’s content view and gives it a color.

The action happens in the custom view layout – SPYBrigadeViewLayout.

For each item returned from the data source, a single army unit is added to the stack. The data source only needs to know the total quantity of armies and the color.

The layout uses these private properties:

View Layout Properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@property int maxColumnStack;
@property int minColumnStack;
@property int unitPieceHeight;
@property int unitPieceWidth;
@property int extraHeight;
@property int extraWidth;
@property float halfUnitPieceHeight;
@property float halfUnitPieceWidth;
@property float columnHeightOffset;
@property float aisleWidthOffset;
@property int maxColumnCount;
@property int zIndexDirection;
@property float transformScaleFactor;

In this case, maxColumnStack = 4 and maxColumnCount = 3.

Here is a GitHub Gist link to the UICollectionViewController file, another for the UICollectionViewLayout, and another for the UIView that represents the content of an individual cell.