ThinkPHP6查询构造器高级用法:构建复杂SQL查询的优雅方案
ThinkPHP6的查询构造器提供了流畅的接口来构建数据库查询,避免了SQL注入风险同时保持了代码可读性。本文将深入探讨如何利用查询构造器处理复杂业务场景下的数据查询需求。
1. 多表联合查询与子查询
实现一个包含子查询的多表关联案例:
$users = Db::table(‘user’)
->field(‘user.id,user.name,order_count.count’)
->join([
‘order_count’ => Db::raw(‘(SELECT user_id, COUNT(*) as count FROM orders
WHERE create_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY user_id)’)
], ‘user.id = order_count.user_id’, ‘LEFT’)
->where(‘user.status’, 1)
->select();
2. 条件构造器高级用法
动态构建复杂查询条件:
$map = [];
if (!empty($params[‘name’])) {
$map[] = [‘name’, ‘like’, ‘%’.$params[‘name’].’%’];
}
if (!empty($params[‘min_price’])) {
$map[] = [‘price’, ‘>=’, $params[‘min_price’]];
}
$query = Db::name(‘products’)
->when(!empty($params[‘category_id’]), function($query) use ($params) {
$query->where(‘category_id’, $params[‘category_id’]);
})
->where($map)
->order(‘sales’, ‘desc’)
->paginate(10);
3. 查询结果处理技巧
使用闭包处理查询结果集:
->where(‘status’, 1)
->select()
->each(function($item) {
// 计算实际支付金额
$item[‘actual_price’] = $item[‘total_price’] – $item[‘discount’];
return $item;
});
4. 实际案例:电商报表查询
实现一个多维度销售报表查询:
$report = Db::name(‘orders’)
->field([
‘DATE(create_time) as date’,
‘COUNT(*) as order_count’,
‘SUM(total_amount) as total_sales’,
‘AVG(total_amount) as avg_order_value’
])
->whereTime(‘create_time’, ‘between’, [$startDate, $endDate])
->group(‘DATE(create_time)’)
->having(‘total_sales’, ‘>’, 1000)
->order(‘date’, ‘asc’)
->select();
// 转换为更友好的格式
$result = array_map(function($item) {
return [
‘date’ => $item[‘date’],
‘order_count’ => $item[‘order_count’],
‘total_sales’ => number_format($item[‘total_sales’], 2),
‘avg_order_value’ => number_format($item[‘avg_order_value’], 2)
];
}, $report);
ThinkPHP6的查询构造器通过链式调用和丰富的查询方法,能够优雅地构建各种复杂SQL查询,同时保证了代码的安全性和可维护性。