CODEWITHSUNDEEP
javascriptsqlarrays
Zach A.
Zach A.

Reputation: 33

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?

Upvotes: 2

Views: 124

Answers (5)

Nina Scholz
Nina Scholz

Reputation: 386858

This proposal features IMTheNachoMan solution, with the extension of generation necessary objects from the given data.

It includes all rows of charges, because with SQL, the rows are returned too.

The problem with null values are here tested and null is then returned.

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" }],
    user_types = [{ id: "1", name: "Regular Clients" }, { id: "5", name: "VIP Clients" }],
    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", }],
    user = Object.create(null),
    type = Object.create(null),
    result;

users.forEach(function (u) {
    user[u.id] = u;
});

user_types.forEach(function (t) {
    type[t.id] = t;
});

result = charges.map(function (charge) {
    return {
        'charges.date': charge.date,
        'charges.amount': charge.amount,
        'users.name': charge.user_id === null ? null : user[charge.user_id].name,
        'user_types': charge.user_id === null ? null : type[user[charge.user_id].user_type_id].name,
    };
});

console.log(result);

Upvotes: 1

amaksr
amaksr

Reputation: 7745

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
    ]
]

Upvotes: 1

Rhumborl
Rhumborl

Reputation: 16609

The only way to do this without restructuring the objects is to loop and filter. You can optimise it slightly by processing the users and their types first, but that's about it...

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
];

// pre-process users
var usersPlusTypes = users.map(function(u) {
  var foundUserTypes = user_types.filter(function(ut) {
    return ut.id == u.user_type_id;
  });

  return {
    id: u.id,
    user: u,
    userType: foundUserTypes.length ? foundUserTypes[0] : null
  }
})

// now link charges to users
var results = charges.map(function(c) {
  var user = usersPlusTypes.filter(function(upt) {
    return upt.id == c.user_id;
  });
  
  return {
    date: c.date,
    amount: c.amount,
    userName: user.length ? user[0].user.name : null,
    userTypeName: user.length && user[0].userType ? user[0].userType.name : null,
  };
});

console.log(results);

Upvotes: 0

IMTheNachoMan
IMTheNachoMan

Reputation: 5839

If you can modify the way users and user_types are being populated then you can do this pretty quickly.

You would need to change users and user_types to objects so you have something like this:

// make users an object with the id as the key
var users = {
	"u0001" : { name: "John",      user_type_id: "1" },
	"u0002" : { name: "Bob",       user_type_id: "1" },
	"u0003" : { name: "Alice",     user_type_id: "5" },
	"u0004" : { name: "Jennifer",  user_type_id: "5" }
};

// same for user_types
var user_types = {
	"1" : { name: "Regular Clients" },
	"5" : { name: "VIP Clients" }
};

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", }
];

// now you can just loop through and use object key lookups:

var out = [];

for(var i = 0, numCharges = charges.length; i < numCharges; ++i)
{
	var currentCharge = charges[i];
	
	if(currentCharge.user_id === null) continue;
	
	out.push([
		currentCharge.date,
		currentCharge.amount,
        
        // get the current charges user_id and look up the name from users
		users[currentCharge.user_id].name,
        
        // same as above but use the user_type_id to get the user_type name
		user_types[users[currentCharge.user_id].user_type_id].name
	]);
}

console.log(out);

Upvotes: 1

Sergiu Paraschiv
Sergiu Paraschiv

Reputation: 10163

Make users a map, so you can use users['u0001']. Then loop through charges and do users[current_charge.user_id].charges.push(current_charge). Each user in users should have a charges property initialized as an empty array. You can do that when you turn the users array into a id => user map.

You don't need anything special here, just two loops through users and charges:

var users_map = {};
var i;
for(i = 0; i < users.length; i++) {
    users_map[users[i].id] = users[i];
    users_map[users[i].id].charges = [];
}

for(i = 0; i < charges.length; i++) {
    users_map[charge[i].user_id].charges.push(charge[i]);
}

If you really need the final "result" to be an array, not a map, you can loop through users_map again and turn it into an array. A really simple solution leveraging modern JS stuff would be this:

var joined_data = Object.keys(users_map).map(function (key) {
    return users_map[key];
});

You can make the above code a lot prettier with lodash or another similar library.

Upvotes: 0

Related Questions