如何通过长尾关键词优化ECShop无限级上下关系描述？

本文共计1066个文字，预计阅读时间需要5分钟。

php连接数据库：ecs_parent.php, 本地服务器, 用户名root, 密码root, 数据库test, 表前缀ecs_用户列表：[1=a, 2=b, 3=c, 4=d, 5=e, 6=f, 7=g, 8=h]初始化// 删除用户3// 添加父子关系：6为父，3为子// 更新用户3的信息

ecs_parent.php

'localhost', 'username' => 'root', 'password' => 'root', 'db'=> 'test', 'prefix' => 'ecs_' ]); $users = [ 1 => 'a', 2 => 'b', 3 => 'c', 4 => 'd', 5 => 'e', 6 => 'f', 7 => 'g', 8 => 'h', ]; init(); //delete_parent(3); //add_parent(6, 3); //update_parent(3, 6); //show parents $parents = $dbi->orderBy('parent_id', 'asc')->orderBy('level', 'asc')->get('parent'); foreach ($parents as $parent) { echo $users[ $parent['parent_id'] ] . ' -> ' . $users[ $parent['user_id'] ] . ', ' . $parent['level'] . '级'.PHP_EOL; } function init(){ $GLOBALS['dbi']->rawQuery('truncate ecs_parent'); //a->b add_parent(1, 2); //b->c add_parent(2, 3); //c->d add_parent(3, 4); //d->e add_parent(4, 5); //a->f add_parent(1, 6); //f->g add_parent(6, 7); //g->h add_parent(7, 8); } /** * 新增上下级关系 * @param integer $parent_id 上级id * @param integer $user_id 下级id */ function add_parent($parent_id, $user_id){ $GLOBALS['dbi']->startTransaction(); try{ //建立跟上级的关系 $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $user_id, 'level' => 1]); //如果上级有上级，把我跟每一个上级建立关系 $grandpas = $GLOBALS['dbi']->where('user_id', $parent_id)->get('parent', null, ['parent_id', 'level']); foreach ($grandpas as $parent){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $user_id, 'level' => $parent['level']+1 ]); } //如果我有下级，把我所有下级跟我的上级建立关系 $children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']); foreach ($children as $child){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $child['user_id'], 'level' => $child['level']+1]); //还要跟我的上级的所有上级建立关系 foreach ($grandpas as $parent){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $child['user_id'], 'level' => $child['level'] + $parent['level'] + 1 ]); } } $GLOBALS['dbi']->commit(); } catch(Exception $e){ $GLOBALS['dbi']->rollback(); } } /** * 更改上级 * @param integer $user_id 用户id * @param integer $parent_id 新上级id，为0时仅去除原有上级 */ function update_parent($user_id, $parent_id = 0){ delete_parent($user_id); if($parent_id > 0){ add_parent($parent_id, $user_id); } } /** * 去除上级 * @param integer $user_id 用户id */ function delete_parent($user_id){ $GLOBALS['dbi']->startTransaction(); try{ //先跟原来的上级们断掉关系 $GLOBALS['dbi']->where('user_id', $user_id)->delete('parent'); //要断绝跟上级们的关系，还得加上我的下级们 $children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']); foreach ($children as $child){ //对每一个下级而言，跟“我”的关系不用变，但是在我之上的更远关系应该删除，无论更远的上级是谁 $GLOBALS['dbi']->where('user_id', $child['user_id'])->where('level', ['>' => $child['level']])->delete('parent'); } $GLOBALS['dbi']->commit(); } catch(Exception $e){ $GLOBALS['dbi']->rollback(); } } lib_relation.php

startTransaction(); try{ //建立跟上级的关系 $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $user_id, 'level' => 1]); //如果上级有上级，把我跟每一个上级建立关系 $grandpas = $GLOBALS['dbi']->where('user_id', $parent_id)->get('parent', null, ['parent_id', 'level']); foreach ($grandpas as $parent){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $user_id, 'level' => $parent['level']+1 ]); } //如果我有下级，把我所有下级跟我的上级建立关系 $children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']); foreach ($children as $child){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent_id, 'user_id' => $child['user_id'], 'level' => $child['level']+1]); //还要跟我的上级的所有上级建立关系 foreach ($grandpas as $parent){ $GLOBALS['dbi']->insert('parent', ['parent_id' => $parent['parent_id'], 'user_id' => $child['user_id'], 'level' => $child['level'] + $parent['level'] + 1 ]); } } $GLOBALS['dbi']->commit(); } catch(Exception $e){ $GLOBALS['dbi']->rollback(); } } /** * 更改上级 * @param integer $user_id 用户id * @param integer $parent_id 新上级id，为0时仅去除原有上级 */ function update_parent($user_id, $parent_id = 0){ delete_parent($user_id); if($parent_id > 0){ add_parent($parent_id, $user_id); } } /** * 去除上级 * @param integer $user_id 用户id */ function delete_parent($user_id){ $GLOBALS['dbi']->startTransaction(); try{ //先跟原来的上级们断掉关系 $GLOBALS['dbi']->where('user_id', $user_id)->delete('parent'); //要断绝跟上级们的关系，还得加上我的下级们 $children = $GLOBALS['dbi']->where('parent_id', $user_id)->get('parent', null, ['user_id', 'level']); foreach ($children as $child){ //对每一个下级而言，跟“我”的关系不用变，但是在我之上的更远关系应该删除，无论更远的上级是谁 $GLOBALS['dbi']->where('user_id', $child['user_id'])->where('level', ['>' => $child['level']])->delete('parent'); } $GLOBALS['dbi']->commit(); } catch(Exception $e){ $GLOBALS['dbi']->rollback(); } }