elasticsearch-query-builder

composer require jfxy/elasticsearch-query-builder

• elasticsearch <= 6.8
• php >= 7.1
• Jfxy\ElasticsearchQuery\Builder****query() scrollQuery()
• Es

select

    public function select($fields) :self
    
    ->select('id','name')
    ->select(['id','name'])

where

• =,>,>=,<,<=,!=,<>
• whereorWherewhereNotorWhereNotorWherewhereNotorWhereNot or not mysql
• or

    public function where($field, $operator = null, $value = null, $boolean = 'and', $not = false, $filter = false) :self
    public function orWhere($field, $operator = null, $value = null) :self
    public function whereNot($field, $value = null) :self
    public function orWhereNot($field, $value = null) :self
    
    ->where('id',1)
    ->where('id','=',1)
    ->where('id',[1,2])                         //   ->whereIn('id',[1,2])
    ->where('news_postdate','<=','2020-09-01')  //   ->whereBetween('news_postdate',['<=' => '2020-09-01'])
    
    // 
    ->where(function($query){
        return $query->where('id',1)->orWhere('status','>',0);
    })
    ->orWhere(function($query){
        return $query->where('id',1)->orWhere('status','>',0);
    })
    
    // timewhere
    ->where(['id' => 1,'status' => [0,1],['time','>=','2020-09-01']])
    ->where(function($query){
        $query->where('id',1)->where('status',[0,1])->where('time','>=','2020-09-01');
    })
    
    // whereNot a != 1 and b != 2
    ->whereNot('a',1)->whereNot('b',2)
    
    // whereNot a != 1 or b != 2not(a=1 and b=2)
    ->whereNot(['a'=>1,'b'=>2])
    ->whereNot(function($query){
        $query->where('a',1)->where('b',2);
    })

filter

• wherefilter

    public function filter($field, $operator = null, $value = null, $boolean = 'and',$not = false) :self
    public function orFilter($field, $operator = null, $value = null) :self
    public function filterNot($field, $value = null) :self
    public function orFilterNot($field, $value = null) :self

in

    public function whereIn($field, array $value, $boolean = 'and', $not = false) :self
    public function whereNotIn($field, array $value, $boolean = 'and') :self
    public function orWhereIn($field, array $value) :self
    public function orWhereNotIn($field, array $value) :self
    
    ->whereIn('id',[1,2])

between

• >,>=,<,<=

    public function whereBetween($field, array $value, $boolean = 'and', $not = false) :self
    public function whereNotBetween($field, array $value, $boolean = 'and') :self
    public function orWhereBetween($field, array $value) :self
    public function orWhereNotBetween($field, array $value) :self
    
    ->whereBetween('id',[1,10])                     // 1 <= id <= 10
    ->whereBetween('id',[1,'<' => 10])              // 1 <= id < 10
    ->whereBetween('id',['>=' => 1,'<' => 10])      // 1 <= id < 10

exists

• null

    public function whereExists($field,$boolean = 'and', $not = false) :self
    public function whereNotExists($field) :self
    public function orWhereExists($field) :self
    public function orWhereNotExists($field) :self
    
    ->whereExists('news_uuid')

prefix

    public function wherePrefix($field, $value, $appendParams = [], $boolean = 'and', $not = false) :self
    public function whereNotPrefix($field, $value, $appendParams = []) :self
    public function orWherePrefix($field, $value, $appendParams = []) :self
    public function orWhereNotPrefix($field, $value, $appendParams = []) :self
    
    ->wherePrefix('news_url','http://www.baidu.com')

wildcard

    public function whereWildcard($field, $value, $appendParams = [], $boolean = 'and', $not = false) :self
    public function whereNotWildcard($field, $value, $appendParams = []) :self
    public function orWhereWildcard($field, $value, $appendParams = []) :self
    public function orWhereNotWildcard($field, $value, $appendParams = []) :self
    
    ->whereWildcard('media_name','*')

regexp

    public function whereRegexp($field, $value, $appendParams = [], $boolean = 'and', $not = false) :self
    public function whereNotRegexp($field, $value, $appendParams = []) :self
    public function orWhereRegexp($field, $value, $appendParams = []) :self
    public function orWhereNotRegexp($field, $value, $appendParams = []) :self
    
    ->whereRegexp('media_name','.*')

fuzzy

    public function whereFuzzy($field, $value, $appendParams = [], $boolean = 'and', $not = false) :self
    public function whereNotFuzzy($field, $value, $appendParams = []) :self
    public function orWhereFuzzy($field, $value, $appendParams = []) :self
    public function orWhereNotFuzzy($field, $value, $appendParams = []) :self
    
    ->whereFuzzy('news_title','')

whereRaw

    public function whereRaw($where, $boolean = 'and', $not = false) :self
    public function orWhereRaw($where) :self
    
    // whereterm,whereRaw
    ->whereRaw([
       "term" => [
           "news_title" => [
               "value" => "",
               "boost" => 2
           ]
       ]
   ])
   
   ->whereRaw([
       'bool' => [
           'must' => [
               "term" => [
                   "news_title" => [
                       "value" => "",
                       "boost" => 2
                   ]
               ]
           ]
       ]
   ])

match

• whereMatch$type=matchmatch_phrasematch_phrase_prefix
• whereMultiMatch$type=best_fieldsmost_fieldscross_fieldsphrasephrase_prefix

    // 
    public function whereMatch($field, $value = null,$type = 'match',array $appendParams = [], $boolean = 'and', $not = false) :self
    public function orWhereMatch($field, $value = null,$type = 'match',array $appendParams = []) :self
    public function whereNotMatch($field, $value = null,$type = 'match',array $appendParams = []) :self
    public function orWhereNotMatch($field, $value = null,$type = 'match',array $appendParams = []) :self
    // 
    public function whereMultiMatch($field, $value = null,$type = 'best_fields',array $appendParams = [], $boolean = 'and', $not = false) :self
    public function orWhereMultiMatch($field, $value = null,$type = 'best_fields',array $appendParams = []) :self
    public function whereNotMultiMatch($field, $value = null,$type = 'best_fields',array $appendParams = []) :self
    public function orWhereNotMultiMatch($field, $value = null,$type = 'best_fields',array $appendParams = []) :self
    
    ->whereMatch('news_title','','match_phrase',['slop'=>1])
    ->whereMultiMatch(['news_title','news_content'],'','phrase',["operator" => "OR"])

minimumShouldMatch

    public function minimumShouldMatch($value) :self
    
    ->where('aa',1)->orWhere('bb',1)->orWhere('cc',1)->minimumShouldMatch(2)
    
    ->where(function(Es $query){
        $query->where('aa',1)->orWhere('bb',1)->orWhere('cc',1)
            ->minimumShouldMatch('50%');
    })
    
    ->postWhere(function(Es $query){
        $query->where('aa',1)->orWhere('bb',1)->orWhere('cc',1)
            ->minimumShouldMatch('50%');
    })

whereNested nested

    public function whereNested($path,$wheres,$appendParams = []) :self
    
    ->whereNested('skus',function(Es $query){
        $query->where('skus.title','iphone')->where('skus.des','iphone');
    },['inner_hits'=>['highlight' => ['fields'=>['skus.title'=>new \stdClass()]]]]);
    
    ->whereNested('skus',['skus.title' => 'iphone','skus.description' => 'iphone',['skus.price','>','100']],['inner_hits'=>['highlight' => ['fields'=>['skus.title'=>new \stdClass()]]]]);

postWhere

• postWherepost_filter
• postWherewhere

    public function postWhere($field, $operator = null, $value = null, $boolean = 'and',$not = false) :self
    
    ->postWhere('platform','wx')
    ->postWhere(['platform' => ['wx','web'],['news_posttime','>','2020-09-01 00:00:00']])
    ->postWhere(function(Es $query){
        $query->where('platform','wx')->whereNotMatch('news_title','')->orWhereIn('news_postdate',['2020-09-01','2020-09-02']);
    })

when

• $valuetrue$callback$default$default

    public function when($value,$callback,$default = null) :self
    
    ->when(1 > 2,function($query){
        return $query->whereBetween('news_postdate',['2020-05-01','2020-05-05']);
    },function($query){
        return $query->whereBetween('news_postdate',['2020-05-09','2020-05-10']);
    })

collapse

• collapsecardinality
• collapsepaginatorpaginatorcardinalitycollapse

    public function collapse(string $field,array $appendParams = []) :self
    
    ->collapse('news_sim_hash')
    ->collapse('news_sim_hash')->aggs('alias','cardinality',['field'=>'news_sim_hash'])
    ->collapse('news_sim_hash')->cardinality('news_sim_hash')
    ->collapse('news_sim_hash')->paginator()

from

    public function from(int $value) :self

size

    public function size(int $value) :self

orderBy

    public function orderBy(string $field, $sort = 'asc') :self
    
    ->orderBy('news_posttime','asc')->orderBy('news_like_count','desc')

highlight

• EshighlightConfig

    // 
    public $highlightConfig = [
        "require_field_match" => false,     // 
        "number_of_fragments" => 0,         // 0
        "pre_tags" => "<em>",
        "post_tags" => "</em>",
    ];

• highlightConfighighlightConfig
• highlight

    public function highlight(string $field,array $params = [])
    public function highlightConfig(array $config = [])
    
    ->highlightConfig(['require_field_match'=>false,'number_of_fragments' => 0,'pre_tags'=>'<h3>','post_tags'=>'</h3>'])
    ->highlight('news_title')->highlight('news_digest',['number_of_fragments' => 0])

aggs

• $alias
• $typetermshistogramdate_histogramdate_rangerangecardinalityavgsumminmaxextended_statstop_hitsfilter...
• $params
• ...$subGroups

    public function aggs(string $alias,string $type = 'terms',$params = [], ... $subGroups) :self
    
    ->aggs('alias','terms',['field'=>'platform','size'=>15,'order' => ['_count'=>'asc']])
    ->aggs('alias','date_histogram',['field'=>'news_posttime','interval' => 'day','format' => 'yyyy-MM-dd','min_doc_count' => 0])
    ->aggs('alias','histogram',['field'=>'news_like_count','interval'=>10])
    ->aggs('alias','extended_stats',['field'=>'news_like_count'])
    ->aggs('alias','cardinality',['field'=>'news_sim_hash'])
    ->aggs('alias','avg',['field'=>'news_like_count'])
    ->aggs('alias','sum',['field'=>'news_like_count'])
    ->aggs('alias','min',['field'=>'news_like_count'])
    ->aggs('alias','max',['field'=>'news_like_count'])
    ->aggs('alias','date_range',[
        'field' => 'news_posttime',
        'format'=> 'yyyy-MM-dd',
        'ranges'=>[
            ['from'=>'2020-09-01','to'=>'2020-09-02'],
            ['from'=>"2020-09-02",'to'=>'2020-09-03']
        ]
    ])
    ->aggs('alias','range',[
        'field' => 'media_CI',
        'ranges'=>[
            ['key'=>'0-500','to'=>'500'],
            ['key'=>'500-1000','from'=>'500','to'=>'1000'],
            ['key'=>'1000-','from'=>'1000'],
        ]
    ])
    ->aggs('alias','top_hits',['size'=>1])
    ->aggs('alias','filter',['term' => ['platform' => 'web']])

• groupByaggsterms

    public function groupBy(string $field, array $appendParams = [], ... $subGroups) :self
    
    ->groupBy('platform',['size'=>20,'order'=>['_count'=>'asc']])
    
    // $appendParams 
    $appendParams = [
        'size' => 10,                   // 
        'order' => ['_count'=>'desc']   // 
        'order' => ['_count'=>'asc']    // 
        'order' => ['_key'=>'desc']     // key
        'order' => ['_key'=>'asc']      // key
        ...
    ]

• dateGroupByaggsdate_histogram

    public function dateGroupBy(string $field,string $interval = 'day',string $format = "yyyy-MM-dd",array $appendParams = [], ... $subGroups) :self
    
    ->dateGroupBy('news_posttime','day','yyyy-MM-dd')

• cardinalityaggscardinality

    public function cardinality(string $field,array $appendParams = []) :self
    
    ->cardinality('news_sim_hash')

• avgaggsavg

    public function avg(string $field,array $appendParams = []) :self
    
    ->avg('media_CI')

• sumaggssum

    public function sum(string $field,array $appendParams = []) :self
    
    ->sum('media_CI')

• minaggsmin

    public function min(string $field,array $appendParams = []) :self
    
    ->min('media_CI')

• maxaggsmax

    public function max(string $field,array $appendParams = []) :self
    
    ->max('media_CI')

• statsaggsstats

    public function stats(string $field,array $appendParams = []) :self
    
    ->stats('media_CI')

• extendedStatsaggsextended_stats

    public function extendedStats(string $field,array $appendParams = []) :self
    
    ->extendedStats('media_CI')

• topHitstop_hits

    public function topHits($params) :self
    
    ->topHits([
        'from' => 2,
        'size' => 1,
        'sort' => ['news_posttime' => ['order' => 'asc']],
        '_source' => ['news_title','news_posttime','news_url','news_digest'],
        'highlight' => [
            'require_field_match'=>true,
            'pre_tags'=>'<h3>',
            'post_tags'=>'</h3>',
            'fields' => [
                'news_title' => new \stdClass(),
                'news_digest' => ['number_of_fragments' => 0]]
            ]
    ]);
    
    ->topHits(function(Es $query){
        $query->size(1)->from(2)
            ->orderBy('news_posttime','asc')
            ->select(['news_title','news_posttime','news_url','news_digest'])
            ->highlight('news_title')
            ->highlight('news_digest',['number_of_fragments' => 0]);
    })

• aggsFilterfilter$whereswhere

    public function aggsFilter($alias,$wheres,... $subGroups) :self
    
    ->aggsFilter('alias1',function(Es $query){
        $query->where('platform','web');
    },function(Es $query){
        $query->groupBy('platform_name',['size'=>30]);
    })
    ->aggsFilter('alias2',['platform'=>'web','news_title'=>'',['news_postdate','>=','2020-09-01']],function(Es $query){
        $query->groupBy('platform_name',['size'=>30]);
    })

raw

• dsl,

    public function raw($dsl) :self
    
    ->raw(['query'=>['match_all' => new \stdClass()]])->get()
    ->raw(json_encode(['query'=>['match_all' => new \stdClass()]]))->get()

dsl

• dsl$type = 'json'json

    public function dsl($type = 'array')

get

• $directReturn = true

    public function get($directReturn = false)
    
    // $directReturn = false
    [
        'total'     => ,
        'list'      => ,
        'aggs'      => ,
        'scroll_id' => scroll_id
    ]

paginator

• paginatorcollapsepaginatorcardinalitycollapse

    public function paginator(int $page = 1, int $size = 10)
    
    ->collapse('news_sim_hash')->paginator()
    
    [
        'total'             => collapse,
        'original_total'    => collapse,
        'per_page'          => ,
        'current_page'      => ,
        'last_page'         => ,
        'list'              => ,
        'aggs'              => 
    ]

first

• $directReturn = true

    public function first($directReturn = false)

count

    public function count()

scroll

    $data = Es::init()->scroll()->size(1000)->where('platform','app')->get();
    $es = Es::init();
    while(true){
        $data = $es->scrollId($data['scroll_id'])->get();
        // do something
        ...
    }

    // orand
    $keywordGroups = [
        ['',''],
        ['',''],
    ];
    public function keywords($keywordGroups,$type = 'full'){
        $this->where(function(self $query)use($keywordGroups,$type){
            foreach($keywordGroups as $keywordGroup){
                $query->orWhere(function(self $query1)use($keywordGroup,$type){
                    foreach($keywordGroup as $keyword){
                        if('full' == $type){
                            $query1->whereMultiMatch(['news_title','news_content'],$keyword,'phrase',["operator" => "OR"]);
                        }elseif('title' == $type){
                            $query1->whereMatch('news_title',$keyword,'match_phrase');
                        }elseif('content' == $type){
                            $query1->whereMatch('news_content',$keyword,'match_phrase');
                        }
                    }
                });
            }
        });
        return $this;
    }

    // 
    $keywords = ['',''];
    public function keywordsExclude($keywords){
        $this->where(function(self $query)use($keywords){
            foreach($keywords as $keyword){
                $query->whereNotMultiMatch(['news_title','news_content'],$keyword,'phrase',["operator" => "OR"]);
            }
        });
        return $this;
    }

queryscrollQuery

    public function query()
    {
        if(!is_string($this->dsl)){
            $this->dsl = json_encode($this->dsl,JSON_UNESCAPED_UNICODE);
        }
        
        /****$this->dsles...****/

        return $response;
    }

    Es::init()->select('id','name')->where('id',3)->dsl();
    Es::init()->select('id','name')->where('id',3)->groupBy('platform_name')->get();
    Es::init()->select('id','name')->where('id',3)->paginator(2,15);
    Es::init()->select('id','name')->where('id',3)->first();
    Es::init()->select('id','name')->where('id',3)->count();
    
    Es::init()->select('news_title','news_url','news_uuid','platform')
    ->where('platform',['wx','web','app'])
    ->whereBetween('news_postdate',['2020-09-01','2020-09-10'])
    ->keywords([['',''],['','']],'full')
    ->keywordsExclude(['',''])
    ->highlight('news_title')
    ->groupBy('platform',['size'=>20,'order'=>['_count'=>'asc']],function(Es $query){
        $query->groupBy('platform_name',['size'=>30]);
    },function(Es $query){
        $query->groupBy('platform_domian_pri',['size'=>30],function(Es $query){
            $query->topHits(['size'=>1]);
        });
    })
    ->dateGroupBy('news_posttime')
    ->aggs('news_like_count','histogram',['interval'=>100])
    ->cardinality('news_sim_hash')
    ->avg('media_CI')
    ->sum('media_CI')
    ->max('media_CI')
    ->min('media_CI')
    ->extendedStats('media_CI')
    ->get();