返回首页 - Notes - 2018

多级代理的存储和查询


表结构

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `uname` varchar(50) NOT NULL COMMENT '用户名',
  `top_uid` int(11) DEFAULT NULL COMMENT '上级用户ID',
  PRIMARY KEY (`uid`),
  KEY `uid` (`uid`),
  KEY `top_uid` (`top_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理用户表';

查询语句

SELECT

CONCAT(
IFNULL(GROUP_CONCAT(t1.uid), ''), ',',
IFNULL(GROUP_CONCAT(t2.uid), ''), ',',
IFNULL(GROUP_CONCAT(t3.uid), ''), ',',
IFNULL(GROUP_CONCAT(t4.uid), '')
)

FROM user t0

LEFT JOIN user t1 ON t1.top_uid = t0.uid
LEFT JOIN user t2 ON t2.top_uid = t1.uid
LEFT JOIN user t3 ON t3.top_uid = t2.uid
LEFT JOIN user t4 ON t4.top_uid = t3.uid

WHERE t0.uid = 1;

原理

需要查几级代理就 JOIN 几次,SELECT 中使用 GROUP_CONCAT 将同列的查询结果组合到一起,最后再用 CONCAT 将多列整合后的结果连接起来作为最终结果返回

但这样返回的结果中有可能会存在相同的 uid,且尾部有可能会存在多余的逗号,这就需要脚本干预做后期处理了


将查询做成 PHP 函数

<?php

/*
 * 获取指定代理的全部下级代理ID,包括自身
 *
 * @param $uid   代理ID
 * @param $level 子代理层级
 *
 * @return 下级代理ID数组
 */
function get_agent_downline_uids($uid, $level = 1)
{
    $select = '';
    $join   = '';

    for ($i = 1; $i <= $level; $i++) {
        $top_table = $i - 1;

        $select .= "IFNULL(GROUP_CONCAT(t{$i}.uid), ''), ',', ";
        $join .= "LEFT JOIN user t{$i} ON t{$i}.top_uid = t{$top_table}.uid ";
    }

    $select = 'CONCAT('.rtrim($select, ", ',', ").')';

    $sql = "SELECT {$select} FROM user t0 {$join} WHERE t0.uid = {$uid};";

    // TODO 执行数据库查询
    $data = '';

    $ret      = [$uid];
    $data_arr = explode(',', $data);

    foreach ($data_arr as $item) {
        $item = intval($item);
        if (!empty($item) && !in_array($item, $ret)) {
            $ret[] = $item;
        }
    }

    // TODO 以 key = uid & level 为键缓存查到的数据,直到有新的子代理加入再刷新缓存

    return $ret;
}

date:2018-11-30