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

Just to be clear, are you wanting to get the tasks that have been assigned to a user that have either been completed today or not completed at all? — Rwd, Nov 25 '18 at 08:08
@RossWilson Please refer to this question (https://stackoverflow.com/questions/53464283/laravel-is-it-possible-to-do-orwheremodelfind) A task can be created by one user and assigned to many users, there's a many to many relationship between tasks and users — Lynob, Nov 25 '18 at 09:29
@RossWilson I want to find all tasks that are either created by or assigned to a user, if they are not completed or if they are closed today. So a user can see all of his open tasks + the tasks that he closed today, if the tasks are open, they should be sorted by the one having the worst due date, meaning by urgency — Lynob, Nov 25 '18 at 09:33
Please could you add your migrations (or just the table structures) for `tasks` and `task_user`? — Rwd, Nov 25 '18 at 09:43

score 1 · Accepted Answer · answered Nov 25 '18 at 17:12

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

Marc · Answer 2 · 2018-11-26T08:25:23.827

0

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

edited Nov 26 '18 at 08:25

answered Nov 23 '18 at 11:12

Marc

5,109
2
32
41

so then, to get the task id from user, it becomes `task.user.id`? – Lynob Nov 23 '18 at 12:34
I see, I went over what you posted again, I have updated my answer with what I think i can understand you want.. – Marc Nov 23 '18 at 13:10
Your second answer is exactly what I want Sir, the only thing is that it gets all tasks for all users, I want to get all tasks for only the authorized user. – Lynob Nov 23 '18 at 13:27
So you mean the date closed query? Auth:user()->tasks()->where('is_done',1)->orWhere('closed_dated', Carbon::today()) ? You gust need to mod that to group the OR to include domain_id – Marc Nov 23 '18 at 13:29
I added a conclusion, I think that is possible but i never had to write such a nested query before.. – Marc Nov 23 '18 at 13:52
Interesting, not sure that works, will give it a try tonight and let you know – Lynob Nov 23 '18 at 14:18

Laravel: How to get records from a pivot table?

2 Answers2