Zach A. Zach A. - 6 months ago 17
Javascript Question

SQL-joining of two or more arrays in JavaScript

I have one-page web app with few arrays, that are logically linked: records from "users" refers to records in "user_types", "charges" refers to "users", etc:

var users = [
{ id: "u0001", name: "John", user_type_id: "1" },
{ id: "u0002", name: "Bob", user_type_id: "1" },
{ id: "u0003", name: "Alice", user_type_id: "5" },
{ id: "u0004", name: "Jennifer", user_type_id: "5" },
// ... more
];

var user_types = [
{ id: "1", name: "Regular Clients"},
{ id: "5", name: "VIP Clients"},
// ... more
];

var charges = [
{ id: "7443", user_id: "u0001", date: "2016-01-01", amount: "3.99", },
{ id: "7445", user_id: "u0001", date: "2016-01-01", amount: "4.02", },
{ id: "7448", user_id: "u0001", date: "2016-01-01", amount: "6.99", },
{ id: "7453", user_id: "u0003", date: "2016-01-01", amount: "3.00", },
{ id: "7469", user_id: null , date: "2016-01-01", amount: "3.99", },
// ... more
];


I need to display them in linked manner, similar to the product of following SQL:

SELECT
charges.date,
charges.amount,
users.name,
user_types.name
FROM
charges
LEFT OUTER JOIN users ON users.id = charges.user_id
LEFT OUTER JOIN user_types ON user_types.id = users.user_type_id


I know I can create API call with this SQL query on server, but I would like to avoid that because tables are already loaded in the web app.

What is the simplest way to join them in memory?

Answer

If small library is OK, this can be done with StrelkiJS:

var users = new StrelkiJS.IndexedArray();
users.loadArray([
        { id:   "u0001", name: "John",      user_type_id: "1" },
        { id:   "u0002", name: "Bob",       user_type_id: "1" },
        { id:   "u0003", name: "Alice",     user_type_id: "5" },
        { id:   "u0004", name: "Jennifer",  user_type_id: "5" },
        // ... more
    ]);
var user_types = new StrelkiJS.IndexedArray();
user_types.loadArray([
        { id: "1", name: "Regular Clients"},
        { id: "5", name: "VIP Clients"},
        // ... more
    ]);
var charges = new StrelkiJS.IndexedArray();
charges.loadArray([
        { id: "7443", user_id: "u0001", date: "2016-01-01", amount: "3.99", },
        { id: "7445", user_id: "u0001", date: "2016-01-01", amount: "4.02", },
        { id: "7448", user_id: "u0001", date: "2016-01-01", amount: "6.99", },
        { id: "7453", user_id: "u0003", date: "2016-01-01", amount: "3.00", },
        { id: "7469", user_id: null   , date: "2016-01-01", amount: "3.99", },
        // ... more
    ]);

var result = charges.query([{
    from_col: "user_id",
    to_table: users,
    to_col: "id",
    type: "outer",
    join: [{
        from_col: "user_type_id",
        to_table: user_types,
        to_col: "id",
        type: "outer",
    }]
}])

Result will be joined array of following structure:

[
    [
        {"id":"7443","user_id":"u0001","date":"2016-01-01","amount":"3.99"},
        {"id":"u0001","name":"John","user_type_id":"1"},
        {"id":"1","name":"Regular Clients"}
    ],
    [
        {"id":"7445","user_id":"u0001","date":"2016-01-01","amount":"4.02"},
        {"id":"u0001","name":"John","user_type_id":"1"},
        {"id":"1","name":"Regular Clients"}
    ],
    [
        {"id":"7448","user_id":"u0001","date":"2016-01-01","amount":"6.99"},
        {"id":"u0001","name":"John","user_type_id":"1"},
        {"id":"1","name":"Regular Clients"}
    ],
    [
        {"id":"7453","user_id":"u0003","date":"2016-01-01","amount":"3.00"},
        {"id":"u0003","name":"Alice","user_type_id":"5"},
        {"id":"5","name":"VIP Clients"}
    ],
    [
        {"id":"7469","user_id":null,"date":"2016-01-01","amount":"3.99"},
        null,
        null
    ]
]
Comments