Column not found: 1054 Unknown column 'comments.commenter

Here is my query with relation, and I am fetching data with comments and with comments I need user detail too, but if I do like below I am getting the above error.

$type = 'success';
        $status_code = 200;
        $message = 'Posts data listed.';

        $response = Post::with(['associate:id,name,avatar', 'comments:id,commenter_id,commentable_id,comment,created_at'])
            ->join('users', 'users.id', '=', 'comments.commenter_id');

        if (request()->query('search')) {
            // $response = $response->where("name", "LIKE", "%" . request()->query('search') . "%");
            $response = $response->search(request()->query('search'));
        };
        $response = $response->latest('posts.created_at')->paginate(5);

        return response_data($type, $status_code, $message, $response);

CodePudding user response：

When you use ->with method, eloquent will fetch comments after executing main query (Means Post::...->join()), So if you want using a table in your query, then you should use it in related query.

So use join in with method:

Post::with('associate:id,name,avatar')
->with('comments',function ($query) {
    $query
        // Now you can select from users or comments
        // e.g. ->select([...,'users.any_column_you_want'])
        ->select(['comments.id','commenter_id','commentable_id','comment','created_at'])
        ->join('users', 'users.id', '=', 'comments.commenter_id');
});

Check reference: Eager Loading and Constraining Eager Loads.

Check mysql join reference for a better understanding: W3Schools Mysql Join