laravel较优雅的分表关联查询（性能较好，SQL的数量=表的
数量，涵盖了较多larav。。。
终于被产品的各种刁钻不合常理的需求磨炼出⽤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经验值
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
protected $table = 'user';
/**这个是laravel的访问器⽅法，实际user表是没有等级这个字段的
但是可以在这⾥定义出等级是怎么来的，给user添加了level这个属性**/
public function getLevelAttribute()
{
//⽤户的经验值根据配置⽂件的等级区间计算⽤户等级，section是⾃⼰封装函数
return section($this->exp, config('user.level.num'));
}
}
2.⽤户资料表
主要字段uid主键，header_name头像⽂件名，header_lock头像是否被锁（0,1）
class UserExt extends Model
{
protected $table = 'user_ext';
protected $primaryKey = 'uid';
//添加头像属性
public function getHeaderUrlAttribute()
{
//头像放在了cdn上，先判断⼀下头像是空的话给默认，或者头像被管理员锁了的话也给默认        if ($this->header_lock == 1 || $this->header_name == '') {
$headerUrl = 'www.cdn/' . 'default_header_user.png';
} else {
$headerUrl = 'www.cdn/' . $this->header_name;
}
return $headerUrl;
}
}
3.SVIP表
主要字段uid主键，expire过期时间
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Svip extends Model
{
protected $table = 'svip';
protected $primaryKey = 'uid';
//这⾥⽤到了laravel的查询范围，可以快速调⽤判断是否VIP，不⽤每次都写⼀次where
public function scopeValidVip($query)
{
return $query->where('expire', '>', LARAVEL_START);
}
}
4.亲民度qinmi表
主要字段uid,beauty_uid(主播主键),qinmi_num亲密度值
class Qinmi extends Model
{
protected $table = 'qinmi';
public function getLevelAttribute()
{
//转换亲密度等级
return section($this->qinmi_num, config('qinmi.qinmi.num'));
}
}
5.好了，重点来了。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 wh        //核⼼是看你输⼊的开始⽇期和结束⽇期和约束条件，组装成⼀个union all的⼦查询然后作为table赋予model
return $this->setTable(DB::raw('(' . implode(' union all ', $tables) . ') as cdb_honey_log'));
}
//关联⽤户资料表，要拿头像
public function userExt()
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定义⾮静态⽅法但是可以通过静态调⽤的话，请告诉我，            $honeyLog = new HoneyLog;
// 查询该主播ID30天有亲密值的⽤户group by 排序⽤分页paginate
$lists = $honeyLog->setUnionAllTable($startTime, $endTime, ['uid', 'honey_num'], [['beauty_uid', '=',
$beauty_uid]])sql中union多表合并
->select(DB::raw('uid, sum(honey_num) as honey_num'))->groupBy('uid')->orderBy('honey_num', 'desc')->paginate($pageSize);
// 很多⼈可能会问为什么不⽤with()渴求式加载，因为⽤了with的话，model会默认去构造⼀次实例，导致table属性丢失，你们试试就知道了，所以下⾯我们终于            // 懒惰渴求式加载头像，vip，亲密值，昵称
// 懒惰渴求式加载头像，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);
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。