Question 418363 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How to create hasOne relationship from hasMany but with conditions?

I am trying to create a HasOne relationship out of HasMany relationship but with added conditions.

These are my models

Document.php

class Document extends Model
{
    public function approvals(): HasMany
    {
        return $this->hasMany(Approval::class);
    }

    public function nextApproval(): HasOne
    {
        return $this->hasOne(Approval::class)->where('status', 'pending')->orderBy('created_at', 'asc');
    }
}

Approval.php

class Approval extends Model
{     
    public function documents(): BelongsTo
    {
        return $this->belongsTo(Document::class);
    }
}

Sample Data of Approval

id document_id position_id status
1 1 1 pending
2 1 2 pending

Now in documents index page, I only want to display documents if the logged in user’s position is in the next approval.

$documents = Document::query()
    ->whereHas('nextApproval', fn ($q) => $q->where('position_id', auth()->user()->position_id))
    ->paginate();

Based on the approval data, user with position ID of 2 should not see any documents because the next pending approval is approval with ID of 1, with position ID of 1.
Now if the first user approved the document, the status of the approval will changed to ‘done’, and the next approval should be the user with position ID of 2. So it’s kind of like dynamic HasOne relationship based on the given condition.

I tried these codes:

$this->approvals()->orderBy('created_at', 'asc')->where('status', 'pending')->one();
$this->hasOne(Approval::class)->where('status', 'pending')->oldest('created_at')->limit(1);

And for query:

$documents = Document::query()
    ->whereExists(function ($query) {
        $query->from('approvals')
            ->whereColumn('approvals.document_id', 'documents.id')
            ->where('status', 'pending')
            ->where('position_id', auth()->user()->position_id)
            ->orderBy('created_at', 'asc');
        });
->paginate();

None of these seems to work. latestOfMany and oldestOfMany doesn’t work either because it always gets either latest of oldest and disregards the given conditions.
If its not possible, are there any suggestions on how to achieve this?

Tags:

2

Answers


  1. 0 
    $position = Auth::user()->position_id;


$documents = Document::whereHas('approvals', function ($query) use ( $position) {
    $query->where('position_id',$position)
          ->where('status', 'pending')
          ->orderBy('created_at', 'asc'); 
})->get();
    Login or Signup to reply.
  2. 0

    the part where you are stuck is that you are applying your position filtering on the relation query, while you need to first "find" the next approval and than check it’s position value.

    To do this you can use a subSelect to calculate the value you want to filter on:

    Document::query()
  ->select()
  ->addSelect([
    'oldest_pending_approval_position_id' => Approval::query()
      ->whereColumn('approvals.document_id', 'documents.id')
      ->where('status', 'pending')
      ->oldest()
      ->select('approvals.position_id')
      ->limit(1)
  ])
  // filter on the calculated column
  ->where('oldest_pending_approval_position_id', $request->user()->position_id)
  ->paginate();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search