资讯专栏INFORMATION COLUMN

laravel较优雅的分表关联查询(性能较好,SQL的数量=表的数量,涵盖了较多laravel手册推

mylxsw / 3526人阅读

摘要:最优就只能是查询表的数量才算是比较合理,完全有足够的能力写出优雅的代码很多人可能会想到的原生查询了么。但是有强大的关联,访问器修改,查询范围等等这些功能让你的代码非常简洁。相信熟悉的人已经知道怎么查询了,可以达到最优化的,和最优雅的写法。

终于被产品的各种刁钻不合常理的需求磨炼出用laravel写出较为优雅的代码,在这里给大家分享一下。

先简单介绍一下基本环境,我们是做一款直播APP的,人很多,所以每个接口都必须尽量优化(主要是SQL的查询)。

有一天,产品跟我们说,那个针对主播的送礼牌行榜能否显示30天内的用户送礼数倒序排列,显示用户是否VIP,用户对主播的亲密度,还有用户的等级。

30天内的数据。也就是说之前那张一直累计数值的排行表不能使用了,而且这个30天是个动态的,也就是说这个数据必须只能利用送礼流水group by出来。我们的送礼流水表是1个月1张表的

介绍一下基本表的情况
用户表user
用户资料表user_ext(你大爷的头像竟然放这张表,谁搞的站出来,看我不弄死你)
礼物表honey_log_201708(XXXX分表日期)
超级VIP表svip
亲密度表qinmi
(这几张表的关联是无法避免的,加上分页count查询。SQL最优就只能是查询表的数量+1才算是比较合理,laravel完全有足够的能力写出优雅的代码)

很多人可能会想到laravel的DB原生查询了么。但是Eloquent有强大的关联,访问器修改,查询范围等等这些功能让你的代码非常简洁。

我们先写model
1.用户表user
关键字段是id用户ID,nickanem昵称,exp经验值

exp, config("user.level.num"));
    }
}

2.用户资料表
主要字段uid主键,header_name头像文件名,header_lock头像是否被锁(0,1)

header_lock == 1 || $this->header_name == "") {
            $headerUrl = "http://www.cdn.com/" . "default_header_user.png";
        } else {
            $headerUrl = "http://www.cdn.com/" . $this->header_name;
        }
        return $headerUrl;
    }
}

3.SVIP表
主要字段uid主键,expire过期时间

where("expire", ">", LARAVEL_START);
    }
}

4.亲民度qinmi表
主要字段uid,beauty_uid(主播主键),qinmi_num亲密度值

qinmi_num, config("qinmi.qinmi.num"));
    }
}

5.好了,重点来了。honey_log表,这个是重点,因为它是分表的,现在我们要封装一个union表的方法,让这个model自动把涉及的分表作为一张表赋予model查询

=", $startTime], ["time", "<", $endTime]], $wheres);
        //时间戳转日期
        $startDate = date("Y-m", $startTime);
        $endDate = date("Y-m", $endTime);
        //涉及的表数组
        $tables = [];
        //循环where数组,格式是[["字段","表达式","值"," and|or "],["字段","表达式","值"," and|or "]]
        //例子[["beauty_uid", "=", "2011654", "and"]]
        foreach ($wheres as $val) {
            //组装每个where条件
            $val[2] = $val[2] ? $val[2] : """";
            if (isset($val[3])) {
                $whereConditions[] = " {$val[3]} {$val[0]} {$val[1]} {$val[2]}";
            } else {
                $whereConditions[] = " and {$val[0]} {$val[1]} {$val[2]}";
            }
        }
        //循环开始日期和结束日期计算跨越的表
        for ($i = $startDate; $i <= $endDate; $i = date("Y-m", strtotime($i . "+1month"))) {
            $tables[] = "select " . implode(",", $attributes) . " from cdb_honey_log_" . date("Yn", strtotime($i)) . " where 1" . implode("", $whereConditions);
        }
        //会得到每一个表的子查询,因为都有约束条件,所以每一个子查询得结果集都不会很多
        //用setTable的方法把这个子查询union all 后 as一个表名作为model的table属性
        //sql大概会是:(select xxx,xxx from honey_log_20177 where time >= 开始日期 and time < 结束日期 and xxx union all select xxx,xxx from honey_log_20178 where time >= 开始日期 and time < 结束日期 and xxx) as cdb_honey_log
        //核心是看你输入的开始日期和结束日期和约束条件,组装成一个union all的子查询然后作为table赋予model
        return $this->setTable(DB::raw("(" . implode(" union all ", $tables) . ") as cdb_honey_log"));
    }

    //关联用户资料表,要拿头像
    public function userExt()
    {
        return $this->belongsTo(UserExt::class, "uid");
    }
    //关联用户表,要拿昵称
    public function user()
    {
        return $this->belongsTo(User::class, "uid");
    }
    //关联SVIP表,要判断是否VIP
    public function svip()
    {
        return $this->belongsTo(Svip::class, "uid");
    }
    //关联用户对于主播的亲民值
    public function qinmi()
    {
        return $this->hasMany(Qinmi::class, "uid", "uid");
    }
    //转化送礼等级,按送礼金额转化
    public function getHoneyLevelAttribute()
    {
        return section($this->honey_num, config("beauty.honey.num"));
    }
}

以上准备工作都有了。相信熟悉laravel的人已经知道怎么查询了,可以达到最优化的SQL,和最优雅的laravel写法。
好。我们来看看控制器如何查询

input("beauty_uid");
        // 每页显示数量
        $pageSize = $request->input("pagesize", 10);
        // 当前页
        $page = $request->input("page");
        // 缓存数据,按查询的主播,页数作为key分页
        $data = Cache::remember("user_for_beauty_rank_{$beauty_uid}_{$pageSize}_{$page}", 2, function () use ($beauty_uid, $pageSize, $page) {
            // 计算30天前
            $startTime = Carbon::today()->subDays(30)->getTimestamp();
            // 计算结束日期
            $endTime = Carbon::tomorrow()->getTimestamp();
            // 实例化honeyLog模型,因为自定义的setUnionAllTable方法是非静态方法,如果谁知道如何在model定义非静态方法但是可以通过静态调用的话,请告诉我,因为不想改底层,laravel是用了魔法静态方法实例化调用的,所以我们才可以使用model::select()->where()->get()这样的链式调用,但是在model自己定义的实体方法好像并没有继承到这种调用
            $honeyLog = new HoneyLog;
            // 查询该主播ID30天有亲密值的用户group by 排序 用分页paginate
            $lists = $honeyLog->setUnionAllTable($startTime, $endTime, ["uid", "honey_num"], [["beauty_uid", "=", $beauty_uid]])
                ->select(DB::raw("uid, sum(honey_num) as honey_num"))->groupBy("uid")->orderBy("honey_num", "desc")->paginate($pageSize);
            // 很多人可能会问为什么不用with()渴求式加载,因为用了with的话,model会默认去构造一次实例,导致table属性丢失,你们试试就知道了,所以下面我们终于理解到laravel为什么会还有个懒惰渴求式加载了,简直绝配
            
            // 懒惰渴求式加载头像,vip,亲密值,昵称
            // 好好理解下面的关联约束
            $lists->load([
                "userExt" => function ($query) {
                    $query->select("uid", "header_name", "header_lock");
                },
                "user" => function ($query) {
                    $query->select("bid", "nickname", "exp");
                },
                "svip" => function ($query) {
                    // 这个validVip是模型定义的范围约束方法,相当于where("expire", ">", LARAVEL_START)
                    $query->select("uid")->validVip();
                },
                "qinmi" => function ($query) use ($beauty_uid) {
                    // 这里需要传入主播ID,只查找用户对于这个主播的亲密值
                    $query->select("uid", "qinmi_num")->where("beauty_uid", $beauty_uid);
                }
            ]);

            // 现在需要的数据都已经全部查出来了,由于我做的是API,现在要组装前端需要的格式return出去就可以了,
            // 如果是自己做的web网页,就直接丢给视图遍历就可以了

            $result = [];
            foreach ($lists as $key => $value) {
                $result[] = [
                    // 用户id
                    "uid" => $value->uid,
                    // 送礼数量
                    "honey_num" => $value->honey_num,
                    // 头像
                    "header" => $value->userExt->header_url,
                    // 是否vip
                    "svip" => $value->svip ? 1 : 0,
                    // 送礼等级
                    "honey_level" => $value->honeyLevel,
                    // 亲密等级
                    "qinmi_level" => $value->qinmi->isEmpty() ? 0 : $value->qinmi[0]->level,
                    // 昵称
                    "nickname" => $value->user->nickname,
                    // 用户等级
                    "level" => $value->user->level,
                ];
            }

            // 这是前端要求的格式,要这样组装没有什么特别要说的,只是前端习惯这样的结构
            $data = [
                "page" => [
                    "last_page" => $lists->lastPage(),
                    "current_page" => $lists->currentPage(),
                    "list" => $result,
                ],
            ];

            return $data;
        });
        
        return response()->json($data);
    }

}

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/25615.html

相关文章

  • 首发,laravel优雅分表关联、分页查询手册方法熟练运用。

    摘要:前几天写了一遍关于分表关联的查询,但是我个人觉得还不算完美,于是今天重新看了一下模型的底层代码,终于写出我暂时觉得最满意的代码风格,简洁优雅是核心。关联查询用了渴求式加载,就能有效减少的条数,保证数据库的性能。 前几天写了一遍关于laravel分表关联的查询,但是我个人觉得还不算完美,于是今天重新看了一下laravel模型的底层代码,终于写出我暂时觉得最满意的代码-laravel风格,...

    fuchenxuan 评论0 收藏0
  • 美团点评携手 PingCAP 开启新一代数据库深度实践之旅

    摘要:一背景和现状在美团,基于构建的传统关系型数据库服务已经难于支撑公司业务的爆发式增长,促使我们去探索更合理的数据存储方案和实践新的运维方式。随着近一两年来分布式数据库大放异彩,美团团队联合架构存储团队,于年初启动了分布式数据库项目。 一、背景和现状 在美团,基于 MySQL 构建的传统关系型数据库服务已经难于支撑公司业务的爆发式增长,促使我们去探索更合理的数据存储方案和实践新的运维方式。...

    gclove 评论0 收藏0
  • [灵魂拷问]MySQL面试高频100问(工程师方向)

    摘要:黑客技术点击右侧关注,了解黑客的世界开发进阶点击右侧关注,掌握进阶之路开发点击右侧关注,探讨技术话题作者丨呼延十排版丨团长前言本文主要受众为开发人员所以不涉及到的服务部署等操作且内容较多大家准备好耐心和瓜子矿泉水前一阵系统的学习了一下也有 ...

    gyl_coder 评论0 收藏0

发表评论

0条评论

mylxsw

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<