Laravel - Accessing model value inside Query Builder

Question

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?

Hey, could you also share the code where you don't set documentTypesByRequester statically? I want to give this query a try, but with static data it's a bit tricky and I might unnecessarily over-complicate stuff. — Andrius Rimkus
– Andrius Rimkus, Commented Sep 18, 2018 at 16:05
Can a flow have multiple documents of the same type (per requester)? — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Sep 18, 2018 at 22:42

Jonas Staudenmeir · Accepted Answer · 2018-09-19 12:44:14Z

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

answered Sep 19, 2018 at 12:44

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Alexandre Demelas Over a year ago

Great trick! but this doesn't works because of line DB::raw('('.(clone $documentTypes)->selectRaw('count(*)')->toSql().')') that converts to a sql string instead of a number that's what the parameter expects...

Jonas Staudenmeir Over a year ago

Did you try the query?

Alexandre Demelas Over a year ago

Look at Laravel API: whereHas(string $relation, Closure $callback, string $operator = '>=', int $count = 1)

Jonas Staudenmeir Over a year ago

You can still pass a raw expression. Replace ->get() with ->toSql() and look at the result.

Alexandre Demelas Over a year ago

There's no way to execute this select before and pass his return value to the integer parameter?

|

Collectives™ on Stack Overflow

Laravel - Accessing model value inside Query Builder

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related