Optimizing Eloquent Relationship Retrieval

Question

I have an interface which displays a list of communities on the platform. Communities have members and in turn members/profiles can befriend one another. On the listing page each community card needs to display the number of members (in the community) and the number of friends (friends of logged in profile) from those members.

Here's an illustration of how a community card looks like

I'm getting the communities with the members first:

$communities = $loggedInProfile->communities->load('members')->take(15);

And then iterating over the communities and then the members to find out which ones are friends with the logged in user.

   foreach ($communities as $key => $community) {
        $friends = [];
        foreach ($community->members as $member) {
            if ($loggedInProfile->isFriendWith($member)) {
                array_push($friends, $member);
            }
        }
        $community->members_who_are_friends = $friends;
    }

My issue is that this is very taxing in terms of the number of queries when the associations get large. Is there a better way of retrieving these relationships without having to use nested for loops? I'm also indexing all data with Elasticsearch. Would a retrieval of this sort be better with Elasticsearch? Also would this be a good use case for hasThrough?

Update

The members relationship:

public function members()
{
    return $this->belongsToMany('App\Profile', 'community_members', 'community_id', 'profile_id')->withTimestamps();
}

The isFriendWith relationship:

public function isFriendWith(Model $recipient)
{
    return $this->findFriendship($recipient)->where('status', Status::ACCEPTED)->exists();
}

The check is done on a table called friendships. The status column (which can be either 0 or 1) is checked to see if friends or not.

The findFriendship check:

private function findFriendship(Model $recipient)
{
    return Friendship::betweenModels($this, $recipient);
}

Database structure:

-Profiles migration

Schema::create('profiles', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
});

-Communities migration (the foreign key is the owner of the community)

Schema::create('communities', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('profile_id');
    $table->foreign('profile_id')->references('id')->on('profiles');
    $table->string('slug')->unique();
});

-Community_members migration

Schema::create('community_members', function (Blueprint $table) {
    $table->primary(['profile_id', 'community_id']);
    $table->unsignedInteger('profile_id');
    $table->foreign('profile_id')->references('id')->on('profiles');
    $table->unsignedInteger('community_id');
    $table->foreign('community_id')->references('id')->on('communities');
    $table->timestamps();
});

-Friendships migration

Schema::create('friendships'), function (Blueprint $table) {
    $table->increments('id');
    $table->morphs('sender');
    $table->morphs('recipient');
    $table->tinyInteger('status')->default(0);
    $table->timestamps();
});

can you show me the function getMutualFriends ? what field of $member do you use there ? — Demonyowh, Apr 03 '19 at 11:17
Sorry about that, was using the wrong check for friendship. Updated the question. Member is a member of the community I'm iterating through. I'm iterating through the members() relationships on community. — Frisbetarian-Support Palestine, Apr 03 '19 at 11:19
show me the `function isFriendWith($member)`, or just tell me w/c field of $member do you use to know if it mutual — Demonyowh, Apr 03 '19 at 11:22
member is an instance of User right ? same as the loggedUser .. — Demonyowh, Apr 03 '19 at 11:26
Member is an instance of Profile which belongs to a User. So yeah same as loggedInUser since I'm getting `auth()->user()->profile` — Frisbetarian-Support Palestine, Apr 03 '19 at 11:26
You should provide some database structure context, so we can see how all this data is stored, because this seems like something that should be done with some custom queries. Nested loops to process an indeterminate number of entries and relations using individual queries is a sure way to slow things down. — Bogdan, Apr 03 '19 at 11:27
I'm assuming the `status` column from the `friendships` indicates whether the friendship invitation is accepted or not, right? Also, do you need the actual list of friends or just the count? — Bogdan, Apr 03 '19 at 11:52
@Bogdan Yes, that is correct. The `status` column on `friendships` determines whether friendship request has been accepted or not. — Frisbetarian-Support Palestine, Apr 03 '19 at 11:53
@Bogdan I need the collection because I may need to display the names of the friends when an interacting client hovers over the count for instance. I could probably do an ajax request when this event happens. Would you recommend that? — Frisbetarian-Support Palestine, Apr 03 '19 at 11:54
This definitely highlights the benefits of graph databases though. I built a similar project a while ago with Neo4j and I didn't even have to think about these considerations. — Frisbetarian-Support Palestine, Apr 03 '19 at 12:31

score 0 · Answer 1 · answered Apr 03 '19 at 12:13

In your line:

$communities = $loggedInProfile->communities->load('members')->take(15);

load() is used to perform Lazy Eager loading, i.e. you load the members after the communities have been retrieved, resulting in a different query for every community. You could extract the whole data with a single query using with(). Also, take(15) is performed on the resulting collection and not on the query. Try this:

$communities = $loggedInProfile->communities()->with('members')->take(15)->get();

I tried both approaches and it doesn't seem to affect the number of queries being run :/. — Frisbetarian-Support Palestine, Apr 03 '19 at 12:20

Optimizing Eloquent Relationship Retrieval

1 Answers1