Laravel 5 How to retrieve related table data using chaining

Question

I've got DB tables of Region (list of cities), Rental (list of rental information), and Rental_Location (list of rental addresses + lat/long).

The basic relationships are:

Regions haveMany Rentals (with FK's region_id and rental_location_id in Rentals table)
Locations haveMany Rentals, or the reverse logic Rental belongsTo a Location

What I'd like to do is get the location information of all the rentals in a region from a region_id (from the rentals table), as well as include the location spatial data (rental_locations table):

$region = Region::find(1);
$rentals = $region->rentals; // doesn't contain location information yet
$rentalDataWithSpatialLocationData = $region->rentals->location; // what I liked to do

But the last line using the belongsTo relationship won't work by adding the spatial data from rental_locations into the original rentals collection.

Right now the $rentals collection contains all the rentals for a region, but no spatial data to mark the location on a map, but other than using a join in QueryBuilder something like this below and not using the ORM hasMany properties in my models:

$spatials = DB::table('rentals')
              ->join('regions', 'rentals.region_id', '=', 'regions.id')
              ->join('rental_locations', 'rentals.rental_location_id', '=', 'rental_locations.id')
              ->where('rentals.region_id', '=', $region_id)
              ->select('*')
              ->get();

Basically, I can't figure out how to get a collection of all the rentals with their location information, is this possible using the ORM? Seems like this might get expensive compared to a join, so I'm assuming right now you don't use the ORM to the exclusion of the QueryBuilder, but more to supplement simple and quick relationships queries only?

If you have in your RegionModel a function rentals() which returns hasMany relationship, it should work perfectly. Is result of $region->rentals - null or empty collection? — Danny
Hi @Danny, sorry I must have explained this wrong, my bad. The $region->rentals returns a collection of the all the rentals, but I need the spatial data from rental_locations as well. — mtpultz
Hi @Danny, each region has many rentals, and each location also has many rentals. I typoed my original question. So I can $location->rentals and $region->rentals no problem, but I can't figure out how to add the rentals associated location something like $region->rentals->owner, but where the resulting collection includes all the rental data as well as the rental location data. — mtpultz
As simple as any other relationships :) In RentalModel you should add a relationship, pointing that Rental hasMany RentalLocations. Then you can access them by $rental->rental_location. Note that $regeion->rentals is a collection, so you can't just do this - $region->rentals->location you should access locations for each rental separately — Danny

patricus patricus · Accepted Answer · 2015-07-15T14:16:47

There are a couple things going on here that you should know about.

First, for your original issue, your code won't work because you're trying to access the relationship attribute on a Collection of rental models, and not an individual model. If you loop through the Collection, you would be able to access the location relationship on each individual item just fine:

$region = Region::find(1);
$rentals = $region->rentals; // Collection of rentals for the region
foreach($rentals as $rental) {
    print_r($rental->location); // location info for the individual rental
}

Now, one thing to note about the code above is that you will be running into what is known as the N+1 problem. By default, Laravel lazy loads the relationship objects, so the queries to populate the relationship data are not run until the relationship attributes are needed. Ignoring the region for right now, in the code above, Laravel will run one query to get all the rentals, and then in the foreach loop, it will run a new query for each loop iteration (N queries) to get the location information for each rental; hence N+1 queries.

To alleviate this problem, you can eager load the relationships. By eager loading, Laravel will run one query to get the rentals, and then one query to get all the location information for all the rentals, for a total of two queries (instead of N+1). Throw the region information back in there and it is actually three queries.

To eager load the relationships, you use the with() method on the query:

// eager load the rentals, and the nested location on the rentals
// 1 query for region, 1 for all rentals on region, and 1 for all locations on rentals
$region = Region::with('rentals', 'rentals.location')->find(1);

// this is now accessing already loaded rentals; no lazy loading needed
$rentals = $region->rentals;

foreach($rentals as $rental) {
    // this is now accessing already loaded locations; no lazy loading needed
    print_r($rental->location);
}

Another difference this makes, and what may have made you think you didn't have access to the location information, is that lazy loaded relationships won't show up until you access them, whereas eager loaded relationships are immediately available. When debugging by printing the objects to the screen, this could make you think you're missing data when you're really not. What I mean by that is this:

/**
 * lazy load relationships
 * This will only show the information for the region object. No
 * rental or location information will be shown as it has not been
 * loaded yet.
 */
$region = Region::find(1);
print_r($region);

/**
 * eager load relationships
 * This will show the information for the region object, as well as
 * all of the related rental objects and their related location
 * information.
 */
$region = Region::with('rentals', 'rentals.location')->find(1);
print_r($region);

Laravel 5 How to retrieve related table data using chaining

1 Answers