Eloquent filtering of nested

Question

I have a db modelled like this:

User
+ id
+ email
+ password 

Profile
+ id
+ owner_id (FK user.id)
+ type_id (FK types.id)
+ address_id (FK addresses.id)
+ org_name
+ phone
+ email
+ url
  etc

Postings
+ id
+ profile_id (FK profiles.id)
+ title
+ description

Addresses
+ id
+ street
+ city 
+ province_id (FK provinces.id)
+ country_id (FK countries.id)
  etc

Countries
+ id
+ name

Provinces
+ id
+ name
+ abbreviation
+ country_id

I am trying to use Eloquent ORM to query a set of Postings from this based upon type_id, city, country_id and/or province_id. These fields are on the subordinate object $posting->profile->address, etc.

Am I going to have to dumb-down my db - flatten out the profile by merging the address data into it? Or is there a way to do this in Eloquent without remodelling all my data?

Answer 1

This is how you filter the table based on constraints in related tables:

// we need to pass all the ids to the closure, so use one array for convenience:
$filters = [
    'typeId'     => 1,
    'addressId'  => 5,
    'provinceId' => 10,
    'countryId'  => 15
];

$posts = Posting::whereHas('profile', function ($q) use ($filters) {
  $q->where('type_id', $filters['typeId'])
    ->whereHas('address', function ($q) use ($filters) {
      $q->where('id', $filters['addressId'])

        // both below wheres don't make sense used together, just an example
        ->where('province_id', $filters['provinceId'])
        ->where('country_id', $filters['countryId']);
    });
});

It will set 2 joins and check those where clauses, so you will fetch only those posts, that meet the requirements, but nothing is eager loaded here (with is for eager loading, but it doesn't filter the main model, only those eager loaded).

Answer 2

Assuming you have defined a relationship of address for profile,

$posts = Posting::with([
    'profile' => function($query) use ($type_id)
    {
        $query->whereTypeId($type_id)
    },
    'profile.address' => function($query) use ($country_id, $province_id)
    {
        $query->whereCountryId($country_id)
              ->whereProvinceId($province_id);
    }
])->get();