Recursive relationship with scope
A user has a sponsor:
public function sponsor()
{
return $this->belongsTo(User::class, 'sponsor_id');
}
A user has referrals:
public function referrals()
{
return $this->hasMany(User::class, 'sponsor_id');
}
A user is considered capped when they have 2 or more referrals:
public function activeReferrals()
{
return $this->referrals()->whereActive(true);
}
public function isCapped()
{
return $this->activeReferrals()->count() >= 2;
}
A user can give points. By default, the sponsor will receive them, but if the sponsor is capped, I want the points to go to a sponsor's referral that is NOT capped. If all the referrals are capped, then it does the same thing with the level below (the referral's referrals).
If I go user by user making database calls for each one, it's gonna take a long time. How can I write a scope that makes recursive calls until it finds the first active referral in the tree that's not capped?
This is what I'm trying to do:

Please or to participate in this conversation.