php - laravel递归显示按级别/深度推荐的用户

Question

所以我正在与附属公司合作，在注册和保存推荐用户方面做得很好，现在我很难按级别向这些用户显示推荐。

级别没有保存在数据库中，我认为它在逻辑区域递增？

用户表结构

id |  name  | sponsor_id |
 1 |  John  |    NULL    |
 2 |  Jane  |      1     |
 3 |  Jess  |      1     |
 4 |  Joe   |      2     |

所以我想要的输出应该是这样的:

我是 John，1 级是谁的 sponsor_id 是我的，2 级是谁的 sponsor_id 是我邀请的人的 id。

ID |  Name  |  Level  |
2  |  Jane  |    1    | 
3  |  Jess  |    1    |
4  |  Joe   |    2    |

Jane & Jess 的 sponsor_id 是我的，Joe 的 sponsor_id 是 Jess

用户有赞助商

public function sponsor()
{
    return $this->belongsTo('App\User', 'sponsor_id');
}

用户有推荐

public function referrals()
{
    return $this->hasMany('App\User', 'sponsor_id');
}

Answer 1

如果是 MySQL 8(非常建议使用像您这样的分层数据)，您可以使用递归 CTE 执行此操作:

WITH RECURSIVE CTE_users_levels(id, name, level) AS 
    ( 
          SELECT id, name, 0 as level
          FROM users 
          WHERE sponsor_id IS NULL 
          UNION ALL 
          SELECT u.id, u.name, cte.level+1 
          FROM CTE_users_levels cte 
          JOIN users u ON cte.id=u.sponsor_id
    ) 

-- To output all users' levels:
SELECT * FROM CTE_users_levels ORDER BY level;

现在您拥有包含所有用户的 level 列的虚拟表，您可以查询它

| id  | name | level |
| --- | ---- | ----- |
| 1   | John | 0     |
| 2   | Jane | 1     |
| 3   | Jess | 1     |
| 4   | Joe  | 2     |

DBFiddle

---

进一步...

从您的 CTE 看

CREATE VIEW VIEW_User_Levels AS
(
WITH RECURSIVE CTE_users_levels(id, name, level) AS 
    ( 
          SELECT id, name, 0 as level
          FROM users 
          WHERE sponsor_id IS NULL 
          UNION ALL 
          SELECT u.id, u.name, cte.level+1 
          FROM CTE_users_levels cte 
          JOIN users u ON cte.id=u.sponsor_id
    ) 
SELECT * FROM CTE_users_levels ORDER BY level   
  
 )

现在很容易获得所有用户级别(无需 CTE 声明):

SELECT * FROM VIEW_User_Levels

然后，如果您有很多用户，那么一直重新计算整个树(VIEW 所做的)就有点过分了

然后您可以在新列中修复您的用户级别。

ALTER TABLE users ADD level INT;

并在您的 View 的帮助下为所有用户填充该列:

UPDATE users u, VIEW_User_Levels v
SET  u.level=v.level
WHERE u.id=v.id;

哪个给

SELECT * FROM users

id  name    sponsor_id  level
1   John    null        0
2   Jane    1           1
3   Jess    1           1
4   Joe     2           2

DBFiddle

如果你有很多用户，并且你的目标是大量查询级别列，INDEX IT。

请注意，您还可以只更新一个用户的级别值(例如，如果其 sponsor_id 更改，或者如果用户是新用户)

UPDATE users u, VIEW_User_Levels v
SET  u.level=v.level
WHERE u.id=v.id AND u.name='Jane';