I'm trying to return all Threads that a given User has participated in.

The endpoint accepts a userId and supposed to return a collection of thread models.

However, I keep getting this error when executing the controller action. It's looking for a message_id column but I don't have that defined on the thread table or on any table, for that matter - making this a weird error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'thread.message_id' in 'where 
clause' (SQL: select * from `thread` where `thread`.`message_id` = 2 and 
`thread`.`message_id` is not null)"

I believe there might be something off with how I'm linking the tables but I'm not entirely sure. I'd assume the message table's column thread_id should reference the id column on the thread table which's what I thought I was doing in the message migration below.

What am I doing wrong and how can I fix this?

Here's users migration:

Schema::create('users', function (Blueprint $table) {
        $table->id('id');
        $table->string('email')->unique();
        $table->string('full_name');
        $table->string('password');
});

Here's thread migration:

Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
});

Here's message migration:

Schema::create('message', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('user_id');
        $table->unsignedBigInteger('thread_id');
        $table->string('body');
        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade');
        $table->foreign('thread_id')
            ->references('id')
            ->on('thread')
            ->onDelete('cascade');
});

controller action:

public function getUserThreads($userId) {
    $userParticipatedThreads = Message::findOrFail($userId);
    return $userParticipatedThreads->thread;
}

message model:

public function thread() {
    return $this->hasMany(Thread::class);
}

endpoint:

[GET] http://127.0.0.1:8000/api/getUserThreads/2

Route::get('getUserThreads/{userId}', [ThreadController::class, 'getUserThreads']);

CodePudding user response：

Your thread relationship on your Message class is looking for the message_id, since that's the default way the hasMany relationship works. You'll need to override the column that it's basing the relationship off of.

public function thread() {
    return $this->hasMany(Thread::class, 'id', 'thread_id');
}

However, since it looks like the message belongs to one single thread (each message has a thread_id), then you actually want belongsTo instead

public function thread() {
    return $this->belongsTo(Thread::class);
}

CodePudding user response：

To answer the question;

Why am I getting a column not found error when trying to return a collection model based on user ID?

Because your thread table doesn't have a message_id field defined on it.

Schema::create('thread', function (Blueprint $table) {
        $table->id();
        $table->string('title');
});

A Message belongs to a Thread but you seem to have that relationship inverted.

On your Thread model, define a relationship to the Message model:

public function messages()
{
    return $this->hasMany(Message::class);
}

Then you can query for the existance of some message by user:

$messages = Thread::whereHas('messages', function ($query) use ($userId) {
    $query->where('user_id', $userId);
})->get();