laravel分表查询

偏执的太偏执、 2023-06-27 12:59 13阅读 0赞

honey\_log表，这个是重点，因为它是分表的，现在我们要封装一个union表的方法，让这个model自动把涉及的分表作为一张表赋予model查询

<?php
    namespace App\Models;
    
    use Illuminate\Database\Eloquent\Model;
    use DB;
    
    class HoneyLog extends Model
    { 
        /*传入查询的开始日期和结束日期用于计算跨越的表和达到约束表数据的目的。 外部可以调整查询的列，还可以添加where条件*/
        public function setUnionAllTable($startTime = LARAVEL_START, $endTime = LARAVEL_START, $attributes = ['*'], $wheres = [])
        { 
            //约束条件
            $whereConditions = [];
            $wheres = array_merge([['time', '>=', $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写法。  
好。我们来看看控制器如何查询

<?php
    namespace App\Http\Api\Controllers;
    
    
    use Illuminate\Http\Request;
    use DB;
    use Cache;
    use Carbon\Carbon;
    use App\Models\HoneyLog;
    
    
    class AAHoneyLogController extends Controller
    { 
        public function index(Request $request)
        { 
            // 主播ID
            $beauty_uid = $request->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);
        }
    
    }