Stored procedures and fetching data as JSON in MySQL

1. GROUP CONCAT for fetching database

Let’s consider that we have two different relations, namely users and posts. A user can have multiple posts.

Now, being used to with ORM (Object Relational Mapping) such as Laravel Eloquent, what we usually do is define two models for each of the two entities and define relations between the two relations through Eloquent’s relations. If we had to fetch a user with all of its posts, what we usually do is fetch the user from database and then using the Eloquent relations defined in the User model, we fetch all the posts related to the users.

// User.php
class User extends Model {
  // 
  public function posts(){
    return $this->hasMany(Post::class, ‘user_id’ , ‘id’);
  }
}
//Post.php
class Post extends Model
{
  //
 
  public function user(){
    return $this->belongsTo(User::class, ‘users_id’ , ‘id’);
  }
}

This is generally a good approach for fetching data where each table entries is represented by a model. However, this also requires an extra query to the database. Remember that most of the performance lagging in big applications are due to high I/O wait times ( which may be time to read some file, time to fetch data from some api, time to fetch data from database etc. ). Hence, it is very crucial to optimize the application architecture so as to reduce those types of I/O waits. One of such cases is reducing the number of queries in our application.

Now, getting back to our above example, we have already fired two queries to fetch a user and all of the user’s posts. One method to fetch the user and all of the posts in a single query might be as below:

SELECT * , ( 
SELECT 
  CONCAT("[",
    GROUP_CONCAT(
      JSON_OBJECT(
        “id" , posts.id,
        “title” , posts.title,
        “slug” , posts.slug,
        “created_at” , posts.created_at, 
        “updated_at” , posts.updated_at
      ) 
    ),
    "]"
  )
FROM posts
WHERE posts.user_id = users.id
GROUP BY posts.user_id
) as posts
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id

Here, the above query will generate data as follow:

id | name     | address   | posts
1  | birendra | Kathmandu | [{”id” : 1, “title”: ”Using GROUP BY in MySQL”, “slug”:”using-group-by-in-mysql” , “created_at”: “2076–02–02 02:02:02”, “updated_at”:“2076–02–02 02:02:02”}]”

So, how to use this in Laravel models?

We can use query scopes methods for this. On our User model, lets add a methods withPosts

public function scopeWithPosts($query)
{
    return $query->select('*')
          ->selectRaw(
<<<EOF 
     (SELECT
         CONCAT("[",
           GROUP_CONCAT(
             JSON_OBJECT(
               “id" , posts.id,
               “title” , posts.title,
               “slug” , posts.slug,
               “created_at” , posts.created_at, 
               “updated_at” , posts.updated_at
             ) 
          ),
          "]"
        )
      ) as posts
EOF
    );
}

Now, we can fetch users with their posts as json string by using the query scope methods in eloquent model as follows:

$usersWithPosts = User::withPosts()->get();

2. Stored Procedures

A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. A procedure has a name, a parameter list, and SQL statement(s). Stored procedure can be useful when we need to perform multiple operations in the database in a single database call. Let’s consider an example where we’ll have to create a new record, insert another row in another relation and fetch the data in a single query call.

CREATE PROCEDURE create_user(IN userData JSON, IN hobbies JSON)
BEGIN
DECLARE data_first_name VARCHAR(50);
DECLARE data_last_name VARCHAR(50);
DECLARE data_address VARCHAR(50);
DECLARE data_hobby_name VARCHAR(50);
DECLARE last_inserted_user_id BIGINT(11);
DECLARE last_inserted_hobby_id BIGINT(11);
DECLARE user_hobby_json LONGTEXT;
SET data_first_name = userData ->> “$.first_name”;
SET data_last_name = userData ->> “$.last_name”;
SET data_address = userData ->> “$.address”;
SET data_hobby_name = hobbyData ->> “$.hobby_name”;
INSERT INTO users(first_name, last_name, address, created_at, updated_at) VALUES(data_first_name, data_last_name, data_address, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP());
SET last_inserted_user_id = LAST_INSERT_ID();
INSERT INTO user_hobbies(hobby_name, user_id, created_at, updated_at) VALUES(data_hobby_name, last_inserted_user_id, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP());
SET last_inserted_hobby_id = LAST_INSERT_ID();
SET user_hobby = SELECT JSON_OBJECT(
    "id", id,
    "hobby_name", hobby_name,
    "user_id" , user_id,
    "created_at" , created_at,
    "updated_at" , updated_at
) FROM user_hobbies 
WHERE id = last_inserted_hobby_id;
SELECT * , user_hobby
FROM users
WHERE id = last_inserted_user_id
END

In the above procedure, we have performed two inserts and two select operations against the database; which has resulted on single call to the database for multiple operations.

To call this procedure from our code, we’ll pass json encoded data to the procedure as follows:

$userData = json_encode([
    'first_name' => 'Birendra',
    'last_name'  => 'Gurung',
    'address'    => 'Kathmandu'
]);
$hobby = json_encode(['hobby_name' => 'FIFA']);
$user = DB::select('CALL create_user(:user,:hobby)', ['user' => $userData , 'hobby' => $hobby])

MySQL Stored Procedure Tutorial

If you are interested, Here’s a post from our colleague.

Intercepting navigations in a Webview — Flutter