Complex relationship that takes (too) long

Hi all,

My question is basically: how can I do the following better, as in quicker:

I have 3 tables with a manyToMany relationship: users, memberships and membership_user (this one's the pivot). A user can have multiple memberships (and vv) and the memberships aren't parallel but sequenced.

A simple example: Tom joined the association and became a "new member" on 01-01-2016. Now after one year he becomes a "regular member", on 01-01-2017. After many years he will become a "senior member", say on 01-01-2030.

Our administrators can put in memberships both in the future and in the past, so they can pre-program Tom to become a senior member, 5 years from now.

To determine the user's current membership the application does the following:

/**
     * Gets the current membership from the user
     *
     * Furthermore, we must account for the possibility of having an user with all memberships in the future
     *
     * @return object membership object
     */
    public function getCurrentMembership()
    {
        $membership = $this->memberships->where('effective_date', '<=', date('Y-m-d'))->limit(1);

        //if no records are found this user holds only future memberships, make a new query
        if (! $membership->exists()) {
            $membership = $this->memberships()->limit(1);
        }

        return $membership->first();
    }

This works fine.

Now the problem is that we have about 600~ users and when I want to retrieve, say all senior members I actually have to retrieve all members and then check, member by member, whether they currently hold this particular membership. That's when the application has to do 600 + 1 query's which puts about a second on top of the regular loading time.

I've tried eager loading the pivot table but I can't get that to work and I don't think it's actually meant to be a solution for this challenge.

Now when I started programming some 10 years ago I did actually learn a bit of SQL but I can't figur out how to join this together. Actually I'm not even sure it's possible to join this.

Simplified DB structure:

Users

id | name

Memberships

id | name

Membership_User

id | membership_id | user_id | effective_date

Any idea's on either: how to do fix this DB-wise or perhaps a (totally) different way to implement the desired functionality?

Cheers!

Bram