Background

MySQL and ES have very big differences in nested queries, so this section is specially organized in version 2.0 to help you understand the differences quickly and get started.

提示

The usage and functions in Easy-Es are the same as those in MP, so that users can learn and use the features that are not in MP but are unique in ES.

ES four nested queries

MySQL	Mybatis-Plus	ES	Easy-Es
and Nested	and(Consumer)	must	and(Consumer)
or nested	or (Consumer)	should	or (Consumer)
none	none	filter	filter(Consumer)
nothing	must_not	not(Consumer)

ES Four Splice Queries

MySQL	Mybatis-Plus	ES	Easy-Es
and splice	default	must	default
or splice	or()	should	or()
none	none	filter	filter()
none	none	must_not	not()

提示

If you've used MP, it's easy to understand the difference, and if you haven't used MP, it's okay, we just need to figure out the difference between nested types and spliced types. The difference is that the filter does not calculate the score, which is better for performance, but does not support sorting by score.

How to understand and use nesting and splicing

Simply put, nesting is with parentheses, splicing is without parentheses, or nesting is with something inside, splicing is without something inside, how to understand this paragraph? Take a familiar section of SQL in MySQL as an example:

  // The or in this is the splice or
  where name = 'old man' or name = 'demented man';
  // Write it in MP or EE as
  wrapper.eq(name, "old man").or().eq(name, "old man");
  
  
  // The or in this is a nested or
  where name = 'old man' or (age = 18 and size = 18)
  // written in MP or EE is
  wrapper.eq(name, "old man").or(i->i.eq(age,18).eq(size,18));

The above example should give you a good understanding of the difference between splicing and nesting, which corresponds to es, where nesting means encapsulating all the query conditions in a nested boolQuery into a newly created boolQuery and then splicing it into the root boolQuery, while splicing means splicing the query conditions directly into the root boolQuery. In EE conditions and conditions directly with the default splicing method is to and splicing, because 95% of the use of scenarios are and splicing, so directly omitted and this splicing, which is the same as MP

 // sql 
 where name = 'old man' and size = 18
 // With EE or MP you can write it directly as
 wrapper.eq(name, "old man").eq(size,18)

If you need to rewrite the default splicing method just add the corresponding splice type, for example:

 // sql
 where name = 'old man' or size = 18
 // Write directly with EE as
 wrapper.eq(name, "old man").or().eq(size,18);

 // sql
 where name = 'old man' and age ! = 18
 // written in EE as
 wrapper.eq(name, "old man").not().eq(age,18);
  
 // All table 'not' conditions can be spliced with not()
 wrapper.eq(name, "old man").not().eq(age,18).not().match(desc,'is a pure and good man');
```''
So, if you have only one content in the condition, the direct use of splicing can be solved, if the condition has more than one content, you can use nesting, about nesting and splicing understanding, is not very simple, you learn to waste?


## Advantage Comparison

Compared to MySQL, which has only 2 types of nesting, ES has 4 types, and the difference in encapsulation is also very big, MySQL query conditions are encapsulated in the form of FIFO queue
But ES is encapsulated in the form of tree structure, in the deeper level of query, the difficulty and complexity of even ES veterans are easy to mistake, but with Easy-Es,
In order to keep the same syntax as MP, and to shield the huge differences between ES and MySQL, this piece of
It took me nearly a year of fragmentation to complete this piece of content is the most difficult piece of the entire framework, but it was worth it, do not believe us to continue to see.

Here we use a specific complex query to compare the use of cases, the advantages at a glance:

```java
 // MySQL syntax  
 where business_type = 1
 and (state = 9 or (state = 8 and bidding_sign = 1))
 or (business_type = 2 and state in (2,3));

 // Easy-Es and Mybatis-Plus syntax 
 wrapper.eq("business_type", 1)
 .and(a -> a.eq("state", 9).or(b -> b.eq("state", 8).eq("bidding_sign", 1)))
 .or(i -> i.eq("business_type", 2).in("state", 2, 3));
        

 // ES native RestHighLevel syntax
 List<Integer> values = Arrays.asList(2, 3);
 BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
 boolQueryBuilder.must(QueryBuilders.termQuery("business_type", 1));
 boolQueryBuilder.must(QueryBuilders.boolQuery()); boolQueryBuilder.must(QueryBuilders.termQuery()
                 .must(QueryBuilders.termQuery("state", 9))
                      .should(QueryBuilders.boolQuery().must(QueryBuilders.termQuery("state", 8))
                              .must(QueryBuilders.termQuery("bidding_sign", 1))));
 boolQueryBuilder.should(QueryBuilders.boolQuery().must(QueryBuilders.termQuery("business_type", 2))
               	 .must(QueryBuilders.termsQuery("state", values)));

The maximum depth of the tree in the above example is only 2. If the depth exceeds 2, the ES native syntax will be difficult to understand, let alone code correctly, and even if you are an ES veteran, you will easily fall into the hole.

Although our syntax and MP a hair, but in order to prevent a lot of white people will not use or have not touched MP, I try to use each type to write a demo for the Lord's reference:

    /**
     * Scenario 1: Use of nested and 
     */
    @Test
    public void testNestedAnd() {
        // The following query condition is equivalent to select * from document where star_num in (1, 2) and (title = 'old man' or title = 'push*') in MySQL
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.in(Document::getStarNum, 1, 2)
               .and(w -> w.eq(Document::getTitle, "Old Man").or().eq(Document::getTitle, "push*"));
        List<Document> documents = documentMapper.selectList(wrapper);
    }

    /**
     * Scenario 2: The use of spliceand 
     */
    @Test
    public void testAnd(){
        // The following query condition is equivalent to the MySQL select * from document where title = 'Old man' and content like 'push*'
        // Splicing and is special, because the most used scenarios, so the default between the conditions and conditions is splicing and, so you can directly omit, this and MP is the same
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.eq(Document::getTitle, "Old Man")
               .match(Document::getContent, "push*");
        List<Document> documents = documentMapper.selectList(wrapper);
    }
    
    /**
     * Scenario 2: The use of nested or 
     */
    @Test
    public void testNestedOr() {
        // The following query condition is equivalent to select * from document where star_num = 1 or (title = 'old man' and creator = 'bad old man') in MySQL
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.eq(Document::getStarNum, 1)
                .or(i -> i.eq(Document::getTitle, "Old Man").eq(Document::getCreator, "Bad Old Man"));
        List<Document> documents = documentMapper.selectList(wrapper);
    }

    /**
     * Scenario 3: The use of spliceor 
     */
    @Test
    public void testOr() {
        // The following query condition is equivalent to select * from document where title = 'old man' or title = 'demented man' in MySQL
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.eq(Document::getTitle, "old man")
                .or()
                .eq(Document::getTitle, "demented man");
        List<Document> documents = documentMapper.selectList(wrapper);
    }

    /**
     * Scenario 4: Use of nested filters is actually the same as scenario 1, except that the conditions in the filter do not calculate the score and cannot be sorted by score, which gives a slightly higher query performance
     */
    @Test
    public void testNestedFilter() {
        // The following query condition is equivalent to MySQL select * from document where star_num in (1, 2) and (title = 'old man' or title = 'push*')
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.in(Document::getStarNum, 1, 2)
                .filter(w -> w.eq(Document::getTitle, "Old Man").or().eq(Document::getTitle, "push*"));
        List<Document> documents = documentMapper.selectList(wrapper);
    }
    
    /**
     * Scenario 5: the use of splice filter filter in the conditions do not calculate the score, can not be sorted by score, the query performance is slightly higher
     */
    @Test
    public void testFilter() {
        // The following query condition is equivalent to select * from document where title = 'old man' in MySQL
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.filter().eq(Document::getTitle, "Old Man");
        List<Document> documents = documentMapper.selectList(wrapper);
    }
    
    /**
     * Scenario 6: Use of nested mustNot 
     */
    @Test
    public void testNestedNot() {
        // The following query condition is equivalent to MySQL select * from document where title = 'old man' and (size ! = 18 and age ! = 18)
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.eq(Document::getTitle, "Old Man")
               .not(i->i.eq(size,18).eq(age,18));
        List<Document> documents = documentMapper.selectList(wrapper);
    }
    
    /**
     * Scenario 6: The use of splice not()
     */
    @Test
    public void testNot() {
        // The following query condition is equivalent to MySQL select * from document where title = 'Old man' and size ! = 18
        LambdaEsQueryWrapper<Document> wrapper = new LambdaEsQueryWrapper<>();
        wrapper.eq(Document::getTitle, "Old Man")
               .not()
               .eq(size,18);
        List<Document> documents = documentMapper.selectList(wrapper);
    }

The above are some high-frequency use scenarios in the case, just need to understand the use, and then the future no matter how complex the query conditions, nesting level how "deep", no matter how much you ES level of dish, you can be as easy as the ES experts to hold, the family together with the public screen buckle on the cow *!