Laravel: How to get records by using the pivot table?

I have a many to many relationship between task and users, I am trying to get archived tasks, an archived task is:

  • A task that is_done = 1
  • A task that was done yesterday or before, not including tasks finished today
  • A user can only see archived tasks that he created or he was assigned
    to, if a user is assigned to a task, his id is stored in the pivot
    table
  • For reasons outside the scope of the question, I can only get to users by using the pivot table as shown in Task.php below.

Task.php model

public function taskUsers()
{
    return $this->hasMany('AppModelsTasksUserTask')->where('role',1);
}

UserTask.php model contains nothing, an empty model
class UserTask extends BaseModel { }

Migrations
class CreateTasksTable extends Migration
{
    protected $table = 'tasks';
    protected $app_table = true;


    public function up()
    {
        Schema::create($this->getTable(), function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->dateTime('submit_date');
            $table->dateTime('closed_date')->nullable();
            $table->dateTime('due_date')->nullable();
            $table->tinyInteger('is_done')->nullable()->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());
    }
}

and
class CreateTaskUsersTable extends Migration
{
    protected $table = 'task_user';
    protected $app_table = true;
    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');
            $table->integer('role');
        });
    }
    public function down()
    {
        Schema::drop($this->getTable());
    }
}

The actual tables respectively

enter image description here

and

enter image description here

My code:

The helper is below

    public static function getArchived($domainId, $userId)
    {
        Task::where("domain_id", $domainId)
            ->where("is_done", 1)
            ->where("closed_date", '<', Carbon::today()->startOfDay())

            ->where(function ($query) use ($userId) {
                $query->whereHas('taskUsers', function ($query) use ($userId) {
                    $query->where('user_id', $userId);
                });
            })
            ->orWhere(function ($query) use ($userId) {
                $query->where('created_by', $userId);
            })
->get();
    }

The Action:
public function execute()
{
    $domainId = $this->request->get('domain_id');
    $userId = Auth::id();
    $tasks = TasksHelper::getArchived($domainId,$userId);
    return $this->response->statusOk(['tasks' => $tasks]);
}

I just get a status OK, no result, tasks array is null, although my code seems to be correct and the tables contain 1 record which should have been returned.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You’re missing the return statement in your getArchived method. Also, the orWhere condition seems out of place. You must use parameter grouping if you need the 1st three conditions to apply as well. Otherwise the query reads (1 && 2 && 3 && 4) || 5 whereas you need 1 && 2 && 3 && (4 || 5).

public static function getArchived($domainId, $userId)
{
    return Task::where("domain_id", $domainId)
        ->where("is_done", 1)
        ->where("closed_date", '<', Carbon::today()->startOfDay())

        ->where(function ($query) use ($userId) {
            $query->whereHas('taskUsers', function ($query) use ($userId) {
                $query->where('user_id', $userId);
            })->orWhere('created_by', $userId);
        })->get();
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x