- 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);