CODEWITHSUNDEEP
phplaraveleloquenteager-loading
Thomas Praxl
Thomas Praxl

Reputation: 777

Load laravel eloquent model withCount of related model

Given I have two eloquent models: Booking and Customer.

When I list all Bookings along with the respective Customer, I also want to show the amount of Bookings the respective customer has in total (count of this Booking + all other bookings).

Example output:

In order to avoid the n+1 problem (one additional query per booking while showing this), I'd like to eager load the bookingsCount for the Customer.

The relations are:

Booking: public function customer() { return $this->belongsTo(Customer::class) }

Customer: public function bookings() { return $this->hasMany(Booking::class) }

Example for querying the Bookings with eager loading

Working, but without eager loading of the bookingsCount:

Booking::whereNotCancelled()->with('customer')->get();

Not working:

Booking::whereNotCancelled()->with('customer')->withCount('customer.bookings')->get();

I learned, that you cannot use withCount on fields of related models, but you can create a hasManyThrough relation and call withCount on that relation, e.g. Booking::whereNotCancelled()->withCount('customerBookings'); (see accepted answer here).

However: This doesn't work. I guess, it's because a Booking belongsTo a Customer and a Customer hasMany Bookings.

Here's the hasManyThrough relation of class Booking

public function customerBookings()
{
    // return the bookings of this booking's customer
    return $this->hasManyThrough(Booking::class, Customer::class);
}

Here's the failing test for hasManyThrough

/**
 * @test
 */
public function it_has_a_relationship_to_the_customers_bookings()
{
    // Given we have a booking
    $booking = factory(Booking::class)->create();
    // And this booking's customer has other bookings
    $other = factory(Booking::class,2)->create(['customer_id' => $booking->customer->id]);
    // Then we expect the booking to query all bookings of the customer
    $this->assertEquals(3, Booking::find($booking->id)->customerBookings()->count());
}

Reported error

no such column: customers.booking_id (SQL: select count(*) as aggregate from "bookings" inner join "customers" on "customers"."id" = "bookings"."customer_id" where "customers"."booking_id" = efe51792-2e9a-4ec0-ae9b-a52f33167b66)

No surprise. There is no such column customer.booking_id.

The Question

Is the intended behavior even possible in this case? If so, how would I eager load the booking's customer's total count of bookings?

Upvotes: 3

Views: 3144

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Try this:

public function customer() {
    return $this->belongsTo(Customer::class)->withCount('bookings');
}

Booking::whereNotCancelled()->with('customer')->get();

Upvotes: 9

Related Questions