Joining tables in a query

Hi,

I currently have a comments table and a user table. I would like to join the 2 tables up and get the 'id', 'users_id','username','post_id','content','created_at', 'updated_at'. But I also don't want to join the entire user table, maybe just add the username and email column.

I get the error: A subquery must be a query builder instance, a Closure, or a string.

User migration

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('username')->unique();
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

Comments migration

public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('user_id')->unsigned();
            $table->bigInteger('post_id')->unsigned();
            $table->bigInteger('parent_id')->unsigned()->default(0);
            $table->string('content');

            $table->foreign('user_id')->references('id')->on('users')
                ->onDelete('cascade')->onUpdate('cascade');
            $table->foreign('post_id')->references('id')->on('posts')
                ->onDelete('cascade')->onUpdate('cascade');
            $table->timestamps();
        });
    }

tykus

5 years ago

Level 104

leftJoinSub expects a sub-query (a Builder instance), but you have given a completed query (using get()). To be honest, a sub-query offers little value in your case:

public function pageComments($post_id)
{
    return Comment::selectRaw('comments.*, users.username')
        ->where('post_id', '=', $post_id)
        ->leftJoin('users', 'comments.user_id', '=', 'users.id')
        ->get();
}

Please or to participate in this conversation.