laravel - Getting entries from relationship and sub-relationship

Question

I have three tables foo, bar and lorem. They are connected to each other with Many-To-Many relationships.

How can I efficiently get all entries of lorem where foo.id is 123, both directly and through bar (without duplicates) ?

=== EDIT ===

Schema:

Foo -> Lorem          |
                      |----- MERGE
Foo -> Bar -> Lorem   |

I'm currently doing it this way:

# Get lorems from foos
$loremsA = Foo::find(123)->lorems()->get();

# Get ids of bars for given foo:
$bar_ids = Foo::find(123)->bars()->pluck('id')->toArray();
# Get lorems from bars
$loremsB = Lorem::whereHas('bars', fn($q) => $q->whereIn('id', $bar_ids))->get();

# Merge
$loremsB->merge($loremsA)

Is there any better way to do it ?

question from:https://stackoverflow.com/questions/65903906/getting-entries-from-relationship-and-sub-relationship

Answer 1

Reverse-querying should work for you. Directly query the Lorem model, and check for reverse-relationship existence:

Lorem::whereHas('bars', function($query) {
  $query->whereHas('foos', function($subQuery) {
    $subQuery->where('foos.id', 123);
  })
})->get();

If Lorem can alternatively be directly related to Foo, then you can do an orWhereHas():

Lorem::whereHas('bars', function($query) {
  $query->whereHas('foos', function($subQuery) {
    $subQuery->where('foos.id', 123);
  })
})->orWhereHas('foos', function($query){ 
  $query->where('foos.id', 123);
})->get();

That should return all Lorem models that belong to a Bar that belongs to Foo, or that directly belongs to Foo.