着墨中文lisp登入_墨风 - SegmentFault 思否

需求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);查看原文