php elequent ORM, Raw Expressions, 原生sql group by, count, or, orWhere查询, update null

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