Laravel: How to get records from a pivot table?

Question

I'm using Lumen 5.1, I have many to many relation between tasks and users

Task model

public function user()
{
    return $this->belongsToMany('App\Models\Auth\User', 'task_user');
}

public function domain()
{
    return $this->hasOne('App\Models\Domain', 'domain_id');
}

User model

public function tasks()
{
    return $this->belongsToMany(Task::class, 'task_user');
}

UserTask model

class UserTask {}

I want to search the get the user of the task, my code is

$tasks = Task::Where(function ($query) use ($domainId) {
    $query->where("domain_id", $domainId)
        ->where("is_done", 0)
        ->orwherehas('tasks.user.id', \Auth::id)
        ->orderBy('due_date', 'DESC');
})
->orWhere(function ($query) use ($domainId) {
    $query->where("domain_id", $domainId)
        ->Where("is_done", 1)
        ->Where("closed_dated", Carbon::today())
        ->orwherehas('tasks.user.id', \Auth::id)
        ->orderBy('closed_date', 'ASC');
})
->get();

My question is whereHas correct? Is tasks.user.id correct? Can I get to the user's id that way? I did it that way because of this question

The tech lead tells me that my code is wrong, he would you use where, he said that whereHas when you want to run a closure.

Migrations:

Tasks

public function up()
{
    Schema::create($this->getTable(), function (Blueprint $table) {
        $table->increments('id');
        $table->string('title')->nullable();
        $table->dateTime('submit_date');
        $table->dateTime('closed_date');
        $table->dateTime('due_date');
        $table->tinyInteger('is_done')->default(0);

        $table->integer('domain_id')->unsigned()->nullable();
        $table->foreign('domain_id')->references('id')
            ->on(self::getTableName('domains'))->onDelete('cascade');

        $table->bigInteger('created_by')->unsigned()->nullable();
        $table->foreign('created_by')->references('id')
            ->on(self::getTableName('auth_users', false))->onDelete('cascade');

        $table->bigInteger('closed_by')->unsigned()->nullable();
        $table->foreign('closed_by')->references('id')
            ->on(self::getTableName('auth_users', false))->onDelete('cascade');
        $table->timestamps();
    });

}

public function down()
{
    Schema::drop($this->getTable());
}

task_user

public function up()
{
    Schema::create($this->getTable(), function (Blueprint $table) {
        $table->increments('id');
        $table->integer('task_id')->unsigned()->nullable();
        $table->foreign('task_id')->references('id')
            ->on(self::getTableName('tasks'))
            ->onDelete('cascade');

        $table->bigInteger('user_id')->unsigned()->nullable();
        $table->foreign('user_id')->references('id')
            ->on(self::getTableName('auth_users', false))
            ->onDelete('cascade');
    });

}

public function down()
{
    Schema::drop($this->getTable());
}

Answer 1

No, whereHas would not be correct for both here. Also, you wouldn't be saying whereHas('tasks...') on the Task model.

NB

The 2nd param for whereHas should be a closure (function) and Auth::id should be Auth::id(). You can also use the auth() helper function instead of the Auth facade if you want to.

The following should give you what you want:

$tasks = Task::where("domain_id", $domainId)
    ->where(function ($query) use ($domainId) {
        $query
            ->where("is_done", 0)

            //whereHas - 1st arg = name of the relationship on the model, 2nd arg = closure
            ->whereHas('user', function ($query) {
                $query->where('id', auth()->id());
            });
    })
    ->orWhere(function ($query) use ($domainId) {
        $query
            //If "is_done" = 1 only when it's been closed then you won't need to check "is_done"
            ->where("is_done", 1)
            ->where('closed_by', auth()->id())
            ->whereDate("closed_dated", '>=', Carbon::today()->startOfDay());
    })
    ->orderBy('due_date', 'DESC')
    ->orderBy('closed_date', 'ASC')
    ->get();