Laravel:如何使用数据透视表获取记录? [英] 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:

• is_done = 1
的任务

• 昨天或之前完成的任务，不包括今天完成的任务

• A task that is_done = 1

• A task that was done yesterday or before, not including tasks finished today

用户只能看到他创建或分配给他的已归档任务 如果用户分配了任务，则将其ID存储在数据透视表中 桌子

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

Task.php模型

Task.php model

public function taskUsers()
{
    return $this->hasMany('App\Models\Tasks\UserTask')->where('role',1);
}

UserTask.php模型什么都不包含，一个空模型

UserTask.php model contains nothing, an empty model

class UserTask extends BaseModel { }

迁移

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

和

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

分别为实际表

和

我的代码:

助手在下面

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

动作:

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

我只是得到一个OK状态，没有结果，tasks数组为null，尽管我的代码似乎是正确的，并且表包含1条应该返回的记录.

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.

推荐答案

您在getArchived方法中缺少return语句.此外，orWhere条件似乎不合适.如果您还需要同时应用第三个条件，则必须使用参数分组 .否则查询将读取(1 && 2 && 3 && 4) || 5，而您需要1 && 2 && 3 && (4 || 5).

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

这篇关于Laravel:如何使用数据透视表获取记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！