laravel - How do I add like numbers to my API column?

Question

we are preparing a mobile application with Laravel infrastructure. I am using the related tables in my api source and I want to add the number of user comments and likes to this api source in addition. How can I do that?

In addition, I would like to receive comments posted on related user's topics and user information that gives likes to my api source.

Likes Table: id, user_id, post_id
Comments Table: id, user_id, post_id, description

Function

public function user_posts($id)
{
    return DB::table('posts')
        ->leftJoin('users', 'posts.user_id', '=', 'users.id')
        ->leftJoin('likes', 'likes.user_id', '=', 'users.id')
        ->where('posts.user_id', '=', $id)
        ->select('posts.*', 'posts.user_id', 'posts.description', 'likes.user_id as like_user_id', 'likes.post_id as like_post_id')
        ->get();
}

URL: http://warm.test/api/users/6/posts

Route: Route::get('/users/{id}/posts/like', [LikeController::class, 'like_show'])->name('users.like_show');
Output:

    [
  {
    "id": 6,
    "user_id": 6,
    "image": "https://lorempixel.com/800/400/cats/warem/?39492",
    "description": "Laboriosam rem unde velit nihil. Hic enim quidem necessitatibus eum aut voluptates. Molestiae voluptas voluptas nam neque qui illum. In autem aut illo sunt cumque numquam. Sit quia rerum maiores eius. Et ut aut inventore omnis tenetur ut eveniet delectus.",
    "created_at": "2015-04-04 07:51:39",
    "updated_at": "2020-12-25 08:55:40",
    "like_user_id": 6,
    "like_post_id": 6
  },
  {
    "id": 6,
    "user_id": 6,
    "image": "https://lorempixel.com/800/400/cats/warem/?39492",
    "description": "Laboriosam rem unde velit nihil. Hic enim quidem necessitatibus eum aut voluptates. Molestiae voluptas voluptas nam neque qui illum. In autem aut illo sunt cumque numquam. Sit quia rerum maiores eius. Et ut aut inventore omnis tenetur ut eveniet delectus.",
    "created_at": "2015-04-04 07:51:39",
    "updated_at": "2020-12-25 08:55:40",
    "like_user_id": 6,
    "like_post_id": 5
  }
]

Answer 1

As you said you are on Laravel I recommend you to use Laravel Eloquent, it makes things much easier. In your case we need Post, Like and Comment models.

Like class:

namespace AppModels;

use IlluminateDatabaseEloquentModel;
use AppModelsPost;

class Like extends Model
{
    // Define relationships here
    public function post() 
    {
        return $this->belongsTo(Post::class);
    }
}

Comment class:

namespace AppModels;

use IlluminateDatabaseEloquentModel;
use AppModelsPost;

class Comment extends Model
{
    public function post() 
    {
        return $this->belongsTo(Post::class);
    }
}

Post class:

namespace AppModels;

use IlluminateDatabaseEloquentModel;
use AppModelsLike;
use AppModelsComment;

class Post extends Model
{
    public function likes()
    {
        return $this->hasMany(Like::class);
    }

    public function comments() {
        return $this->hasMany(Comment::class);
    }
}

Your controller action method:

public function userPosts($id)
{
    return Post::withCount('likes', 'comments')->where('user_id', '=', $id)->get();
}

Example output:

    [
  {
    "id": 6,
    "user_id": 6,
    "image": "https://lorempixel.com/800/400/cats/warem/?39492",
    "description": "Laboriosam rem unde velit nihil. Hic enim quidem necessitatibus eum aut voluptates. Molestiae voluptas voluptas nam neque qui illum. In autem aut illo sunt cumque numquam. Sit quia rerum maiores eius. Et ut aut inventore omnis tenetur ut eveniet delectus.",
    "created_at": "2015-04-04 07:51:39",
    "updated_at": "2020-12-25 08:55:40",
    "likes_count": 6,
    "comments_count": 2
  },
    ...
    ]