2

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?

Improve this question
4
  • Are you willing to use a library or pure JS? And how much data is in these arrays? Are they huge? Commented May 26, 2016 at 15:09
  • You already answered your own question: do it in SQL. Then update your data in your app to match. Commented May 26, 2016 at 15:09
  • Also, how are the arrays being generated/populated? If you can modify how they are being populated you can make things a lot easier when you need to join them. Commented May 26, 2016 at 15:09
  • IMTheNachoMan: small JS library is OK. "Charges" array is usually few hundred records, but should be able to handle few thousands. "User" are few hundreds. Commented May 26, 2016 at 15:14

5 Answers 5

Reset to default
1

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
    ]
]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This looks perfect. Thanks!
1

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

Improve this answer

Comments

1

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.