Laravel – Accessing model value inside Query Builder

I have five tables: requesters, document types, protocols, flows and documents.

Relationships

  • Requester has many document types.
  • Requester has many protocols.
  • Protocol has many flows.
  • Flow has many documents.
  • Document type has many documents.

Problem

Select all protocols that have the first flow (let’s say sequence = 1) with documents having all document types for protocol requester.

In other words, select the protocols that doesn’t have pendencies, this means protocols with documents inside first flow having all required document types for protocol requester.

Solution

I ended up with a solution that actually doesn’t work because I can’t access requester_id value inside protocols whereHas Closure:

// All document types id's group by requester id's.
$documentTypesByRequester = DocumentType::all()
  ->groupBy('requester_id')
  ->map(function ($documentTypes, $requesterId) {
      return $documentTypes->pluck('id')
             ->toArray();
  })->toArray();
// Defined statically to reduce the complexity of question.
// $documentTypesByRequester = [
//    1 => [1, 2, 3],
//    2 => [4, 5]
// ];
// Here I have to select all protocols that have flows with
// documents having all document types of protocol requester.
$protocols = Protocol::whereHas('flows', function ($flows) use ($documentTypesByRequester) {
    // 
    // *---------------------------------------
    // * THIS DOESN'T WORK BUT IS WHAT I NEED!
    // *---------------------------------------
    // 
    // Access the requester id for current protocol.
    $requesterId = $flows->first()
        ->protocol
        ->requester_id;
    $documentTypesId = $documentTypesByRequester[$requesterId];

    return $flows->where('sequence', 1)
        ->whereHas('documents', function ($documents) use ($documentTypesId) {
            return $documents->whereIn('document_type_id', $documentTypesId);
        }, '=', count($documentTypesId));
})->get();

There is some way to access values of model inside this whereHas Closure or exists another alternative to help me solve this query?

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

Try this:

$documentTypes = DocumentType::whereColumn('requester_id', 'protocol.requester_id');
$protocols = Protocol::whereHas('flows', function ($query) use ($documentTypes) {
    $query->where('sequence', 1)
        ->whereHas('documents', function ($query) use ($documentTypes) {
            $query->select(DB::raw('count(distinct document_type_id)'))
                ->whereIn('document_type_id', (clone $documentTypes)->select('id'));
        }, '=', DB::raw('('.(clone $documentTypes)->selectRaw('count(*)')->toSql().')'));
})->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