Let's consider how you would do this in SQL:
SELECT * FROM items INNER JOIN pkgs ON items.id = pkgs.item_id
ORDER BY tobuy DESC, store, aisle, name
The following answer demonstrates how to implement an inner join and an equijoin in JavaScript:
function equijoin(primary, foreign, primaryKey, foreignKey, select) {
var m = primary.length, n = foreign.length, index = [], c = [];
for (var i = 0; i < m; i++) { // loop through m items
var row = primary[i];
index[row[primaryKey]] = row; // create an index for primary table
}
for (var j = 0; j < n; j++) { // loop through n items
var y = foreign[j];
var x = index[y[foreignKey]]; // get corresponding row from primary
c.push(select(x, y)); // select only the columns you need
}
return c;
}
Now you can use equijoin to join items and pkgs as follows:
equijoin(items, pkgs, "id", "item_id", function (item, pkg) {
return {
id: +item.id,
tobuy: +item.tobuy,
store: pkg.store,
aisle: +pkg.aisle,
name: item.name
};
});
Note that I'm coercing item.id, item.tobuy and pkg.aisle to numbers by applying the unary + operator to them.
Now that we joined the two tables we need to sort them. To sort the table we use the built-in array sort method:
.sort(function (a, b) {
// ORDER BY tobuy DESC
var aTobuy = a.tobuy, bTobuy = b.tobuy;
if (aTobuy < bTobuy) return 1;
if (aTobuy > bTobuy) return -1;
// ORDER BY store
var aStore = a.store, bStore = b.store;
if (aStore < bStore) return -1;
if (aStore > bStore) return 1;
// ORDER BY aisle
var aAisle = a.aisle, bAisle = b.aisle;
if (aAisle < bAisle) return -1;
if (aAisle > bAisle) return 1;
// ORDER BY name
var aName = a.name, bName = b.name;
if (aName < bName) return -1;
if (aName > bName) return 1;
// keep them unchanged
return a.id - b.id;
});
The sort method is unstable (i.e. it might not preserve the ordering of items with equal sort value in the input list). Hence to workaround this limitation we return a.id - b.id as the last statement.
Also notice that we're comparing all the values (whether strings or numbers) using < and >. Strings are compared lexicographically while numbers are compared numerically.
Put together the code is as follows:
var table = equijoin(items, pkgs, "id", "item_id", function (item, pkg) {
return {
id: +item.id,
tobuy: +item.tobuy,
store: pkg.store,
aisle: +pkg.aisle,
name: item.name
};
}).sort(function (a, b) {
var aTobuy = a.tobuy, bTobuy = b.tobuy;
if (aTobuy < bTobuy) return 1;
if (aTobuy > bTobuy) return -1;
var aStore = a.store, bStore = b.store;
if (aStore < bStore) return -1;
if (aStore > bStore) return 1;
var aAisle = a.aisle, bAisle = b.aisle;
if (aAisle < bAisle) return -1;
if (aAisle > bAisle) return 1;
var aName = a.name, bName = b.name;
if (aName < bName) return -1;
if (aName > bName) return 1;
return a.id - b.id;
});
Not as concise as SQL is it? Anyway, see the demo for yourself: http://jsfiddle.net/7ZG96/
Edit: If you want only want the id, tobuy and name columns then you can extract it from the sorted table using map as follows:
table.map(function (item) {
return {
id: item.id,
tobuy: item.tobuy,
name: item.name
};
});
This corresponds to the following SQL query:
SELECT id, tobuy, name FROM (SELECT * FROM items INNER JOIN pkgs
ON items.id = pkgs.item_id ORDER BY tobuy DESC, store, aisle, name)
See the updated demo: http://jsfiddle.net/7ZG96/1/
