SQL: One to Many Join Query in an Optimal way with Node.js

#sql #node #javascript #tutorial

For the past few years, one of the queries that I needed most was making one too many queries to return a more informative response back to the client.

So what is One to Many Relationships in SQL?

Formally, A One to many relationship is a type of cardinality that refers to the relationship between two entities A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A.

Along with One to Many, we Have One to One and Many to Many Relationships as well but we are on focusing on One To Many in this Article.
One to One relations are easier as only one record is connected with another one, You could retrieve the data you want in both tables with a single join.
Many to Many Relation is made of two One To Many Relationships so the Idea we are discussing here can be applied to that as well.

So What are we discussing here?
Imagine you have a User and User Hobbies Table, Each User Has Many Hobbies and Each Hobby belongs to only one user.
If you make a query over the Hobbies table then I could easily Join it with Users to retrieve the User's information related to a Hobby.
But is it helpful? a list of Hobbies? Actually in most cases No, You want a List of Users with their Hobbies and we preferably want to have an array of User Objects Each one of them should have a property which is an Array of Hobbies embedded in the user object.

Ok, but can't we just make the Join on the Users table with the Hobbies to fetch the Hobbies with the respect to the user? Again No, SQL does not work that way and it is not like NoSQL databases and does not support this kind of Query.

Hmmm using a backend language like Node.js or any other, To Find a List of Users in a separate Query and then Loop through all of them and each iteration to query on the hobbies table and embed it? This Actually Works, you will have the correct result but this is one of the worst approaches as you are hitting the Database in a Loop.

So let's do this in an Optimal Way that does not hurt our DB and we are not making too many connections to the DB and yet we achieve the same output.

First, Am gonna Create these two tables with the below schema on SQL , they are in the simplest form.

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `user_hobbies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL DEFAULT '0',
  `hobby_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Now Tables are Created, I will put some Dummy data into them so that we will have something to work with.

You may have noticed that I am adding User_id without having SQL relationships, its works that way, and it's preferable and we will discuss why in another article.

ok let's start coding

init new Node.js Project
I will be using two packages
1. Knex.js as Query Builder
2. Lodash for Computation
Start

First I will be creating a Function and Fetch All of the Users, Please refer to Knex.js Documentation to know how to make connections and start querying.

const _ = require('lodash');
const lodash = require('lodash');
const database = require('/database/connection'); //DB Connection

function getAllUsersWithHobbies() {}

getAllUsersWithHobbies()

Lets Fetch All of the Users first.

async function getAllUsersWithHobbies() {
    const users = await database("user").select();    
}

Now you have an Array of Users , So what should you do next is to extract their Ids.

async function getAllUsersWithHobbies() {
    const users = await database("user").select();  //[ { id: 1, name: 'Ali' } ]
    const usersIds = _.map(users, (el) => el.id);  //[ 1 ]
}

Perfect , now we make a query on the Hobbies to find all the Hobbies that their user_id is in our UserIds variable.

async function getAllUsersWithHobbies() {
    const users = await database("user").select();  
    const usersIds = _.map(users, (el) => el.id);  
    const Hobbies = await database('user_hobbies').select().whereIn('user_id', usersIds) //returns 2
}

The next step is to Group The Hobbies By User_id , using GroupBy Function from Lodash ... it will return an Object that has user_id as keys and Array of the Hobbies belongs to a User as value.


async function getAllUsersWithHobbies() {
    const users = await database("user").select();  
    const usersIds = _.map(users, (el) => el.id);  
    const Hobbies = await database('user_hobbies').select().whereIn('user_id', usersIds) 
    const groupedHobbies = _.groupBy(Hobbies, 'user_id');

    // groupedHobbies: {
    //     '1': [
    //        { id: 1, user_id: 1, hobby_name: 'First Hobby' },
    //        { id: 2, user_id: 1, hobby_name: 'Second Hobby' }
    //     ]
    //   }
}

Finally We loop through our users and we can point to the Hobbies object with the user.id

async function getAllUsersWithHobbies() {
    const users = await database("user").select();  
    const usersIds = _.map(users, (el) => el.id);  
    const Hobbies = await database('user_hobbies').select().whereIn('user_id', usersIds) 
    const groupedHobbies = _.groupBy(Hobbies, 'user_id');

    const usersEmbedded = _.map(users, (record) => {
            return {
                ...record,
                hobbies: groupedHobbies[record.id] ,
            };
        }); 

       return usersEmbedded;
}

an Example of final output :

Well, we have achieved the exact output we have wanted by hitting the database only twice to retrieve all the hobbies for all the users and embed them as well.

This is a simple technique yet so powerful that you want to do all of your embedded this way.

Thanks for reading this Article