Optimize query

Laravel : One To Many Relation in Single Query

What we want

In database structure when a main table has many child table in one to many relation, like user has different skills as a child table

we can easily get skills in users listing, but what if user has multiple tables like skills, educations, experiences, certificates as a child table

We can easily get all details of specific user, but what if we have to get all the details with listing of user, That can only possible by NoSQL structure, there we can store all details in hierarchical structure

There is a way to get a hierarchical record in SQL, a little tricky ;)

Logic Behind the scene

To achieve that, let’s understand the logic behind the scene.

SQL return query result in table structure, we cannot get records in hierarchical structure, but we can convert all user skills, educations, certifications and experiences in each column as json encoded string, After the result we can easily convert them to arrays.

MySQL already providing JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregate function, but it was introduced in MySQL 5.7.22

Start to code

Check JSON_QUOTE function

First of all we need to cover any value using double quotes, we have to use MySQL function named “JSON_QUOTE”, that we will use it in building of JSON encoded string

Build Query

This will be our query, let try to understand the query, First we will select all records

select * from users

Then we need to join your child tables to our main table with group by users.id

select
  `users`.*,
  CONCAT( '[', GROUP_CONCAT( 
    DISTINCT CONCAT(
      '{',
      '"degree": ', JSON_QUOTE(IFNULL(edu.degree, '')), ',',
      '"start_year": ', JSON_QUOTE(IFNULL(edu.start_year, '')), ',',
      '"end_year": ', JSON_QUOTE(IFNULL(edu.end_year, '')), ',',
      '"school": ', JSON_QUOTE(IFNULL(edu.school, '')), ',', '}'
    ) SEPARATOR ','
  ), ']' ) as educations
from
  `users`
  left join `skills` on `skills`.`user_id` = `users`.`id`
  left join `education` as edu on `edu`.`user_id` = `users`.`id`
  left join `experiences` on `experiences`.`user_id` = `users`.`id`
  left join `certificates` on `certificates`.`user_id` = `users`.`id`
group by
  `users`.`id`

Now will create about query in Laravel

The difficult part in above query is to build the JSON encoded string, to make that easy I have created a dynamic function, just by passing key value argument you can get the string of group_concat function given as below

Now its time to create your API where we get all skills, educations, certifications and experiences in as single query

Conclusion

In conclusion, by using this function you can directly get the group concat query also in a single query we can get all the details of users with their child tables.

Demo code is available at “one-to-many-demo”