mardi 12 avril 2016

Laravel 5.1 select only rows which do not belong to user

I have a role based application which has timesheets, supervisors and users.

Timesheets belong to users.

Users are assigned to supervisors and therefore supervisors are able to approve users timesheets who they are assigned to.

The problem is that supervisors can also submit their own timesheets.

I want to be able to select timesheets that are assigned to the supervisor but do not belong to the supervisor (i.e they didn't submit the timesheet).

I have the following model relationship setup:

/**
 * The user that owns the timesheet.
 *
 * @return Object
 */
public function user()
{
    return $this->belongsTo('App\Models\User\User');
}

/**
 * The supervisor that approved the timesheet.
 *
 * @return Object
 */
public function supervisor()
{
    return $this->belongsTo('App\Models\User\User');
}

The code I have at the moment is:

$this->timesheet->whereHas('user.supervisors', function ($query) {
    $query->where('timesheet.status', 'Submitted');
})->get();

But the above code, also selects timesheets that the supervisor submitted themselves as well as ones they are assigned to.

I have tried the following code to only get timesheets that are assigned to the supervisor...

$this->timesheet->whereHas('user.supervisors', function ($query) {
    $query->where('timesheet.status', 'Submitted');
    $query->where('user.supervisors.id', '<>', 'timesheet.user_id');
})->get();

...however, the above code throws an error;

[2016-04-12 11:45:15] local.ERROR: exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.supervisors.id' in 'where clause'' in /home/vagrant/Code/App/public/vendor/laravel/framework/src/Illuminate/Database/Connection.php:319

How can I achieve what I need?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire