需求1:
SELECT COUNT(*) FROM
(SELECT * FROM abc GROUP BY col1) AS a;
执行代码
$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance
$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
->count();
需求2: 执行如下代码
SELECT
`p`.`id`,
`p`.`name`,
`p`.`img`,
`p`.`safe_name`,
`p`.`sku`,
`p`.`productstatusid`
FROM `products` p
WHERE `p`.`id` IN (
SELECT
`product_id`
FROM `product_category`
WHERE `category_id` IN ('223', '15')
)
AND `p`.`active`=1
实现:
Products::whereIn('id', function($query){
$query->select('paper_type_id')
->from(with(new ProductCategory)->getTable())
->whereIn('category_id', ['223', '15'])
->where('active', 1);
})->get();
综合运用:
$query = \DB::table('message')
->where(function ($query) use ($userId) {
$query->where([
['receiver_id', '=', "$userId"],
['type', '=', Messages::PRIVATE_MESSAGE],
]);
});
if (!empty($groupIds)) {
$query->orWhere(function ($queryq) use ($groupIds) {
$queryq
->where([
['type', '=', Messages::GROUP_MESSAGE],
['message.created_at', '>', \Auth::user()->created_at]
])
->whereIn('receiver_id', $groupIds);
});
}
/* 获取广播 */
$query->orWhere(function ($query) {
$query
->where([
['type', '=', Messages::BROADCAST_MESSAGE],
['dead_line', '>', Carbon::createFromTimestamp(time())],
['message.created_at', '>', \Auth::user()->created_at]
]);
});
$query->leftJoin('message_log', function ($join) use ($userId) {
$join->on('message.id', '=', 'message_log.message_id')
->where('message_log.user_id', '=', $userId);
})
->leftJoin('subscribe', function ($join) use ($userId) {
$join->on('subscribe.pcode_id', '=', 'message.receiver_id')
->where('subscribe.user_id', '=', $userId);
})
->select([
'message_log.id as message_log_id',
'message.id as message_id',
'message.content',
'message.type',
'message.sender_id',
'message.updated_at',
'message.created_at',
'message.dead_line',
\DB::raw('IF (message.`created_at` < subscribe.`created_at` , 1, 0) AS is_expired')
]);
$resQuery = \DB::table(\DB::raw("({$query->toSql()}) as sub"))
->mergeBindings($query)
->where('is_expired', '=', 0);
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。