make entity searchable, extension for entity repository

searchable_entity.php

<?php

namespace Test\Bundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * AdvBaseRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
abstract class BaseRepository extends EntityRepository
{
    const AGGREATE_FIELD_PREFIX = '_aggr_';
    protected $bundleName;
    protected $entityName;
    protected $alias;
    
    /**
     * Search by filters
     * @param array $filters 指定的搜索条件，
     *              1. 与查询['id'=>'1','name'='abc'] 返回 id=1 and name='abc', 
     *              2. 区间['id'=>'>1','name'='abc'] 返回 id>1 and name='abc'， 支持>,<,>=,<=,<>, like.like见下面例子
     *              4. 模糊查询['id'=>'%1%','name'='abc'] 返回 id like %1% and name='abc'， 
     *              5. 范围查询['id'=>['>1', '<10'],'name'='abc'] 范围查询,返回 id >1 and id<10 and name='abc'
     *              6. 范围查询['id'=>['>1', '<10'],'name'='abc'] 范围查询,返回 id >1 and id<10 and name='abc'，
     *              7. 同字段的或查询 ['-id'=>['1', '<10']]，返回 where id = 1 or id<10
     * @param array $sorts 指定返回的顺序，如['id'=>'ASC','name'='DESC','createdBy'='ASC'] , ASC 和 DESC是大小不敏感的
     * @param array $fields 需要返回的字段的名字，如['id','name','createdBy'], 字段对应的是doctrine entity的mapping字段名字而不是数据库的column name
     *              查询 aggregation 如 count(id), 或者avg(cashback, userName)， sum(cashback, userName)
     * @param array $page 查询的页的页码，起始值是1
     * @param array $limit 每页返回的数据条数
     * @return [
     *                      $alias => $entities,
     *                      'page' => $page,
     *                      'limit' => $limit,
     *                      'filters' => $filters,
     *                      'sorts' => $sorts,
     *                      'total' => $total,
     *                      'distinct'=$distinct 是否返回distint的结果， distinct的结果的count会以object的形式返回， key是 distTotal
     *      ];
     */
    public function search($filters=[], $sorts=[], $fields=[], $page=1, $limit=10, $distinct=false, $orOpt = false, $qbModifierFn = null)
    {
        $em = $this->_em;
        $alias = $this->alias;
        $entityFullName = $this->bundleName.':'.$this->entityName;
        $aggrFieldPrefix = self::AGGREATE_FIELD_PREFIX;
        
        $qb = $em->createQueryBuilder();
        $countQb = $em->createQueryBuilder();


        // if there are specified fields , use them as partial select
        if(count($fields)){
            $selStr = [];
            $countSelStr = [];
            
            //should dinstinct result
            $disctinctPrefix = $distinct ? ' DISTINCT ' : '';
            
            //if there is no id, add it, so we can have default id distinct
            $totalKey = $aggrFieldPrefix.'id';
            if(!in_array('id', $fields)){
                $countSelStr[] = 'count('.$disctinctPrefix.$alias.'.id) as id';
            }

            $patterns =[
                            'aggr' => '/(count|avg|sum|min|max)(?=\(.*\))\(([a-zA-Z0-9,\s\(\)_]+)+\)/',
                            'group' => '/([a-zA-Z0-9\s\(\)]+),([a-zA-Z0-9\s\(\)_]+)/',
                            'timefn' => '/(year|month|date|week|weekday|day|hour|minute|second)(?=.*)\(([a-zA-Z0-9\s_]+)*\)/',
                            'field' =>'/[a-zA-Z0-9_]+/',
            ];
            //tokeniser and parser
            function getAstTree($field, $patterns){
                $node = null;
                foreach($patterns as $ptype => $pattern){
                    $matchs = [];
                    preg_match($pattern, $field, $matchs);
                    $mc = count($matchs);
                    switch ($ptype) {
                        case 'aggr':
                            if( $mc == 3){
                                $value = getAstTree($matchs[2], $patterns);
                                $node = ['ptype'=>$ptype, 'type'=>$matchs[1], 'value' => $value];
                                return $node;
                            }
                        break;
                        case 'group':
                            if( $mc == 3){
                                $value = getAstTree($matchs[1], $patterns);
                                $group = getAstTree($matchs[2], $patterns);
                                $node = ['ptype'=>$ptype, 'type'=>'group', 'value' => $value, 'group' => $group ];
                                return $node;
                            }
                        break;
                        case 'timefn':
                            if( $mc == 3){
                                $value = getAstTree($matchs[2], $patterns);
                                $node = ['ptype'=>$ptype, 'type'=>$matchs[1], 'value' => $value];
                                return $node;
                            }
                        break;
                        case 'field':
                            if( $mc == 1){
                                $value = $matchs[0];
                                $node = ['ptype'=>$ptype, 'type'=>'string', 'value' => $value];
                                return $node;
                            }
                        break;
                        default:
                        break;
                    }
                }
                
                return $node;
            }
            // compile
            function getCompiledSql($node, $alias, $aggrFieldPrefix, &$groups, $disctinctPrefix, &$countSelStr, $addAlias=true, $addPrefix = true, $forGroup=false){
                $str = '';
                $ptype = $node['ptype'];
                switch ($ptype) {
                    case 'aggr': //form like ?fields[]=sum(cashback)
                    case 'timefn': //form like ?fields[]=date(createdTime)
                        
                        $inner = getCompiledSql($node['value'], $alias, $aggrFieldPrefix, $groups, $disctinctPrefix, $countSelStr, false, true, $forGroup);
                        if(!$addPrefix){
                            $aggrFieldPrefix = '';
                        }
                        $escapeAlias = '';
                        if($addAlias){
                            $escapeAlias = $aggrFieldPrefix.preg_replace('/\(?'.$alias.'.?|\)?/', '', $inner);
                        }
                        if($forGroup){
                            $groups[] = $escapeAlias;
                        }else{
                            $str = $node['type'].'('.$inner.')'.' AS '.$escapeAlias;
                        }
                        return $str;
                    break;
                    case 'group': // form like ?fields[]=count(id, date(createdTime))
                        $field = $node['value'];
                        $group = $node['group'];
                        $str = getCompiledSql($field, $alias, $aggrFieldPrefix, $groups, $disctinctPrefix, $countSelStr, false);
                        getCompiledSql($group, $alias, $aggrFieldPrefix, $groups, $disctinctPrefix, $countSelStr, true, true, true);
                        return $str;
                    case 'field':
                        $str = empty($alias) ? $node['value'] : $alias.'.'.$node['value'];
                        if(!$forGroup) // if this is a loop for group, then do add it to count
                            $countSelStr[] = 'COUNT('.$disctinctPrefix.$str.') AS '.$aggrFieldPrefix.$node['value'];
                        else
                            $groups[] = $str;
                        return $str;
                    break;
                    default:
                    break;
                }
                return $str;
            }
            
            $selects = [];
            $groups = [];
            foreach ($fields as $field){
                $node = getAstTree($field, $patterns);
                // compile 
                $selects[] = getCompiledSql($node, $alias, $aggrFieldPrefix, $groups, $disctinctPrefix, $countSelStr);
            }
            $selectStr = $disctinctPrefix.implode(',', $selects);
            $qb->select($selectStr);
            
            if(count($groups) > 0){
                $grpByStr = implode(',', $groups);
                $qb->groupBy($grpByStr);
            }
            
            $countQb->select(implode(',', $countSelStr));
        }else{
            // else select all fields
            $qb->select($alias);
            $countQb->select('COUNT('.$alias.'.id) AS id');
        }
        //create from 
        $qb->from($entityFullName, $alias);
        $countQb->from($entityFullName, $alias);
        
        //create sort
        if(count($sorts) > 0){
            foreach ($sorts as $field => $sort){
                $order = strcasecmp($sort, 'ASC') == 0 ? 'ASC' : 'DESC';
                // don't need alias before 
                if(strpos($field, $aggrFieldPrefix) !== FALSE){
                    $qb->addOrderBy($field, $order);
                }else{
                    $qb->addOrderBy($alias.'.'.$field, $order);
                }
            }
        }
        // create filter
        if(count($filters) > 0){
            $wheres = $this->buildWhereObj($filters);
            foreach($wheres as $whereObj){
                $where = $whereObj['where'];
                if($orOpt === true && strpos($where, 'isDeleted') == false ){
                    $qb->orWhere($where);
                    $countQb->orWhere($where);
                }else{
                    $qb->andWhere($where);
                    $countQb->andWhere($where);
                }
                
                $values = $whereObj['values'];
                foreach($values as $valueObj){
                    $value = $valueObj['value'];
                    $offset = $valueObj['offset'];
                    $qb->setParameter($offset, $value);
                    $countQb->setParameter($offset, $value);
                }
            }
        }


        //craete page offset
        $limit = empty($limit) || is_nan($limit) ? 10 : $limit;
        $offset = empty($page) || is_nan($page) || $page < 1 ? 0 : ($page - 1) * $limit;
        
        $qb->setFirstResult( $offset )
        ->setMaxResults( $limit );
        
        if($qbModifierFn){
            $countQuery = $qbModifierFn($countQb, true)->getQuery();
            $query = $qbModifierFn($qb)->getQuery();
        }
        $countQuery = $countQb->getQuery();
        $query = $qb->getQuery();
        
        // $sql = $query->getSQL();
        // var_dump($sql);
        // $parameter = $query->getParameters();
        try{
            $entities = $query->getResult();
            $totalResult = $countQuery->getResult()[0];
            $total = array_key_exists('id', $totalResult) ? $totalResult['id'] :$totalResult[$totalKey];
            $result = [
                            'results' => $entities,
                            'page' => empty($page) ? 1 : $page,
                            'limit' => empty($limit) ? 10 : $limit,
                            'filters' => $filters,
                            'sorts' => $sorts,
                            'total' => $total,
            ];
            if($distinct){
                $result['distTotal'] = $totalResult;
            }
            return $result;
        }catch(\Exception $ex){
            // var_dump($ex);
            return [
                            'error' => $ex->getMessage() . (empty($ex->getPrevious()) ? null : $ex->getPrevious()->getMessage()),
            ];
        }
    }
    
    /**
     * 根据条件统计个数
     * @param array $filters
     * @return object ['total'=>0]
     */
    public function count($filters=[], $fields=[], $distinct=false)
    {
        $em = $this->_em;
        $alias = $this->alias;
        $entityFullName = $this->bundleName.':'.$this->entityName;
        $countQb = $em->createQueryBuilder();

        $countSelStr = [];
        if(!empty($fields)){
            //should dinstinct result
            $disctinctPrefix = $distinct ? ' DISTINCT ' : '';
            //if there is no id, add it, so we can have default id distinct
            if(!in_array('id', $fields)){
                $countSelStr[] = 'count('.$disctinctPrefix.$alias.'.id) as id';
            }
            foreach ($fields as $field){
                $fullName = $alias .'.'.$field;
                $selStr[] = $fullName;
                $countSelStr[] = 'count('.$disctinctPrefix.$fullName.') as '.$field;
            }
        }else{
            $countQb->select('count ('.$alias.'.id) as id');
        }
        
        // if there are specified fields , use them as partial select
        $countQb->select(implode(',', $countSelStr));
        
        $countQb->from($entityFullName, $alias);
        if(count($filters) > 0){
            $wheres = $this->buildWhereObj($filters);
            foreach($wheres as $whereObj){
                $where = $whereObj['where'];
                $countQb->andWhere($where);
                $values = $whereObj['values'];
                foreach($values as $valueObj){
                    $value = $valueObj['value'];
                    $offset = $valueObj['offset'];
                    $countQb->setParameter($offset, $value);
                }
            }
        }
       
        $countQuery = $countQb->getQuery();
        try{
            $totalResult = $countQuery->getResult()[0];
            $total = $totalResult['id'];
            $result = [
                            'total' => $total,
            ];
            if($distinct){
                $result['distTotal'] = $totalResult;
            }
            return $result;
        }catch(\Exception $ex){
            return [
                 'error' => $ex->getMessage()
            ];
        }
    }
    
    /**
     * 构建where需要的数据
     * @param array $filters
     * @return multitype:
     */
    private function buildWhereObj($filters){
        //create filter and set it as parameters
        $offset = 0;
        // 对应所有的where条件的值
        $vals = [];
        // 所有的where条件
        $allWhere = [];
        /*
         * values can be string or array of string
         * case 1 如 key = id, 单个value =1 : id=1
         * case 2 如 key = id, 多个value= ">=1" 和 value = "<=10",条件间是与的关系: id = ['>=1', '<=10']
         * case 3 如 key = -id, 多个value= ">=1" 和 value = "<=10",条件间是或的关系: -id = ['>=1', '<=10']
         * case 4 如 key = |id, 多个value= ">=1" 和 value = "<=10",条件间是或的关系: -id = ['>=1', '<=10']
         */
        foreach ($filters as $filterKey=>$compositValue){
            /*
             * 通过buildWhereObj方法把compisitkey中的key和value分离出来
             * 查找当前的filter的设置， 如果有-或者+作为前缀的，就分离出来
             * 如果前缀为- 则是OR关系
             * 如果前缀为 + 或者无前缀则是 AND 关系
             */ 
            $fp = '/^(\-|\+)?(.*)$/'; //Pattern to seperate target
            preg_match($fp, $filterKey, $fmatches);
            $orFilterPrefix = $fmatches[1];
            $filter = $fmatches[2];
            
            $compositValues = null;
            if(is_array($compositValue)){
                $compositValues = $compositValue;
            }else{
                $compositValues = [$compositValue];
            }
            
             
            // Processing parameters , 为同一个filter产生不同的条件， 比如说是id=['>=1', '<=10']这种情况，filter是id， 条件是数组中的值
            // 最终产生 where id>=?1 and id<=?2, 加上对应1，2的value
            $wheres = []; //循环开始前初始化用来存储where的最终产生 where id>=?1 and id<=?2 字符串
            $whereVals= []; //循环开始前初始化用来存储where的最终产生的值，对应以上的?1
            $pattern = '/^(!=|<>|=|>=|<=|>|<|%)?([a-z0-9A-Z\s\-:\p{Han}*]*)(%)?/u';
            $timeFnPattern = '/(year|month|date|week|weekday|day|hour|minute|second)(?=.*)\(([a-zA-Z0-9\s_]+)*\)/';
            foreach($compositValues as $compVal){
                $matches = [];
                preg_match($pattern, $compVal, $matches);
                if(count($matches) >= 3){
                    $operator = $matches[1];
                    $suffixOp = count($matches) == 4 ? '%' : '';
                    $value = $matches[2].$suffixOp;
                    $op = '=?';
                    if(count($matches) == 3){
                        switch ($operator) {
                            case '!=':
                                $op = '<>?';
                                break;
                            case '<>':
                                $op = '<>?';
                                break;
                            case '>':
                                $op = '>?';
                                break;
                            case '<':
                                $op = '<?';
                                break;
                            case '>=':
                                $op = '>=?';
                                break;
                            case '<=':
                                $op = '<=?';
                                break;
                            case '%':
                                $op = 'like ?';
                                $value = $operator.$value;
                                break;
                            default :
                                $op = '=?';
                        }
                    }else{
                        $op = 'like ?';
                        $value = $operator.$value;
                    }
                    $offset += 1;
                    
                    $timeFnPatternM = [];
                    preg_match($timeFnPattern, $filter, $timeFnPatternM);
                    if(count($timeFnPatternM) == 3){
                        $filter = $timeFnPatternM[1].'('. $this->alias.'.'.$timeFnPatternM[2].')';
                        $wheres[] = $filter . ' ' . $op . $offset;
                    }else{
                        $wheres[] = $this->alias . '.' . $filter . ' ' . $op . $offset;
                    }
                    
                    $whereVals[] = ['value'=>$value, 'offset'=>$offset];
                }
            }
            $condOp = empty($orFilterPrefix) || $orFilterPrefix == '+' ? 'and' : 'or';
            $whereClause = implode(' '.$condOp.' ', $wheres);
            array_push($allWhere, [
                'where' => $whereClause,
                'values' => $whereVals
            ]);
        }
        return $allWhere;
    }
}