I suggest some remodeling:

// Lists model

public function getSubscribersCountAttribute()
{
	if ( ! array_key_exists('subscribersCount', $this->relations)) $this->load('subscribersCount');

	if (is_null($this->getRelation('subscribersCount')->first()) return 0;

	return $this->getRelation('subscribersCount')->first()->aggregate;
}

public function getUnsubscribersCountAttribute()
{
	if ( ! array_key_exists('unsubscribersCount', $this->relations)) $this->load('unsubscribersCount');

	if (is_null($this->getRelation('unsubscribersCount')->first()) return 0;

	return $this->getRelation('unsubscribersCount')->first()->aggregate;
}

public function subscribersCount() // allows you to eager load
{
  return $this->contacts()  // Relation object, now we work on Contacts query
      ->confirmed()            // scope on Contacts model
      ->subscribed()         // again scope on Contacts model (omit if it's default)
      ->selectRaw('count(*) as aggregate, list_id')
      ->groupBy('pivot_list_id');
}

public function unsubscribersCount()
{
  return $this->contacts() 
      ->confirmed()
      ->unsubscribed()
      ->selectRaw('count(*) as aggregate, list_id')
      ->groupBy('pivot_list_id');
}

public function contacts()
{
   return $this
       ->belongsToMany('\Contacts\Contacts', 'contacts.contacts_lists', 'lists_id', 'contacts_id')
}

// Contacts model

public function scopeConfirmed($query)
{
   $query->where('is_confirmed', true);
}

public function scopeSubscribed($query)
{
   $query->where('is_unsubscribed', false);
}

public function scopeUnsubscribed($query)
{
   $query->where('is_unsubscribed', true);
}

Then you can easily do this:

$lists = Lists::with('subscribersCount', 'unsubscribersCount')->get(); // 3 queries

$lists->first()->subscribersCount; //  returns eg. '25'
$lists->first()->unsubscribersCount; //  returns eg. '5'

Or on a single List, no need to eager load:

$list = Lists::find($listId);

$list->subscribersCount; // '25'  (lazy loads the relation and returns the count)

Hi jarektkaczyk, I really appreciate your help with this issue and effort you have put into giving your answer. But this doesn't solve my problem.

I have remodeled the models to the above example and the query that is generated still requests all of the data from the contacts table.

Selecting all of the contact data would be a huge overkill for the application I'm currently building because some of our clients have huge amounts of contacts available.

Hi jarredholman,

Thank you for the tip and the code. Maybe a sub query is a bit trickier, but still better that selecting all of the data when it's not needed ;)

Did I encounter a bug in Eloquent with my earlier posted attempt? Because Eloquent always selected all of the data from the contacts table. No matter what I tried, I couldn't stop Eloquent from requesting all of the data from that table. I even tried yelling at the code... didn't work either.

With your first attempt you are eager loading the subscribers and unsubscribers relationships, which are collections of contacts. It is selecting everything from the contacts table to load the contact models that you requested to be eager loaded.

I'm not sure why jarektkaczyk's solution didn't work. Laravel relationships are a bit wonky if you try to do anything complicated with them so that might be the reason. Or it could be a version difference, since it looks like it depends on implementation details (like pivot_list_id?).

jarredholman said:

I'm not sure why jarektkaczyk's solution didn't work. Laravel relationships are a bit wonky if you try to do anything complicated with them so that might be the reason. Or it could be a version difference, since it looks like it depends on implementation details (like pivot_list_id?).

Agreed, if optimization is needed, you should use query scope and raw SQL to perform your query. Eloquent has the tendency to multiplicate unecessary queries (and not forcefully with the expected result).