lumen 如何执行 原生sql 语句

Question

在lumen 中对数据库进行查询 由于条件过多判断 采取使用原生sql
如下

 public static function getAllNewsList($userId,$newId,$companyId,$communityId){
        $sql = "SELECT * FROM notices";
        $where = "WHERE addressee= :userId";
        if($newId) $where .= "or id> :newId";
        if($companyId) $where .= "or addressee= :companyId";
        if($communityId) $where .= "or addressee= :communityId";
        $query = $sql.$where;
        $res =NewNotice::select($query, ['userId' => $userId,'companyId' => $companyId,'communityId' => $communityId]);

        dd($res);
    }

但是结果不同于select(..)->get();
该如何处理啊

Answer 1

    public static function getAllNewsList($userId,$newId,$companyId,$communityId){
        $res = DB::table('notices')
            ->where('addressee',$userId);
        
        if($newId){
            $res->orWhere('id','>',$newId);
        }
        if($companyId){
            $res->orWhere('addressee',$companyId);
        }
        if($communityId){
            $res->orWhere('addressee',$communityId);
        }

        $res = $res->select('*');

        dd($res);

    }

Answer 2

按你这段代码, 是会报错的. sql各元素之间缺少空格.

使用DB::table($table)->select('*')->where($column)->get(); 这种build方式吧.

或者 DB::connection()->getPdo(), 使用PDO执行.

$q = \DB::query();

if ($xxx) {
    $q->where('xxx', 'xxx');
}

if ($yyy) {
    $q->where('yyy', 'yyy');
}

$q->get(); // 这就是结果.

Answer 3

经修改 NewNotice:: 该为BD::

新增

`
public static function getAllNewsList($userId,$newId,$companyId,$communityId){

    $communityArr = explode(",",$communityId);

    $sql = "SELECT * FROM notices";
    $where = " WHERE addressee= :userId";
    $array = array();
    $array['userId'] = $userId;
    if($newId){
        $where .= " AND id > :newId";
        $array['newId']=$newId;
    }

    if($companyId){

        $where .= " OR addressee= :companyId";
        $array['companyId']=$companyId;
    }
    if($communityId){
        for ($i=0;$i<count($communityArr);$i++){
            $where .= " OR addressee= $communityArr[$i]";
        }

    }
    $query = $sql.$where;
    $res = DB::select($query, $array);

    return($res);
}
`