php实现简单的SQL Builder

最近在使用Swoole C扩展来开发自己的项目, 本来想要使用golang 可是由于团队中的程序员大多数都是php 便放弃了。   我选用的是 swoole  framework 来开发 后端api服务, 深入应用的时候 让我最痛苦的是那相当干净的官方文档, 最终我读了一遍源代码 才顺利上手 。

可能由于框架很久没有维护了 发现其orm中 有几处小bug, 于是便自己写了一个 简单的sql 构造器,还算挺方便的,贴下代码 。

/**
 * PHPProject
 * SqlBuilder.php Created by usher.yue.
 * User: usher.yue
 * Date: 16/7/7
 * Time: 下午12:32
 * 心怀教育梦－烟台网格软件技术有限公司
 */
class SqlBuilder{

    /**
     * sql结果
     * @var
     */
    protected  $_sql="";
    protected  $_select="";
    protected  $_from="";
    protected  $_where="";
    protected  $_update="";
    protected  $_insert="";
    protected  $_join="";
    protected  $_value="";
    protected  $_limit="";
    protected  $_set="";
    protected  $_method;
    protected  $_order="";

    //执行方式
    const SQL_INSERT=0;
    const SQL_UPDATE=1;
    const SQL_SELECT=2;

    /**
     * clear sql
     */
    public function clear(){
        $this->_select="";
        $this->_from="";
        $this->_where="";
        $this->_update="";
        $this->_insert="";
        $this->_method="";
        $this->_value="";
        $this->_set="";
        $this->_limit="";
        $this->_order="";
    }

    /**
     * @param array $fields
     * @return $this
     */
    public function select($fields=array()){
        $this->clear();
        $this->_method=SqlBuilder::SQL_SELECT ;
        if(is_array($fields)){
            $selectFields=implode(' , ',$fields);
            $this->_select=($selectFields=="")?" * ":$selectFields;
        }else if(is_string($fields)){
            $this->_select=$fields;
        }
        return $this;
    }

    /**
     * @param array $tables
     * @return $this
     */
    public function  from($tables=array()){
        if(is_array($tables)){
            $from=implode(',',$tables);
            $this->_from=' ' . $from .'  ';
        }else if(is_string($tables)){
            $this->_from=$tables;
        }
        return $this ;
    }

    /**配合表达式
     * @param array $conditionExpr
     * @return $this
     */
    public function wheres($conditionExpr=array()){
        //拼接expr
        if(array_key_exists('expr',$conditionExpr)){
            $this->_where.=$conditionExpr['expr'];
            unset($conditionExpr['expr']);
        }
        $arrSetExpr=[];
        foreach($conditionExpr as $k=>$v){
            $arrSetExpr[]="$k='$v'";
        }
        $condition=implode(' and ',$arrSetExpr);
        $this->_where=($condition==''?$this->_where:$this->_where.' and '.$condition);
        return $this ;
    }

    /**
     * @param $conditon
     * @return $this
     */
    public function where($conditon){
        if(is_string($conditon)){
            $this->_where.=" $conditon";
        }
        else if(is_array($conditon)){
            $arrCondition=[];
            foreach($conditon as $k=>$v){
                $arrCondition[]="$k='$v'";
            }
            if(!empty($arrCondition)){
                $this->_where.=implode(" and ",$arrCondition);
            }
        }
        return $this ;
    }



    /**
     * @param $condition
     * @return $this
     */
    public function  on($condition){
        if(is_string($condition)){
            $this->_join.=" on $condition ";
         }else if(is_array($condition)){
            $arrCondition=[];
            foreach($condition as $k=>$v){
                $arrCondition[]="$k='$v'";
            }
            $this->_join.=" on ".implode(" and ",$arrCondition);
        }
        return $this ;
    }

    /**
     * @param $tablename
     * @param string $join_type
     * @return $this
     */
    public function join($tablename,$join_type='join'){

        $this->_join.=" $join_type $tablename ";
        return $this ;
    }


    /**
     * @param $arr
     */
    public  function in($arr=""){
        if(is_array($arr)){
            foreach($arr as &$field){
                 $field='\''.$field.'\'';
            }
            $ins=' in (' .implode(',',$arr).')';
            $this->_where.="  $ins";
        }else if(is_string($arr)){
             $this->_where.=" in($arr)";
        }
        return $this;
    }

    /**
     * 支持关联数组和字符串
     * @param $field
     */
    public  function  and_($field){
        if(is_string($field)){
            $this->_where.=" and $field";
        }
        else if(is_array($field)){
            $arrCondition=[];
            foreach($field as $k=>$v){
               $arrCondition[]="$k='$v'";
            }
            if(!empty($arrCondition)){
                $this->_where.=" and ".implode(" and ",$arrCondition);
            }
        }
         return $this ;
    }

    /**
     * @param $condition
     * @return $this
     */
    public function onOr($condition){
        $this->_join.=" or $condition ";
        return $this;
    }

    /**
     * @param array $tables
     * @return $this
     */
    public function update($tables){
        $this->clear();
        $this->_method=SqlBuilder::SQL_UPDATE ;
        if(is_array($tables)){
            $this->_update=implode(",",$tables);
        }else if(is_string($tables)){
            $this->_update=" $tables ";
        }
        return $this ;
    }

    /**
     * @param array $conditionExpr
     * @return $this
     */
    public function  set($conditionExpr=array()){
        //拼接expr
        if(is_array($conditionExpr)){
            if(array_key_exists('expr',$conditionExpr)){
                $this->_set.=$conditionExpr['expr'];
                unset($conditionExpr['expr']);
            }

            $arrSetExpr=[];
            foreach($conditionExpr as $k=>$v){
                $arrSetExpr[]="$k='$v'";
            }
            $condition=implode(' , ',$arrSetExpr);
            $this->_set=($this->_set==''?$condition:$this->_set.' , '.$condition);
        }else if(is_string($conditionExpr)){
            $this->_set.=" $conditionExpr ";
        }
        return $this;
    }

    /** get sql
     * @return string
     */
    public function sql(){
        $sqlCmd="";
        switch($this->_method){
            case SqlBuilder::SQL_SELECT:
            {
                $sqlCmd="select {$this->_select} from {$this->_from}" ;
                if($this->_join){
                    $sqlCmd.=$this->_join;
                }
                if($this->_where){
                    $sqlCmd.=' where '.$this->_where;
                }
                if($this->_limit){
                    $sqlCmd.=$this->_limit;
                }
                break;
            }
            case SqlBuilder::SQL_UPDATE:
            {
                $sqlCmd="update {$this->_update } set {$this->_set}" ;
                if($this->_where){
                    $sqlCmd.=' where '.$this->_where;
                }
                break;
            }
            case SqlBuilder::SQL_INSERT:
            {
                $sqlCmd="insert into  {$this->_insert } VALUES  {$this->_value}" ;
                break;
            }
        }
        return $sqlCmd ;
    }

    /**
     * @param array $fields
     * @param array $values
     */
    public function values($values=[[]]){

        $valueList=[];
        foreach($values as &$value){
             foreach($value as &$v){
                    $v="'$v'";
             }
             $valueList[]="(".implode(",",$value).")";
        }
        $this->_value=implode(",",$valueList);
        return $this;
    }

    /**
     * @param array $table
     * @param array $fields
     * @return $this
     */
    public function insertinto($table,$fields){
        $this->clear();
        $this->_method=SqlBuilder::SQL_INSERT;
        if(is_array($table)){
            $this->_insert=implode(",",$table) ;
        }else if(is_string($table)){
            $this->_insert=$table ;
        }
        if(is_array($fields)){
            $insertFields=implode(',',$fields);
            if($insertFields!=""){
                $this->_insert.= " ($insertFields)  ";
            }
        }else if(is_string($fields)){
            $this->_insert.= " ($fields)  ";
        }
        return $this;
    }

    /**
     * @param $num
     */
    public function limit($num,$offset=0){
        if($offset<=0)
            $this->_limit=" limit $num";
        else
            $this->_limit=" limit $num,$offset ";
        return $this ;
    }

    /**
     * @param $field
     */
    public  function orderby($field='',$order='desc'){
        if(is_array($field)){
            $this->_order=" ".implode(',',$field)." $order";
        }else{
            $this->_order=" $field $order";
        }
        return $this ;
    }

}

//$builder=new SqlBuilder() ;
//$builder->select(['a','b'])->from(['aa as ','bb vd'])->join("t1")->on("a=5")->onAnd("c=1")->wheres(['ax'=>1,'expr'=>"cx>=1"])->limit(100);
//var_dump($builder->sql());
//$builder->update(['a','b'])->set(['a'=>1,'ss'=>2])->where("a>1")->whereAnd("b<1");
//var_dump($builder->sql());
//$builder->insertinto("table_1",["a","b","c","d"])->values([["aaa","bbb","ccc","ddd"],["aaa","bbb","ccc","ddd"],["aaa","bbb","ccc","ddd"]]);
//var_dump($builder->sql());

//完整测试
//echo $builder->select("*")->from("a,b as 3")->join("b")->on(['ccsxx'=>34566,'dss'=>"3"])->where(["a4"=>12,"2b"=>32,"c23"=>"42"])->and_("b=2")->and_(["a"=>1,"b"=>2,"c"=>"4"])->and_("s")->in([1,2,3,43,2,77])->orderby("cc desc")->limit(1,2)->sql();
//echo $builder->insertinto("a","c,d,s,d,e")->values([[1,2,3,4,5],[2,3,4,5,6],[32,43,43,43]])->sql();
//echo $builder->update("a")->set("a=1,b=4,c=c+1")->where("c>c+1")->and_("c<6")->sql();

经过测试应该没多大问题