DEV Community

Cover image for SQL: One to Many Join Query in an Optimal way with Node.js
Ali Amjad
Ali Amjad

Posted on

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

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`)
);
Enter fullscreen mode Exit fullscreen mode

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

Image description

Image description

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()
Enter fullscreen mode Exit fullscreen mode

Lets Fetch All of the Users first.

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

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 ]
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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' }
    //     ]
    //   }
}

Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

an Example of final output :

Image description

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

Discussion (2)

Collapse
aramrafeq profile image
aram

Nice article Ali, try writing it in kurdish as well we are more than happy to publish it in devs.krd

Collapse
ali_a_koye profile image
Ali Amjad Author

for sure i will