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();

Answer 2

The following blog post covers a one to many task assignment example.

https://medium.com/@brice_hartmann/building-a-user-based-task-list-application-in-laravel-eff4a07e2688

Getting tasks for current user, Relation on belongsToMany to the User model would be:

Auth::user()->tasks()->where('is_done',0) .... ->get();

Getting tasks with users:

Tasks::with(['user'])->where('is_done',1) ... ->get();

Authed user conclusion ... I am not 100% sure this is correct:

Auth::user()->tasks()
    ->where('domain_id', $domainId)
    ->where(function ($query) {
        $query->where('is_done', 1)
              ->orWhere(function($query) {
                 $query->where('is_done', 0)
                       ->where('closed_dated', Carbon::today())
            });
        });
    })
    ->get();