当前位置 博文首页 > fareast_mzh的博客:php elequent ORM, Raw Expressions, 原生sq
raw expression
private function listCustomerIds() {
// SELECT customer_id,name,count(1) as c from contact group by customer_id,name HAVING c>1;
/** @var $db \Illuminate\Database\Connection */
$db = $this->tblCustomer->dbCRM;
/** @var $qb \Illuminate\Database\Query\Builder */
$qb = $db->table(ContactTable::TABLE)
->select($db->raw("customer_id, name, count(1) as c"))
->where('is_deleted', '=', 0)
->groupBy('customer_id','name')
->having('c', '>', 1);
return $qb->get()->all();
}
?
https://laravel.com/docs/8.x/queries
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
?
假设要实现下面的SQL
select * from `log`?
where (`relation_id` = '10951' and `relation_type` = '3')?
?? ?or (`relation_id` in ('21976', '21983') and `relation_type` = '5');
?
QueryBuilder操作方式如下:
<?php
// ...
/** @var $query \Illuminate\Database\Query\Builder */
$query->where(function($query) use ($params) {
if (is_array($param['relation_id'])) {
$query->whereIn('relation_id', $param['relation_id']);
} else {
$query->where('relation_id', '=', $param['relation_id']);
}
$query->where('relation_type', '=', $param['relation_type']);
});
if (isset($params['or'])) {
$cond = $params['or'];
$query->orWhere(function($query) use ($cond) {
$query->whereIn('relation_id', $cond['relation_id'])
->where('relation_type', '=', $cond['relation_type']);
});
}
用闭包实现括号效果
?
update xxxTable set xxxColumn = null where id=?
?
public static function restoreById($id) { return DB::update("UPDATE `".self::getInstance()->getTable()."` SET `".self::getInstance()->getDeletedAtColumn()."`=NULL WHERE id=?", [$id]); }
?
?
?
cs