Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 1 year ago.
0

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)
Last updated 1 year ago.
0

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.

Last updated 1 year ago.
0

jarektkaczyk, thats a pretty neat abuse of eager loading and relationships :)

Personally I would just use subselects to get the counts, its a bit trickier because eloquent doesn't give you any help though.

Lists::where('user_id', '=', $activeUserId)
    ->select('*')
    ->addSelect(DB::raw('
        (select count(*) from contacts_lists inner join contacts on (contacts.id = contact_id)
        where list_id = lists.id and is_confirmed = true and is_unsubscribed = false)
        AS subscribed_count'))
    ->addSelect(DB::raw('
        (select count(*) from contacts_lists inner join contacts on (contacts.id = contact_id)
        where list_id = lists.id and is_confirmed = true and is_unsubscribed = true)
        AS unsubscribed_count'))
    ->get();
Last updated 1 year ago.
0

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.

Last updated 1 year ago.
0

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?).

Last updated 1 year ago.
0

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).

Last updated 1 year ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

mikevrind mikevrind Joined 1 Feb 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.