Function for inner and outer joins

[michael-felden]

/**
* 	Left and inner join 
* 	eqJoin enhanced
* 
* 	simplified for readability
*/
function join(left, leftJoinKey, right, rightJoinKey, mapFun, isOuter){
	
	var leftData = left.data(),
	rightData = right.data();
	
	var leftDataLength = leftData.length,
	rightDataLength = rightData.length;

	var	joinMap = {},
	result = [];
	
	for (var i = 0; i < rightDataLength; i++) {
		key = rightData[i][rightJoinKey];
		if( typeof joinMap[key] == "undefined"){
			joinMap[key] = [];	
		}
		joinMap[key].push(rightData[i]);
	}
  		
	for (var j = 0; j < leftDataLength; j++) {
		key = leftData[j][leftJoinKey];

		if(typeof joinMap[key] != "undefined"){
			joinMap[key].forEach(function(joinData){
				result.push(mapFun(leftData[j], joinData));	
			});
		}else{	
			if(isOuter){ 		
				result.push(mapFun(leftData[j], joinMap[key] || {}));
			}
		}
	}
    return result;
}
		
var db = new loki();

var person = db.addCollection('person');
person.insert({ id: 1, name: 'Peter' });
person.insert({ id: 2, name: 'Paul' });
person.insert({ id: 3, name: 'Mary' });

var pet = db.addCollection('pet');
pet.insert({ id_person: 1, animal: 'dog' });
pet.insert({ id_person: 3, animal: 'cat' });
pet.insert({ id_person: 3, animal: 'dog' });

var rsPerson = person.chain().simplesort('id'),
rsPet = pet.chain(),
isOuter = true;

var mapPersonPet = function(person, pet) {
  return {
    id: person.id,
    name: person.name,
    pet: pet.animal    
  };
};

var rsJoin = join(rsPerson, "id", rsPet, "id_person", mapPersonPet, isOuter);

console.log(rsJoin);
/*
0:{id: 1, name: "Peter", pet: "dog"}
1:{id: 2, name: "Paul", pet: undefined}
2:{id: 3, name: "Mary", pet: "cat"}
3:{id: 3, name: "Mary", pet: "dog"}
*/

The result is what you would expect from sql.
Small changes to eqJoin would suffice.

What do you think?

[obeliskos]

Hi @michael-felden, so can you explain whether there are edge cases that your rework supports that the eqJoin doesn't? Or how is the "result" more like what I would expect from sql.

It's too late to change the eqJoin prototype method signature and functionality unless we just add a new param(s)... and the method would have to support full backwards compatibility and unit tests.

Current eqJoin hangs off resultset prototype and has implicit left data being the results in the resultset. As for the right data, eqJoin can accept raw data array, a collection or resultset. I also believe it supports left outer join and cross join.

Let me know your usability concerns or if this is intended to be cleanup of internal code within that method.

[michael-felden]

To demonstrate the difference here with eqJoin:

rsJoin = person.eqJoin(pet, "id", "id_person", mapPersonPet).data();
console.log(rsJoin);
0:{id: 1, name: "Peter", pet: "dog", meta: {…}, $loki: 1}
1:{id: 2, name: "Paul", pet: undefined, meta: {…}, $loki: 2}
2:{id: 3, name: "Mary", pet: "dog", meta: {…}, $loki: 3}

In this resultset Mary got only a dog. The cat is lost!

Why not the other way round? How can I know?
Frankly this behavoir renders eqJoin pretty useless for me.

Or am I missing something?

[obeliskos]

Sorry, I have not had much time to research this until now...

I do see now the functionality difference you are describing and agree the current eqJoin functionality supports only single value 'match' on right.

Perhaps we could add an 'outerJoin' or 'leftOuterJoin' method that behaves with the functionality you describe.

Feel free to submit a pull request to incorporate your method into resultset class if you are still interested. We should probably have a unit test which demonstrates your expected usage. npm install and npm run test can be used locally to verify.

Ideally it would be nice to support the same variable 'right' side where you can pass in Resultset, Collection, or doc array.

If you do not feel comfortable with that I can add to my todo list but that might take a while to get to.

[michael-felden]

For my current project there was a need for a multi collection join like in SQL. So I wrote a little class for that. That kind of solution I would engage in if could find some spare time.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[patarapolw]

I think your code on the OP is broken, because joinMap[key] is any[], but is used in mapFun as any.

Anyways, fixed and tested -- LokiJS-Forge/LokiDB#167