Nested Query Question

Hello, I have a three Eloquent Models which are Linked like this: A HasMany B HasMany C

And I want to get all A's which have B's where the latest C is older than lets say 30 day. There are multiple C's for a B and I am only interested in the latest (via created_at column)

Does anyone know how to fetch this data with a single SQL Statement?

I tried something like this but I don't know how I should structure the C Query part...

A::query()
 ->whereHas('bs', fn(HasMany $query) => $query
      ->whereHas('cs', fn(HasMany $query) => $query)
);

LaryAI

3 years ago

Level 58

You can use the whereHasMorph method to query the latest C record for each B record.

A::query()
    ->whereHas('bs', fn(HasMany $query) => $query
        ->whereHasMorph('cs', C::class, fn(HasMany $query) => $query
            ->where('created_at', '<', now()->subDays(30))
            ->latest()
        )
    );

markus.heb

3 years ago

Level 7

Hi Lary, thanks for your quick answer ;) But this would only give me a C which is older than 30 days. But I want to ensure that the latest C is older than 30 days.

Nice try A.I :P

markus.heb

3 years ago

Best Answer

Level 7

After some tinkering I found the solution.

A::query()
    ->whereIn('id', B::query()
       ->select('a_id')
        ->whereIn('id', C::query()
               ->select('b_id')
                ->groupBy('b_id')
                ->havingRaw('MAX(created_at) < ?', [now()->subDays(30)])
         )
     )

Please or to participate in this conversation.