1

I need to divide my search result into two parts. 1 with those goods in which the number> 0 sort them by price and withdraw first. 2 products whose quantity = 0 sort by price and display at the end, after those products that are in stock. The main thing is that in the first group of goods (whose quantity> 0) there were no goods from the second group (whose quantity = 0) What unfortunately happens when I sort by two conditions

Use PHP 7.1 and Elastic Search 6.6.0

Small example, there is a table of goods

id | site_price | count
 1 | 10         |  0
 2 | 5          |  5
 3 | 15         |  2
 4 | 20         | 10
 5 | 15         |  0

I need to sort first by quantity, and then by price (without losing the first sorting). First sort: ('count'=>'desc'). Second sort: ('site_price'=>'asc'). Should get this result:

id | site_price | count
 2 |  5         | 10
 3 | 15         |  5
 4 | 20         |  2
 1 | 10         |  0
 5 | 15         |  0


$this->params['body'] = array(
    'from' => ($filters['page'] - 1) * 15,
    'size' => 15,
    'query' => array(
        'bool' => array(
            'must' => array(
                "query_string" => array(
                    'query' => "*" . $filters['text'] . "*",
                )
            ),
        )
    ),
    'sort' => array(
        array("shops_count" => "desc"),
        array("site_price" => "asc")
    )
);
$result = $this->client->search($this->params);
Nikolay Vasiliev
  • 5,656
  • 22
  • 31
Max Vasyliev
  • 23
  • 1
  • 5
  • May you please provide a few example documents and expected output in correct order? – Nikolay Vasiliev Apr 07 '19 at 09:49
  • small example, there is a table of goods id | site_price | count 1 | 10 | 0 2 | 5 |5 3 | 15 |2 4 | 20 |10 5 | 17 |0 I need to sort first by quantity, and then by price (without losing the first sorting) First sort ('count'=>'desc') Second sort ('site_price'=>'asc' should get this result id | site_price | count 2 | 5 |10 3 | 15 |5 4 | 20 |2 1 | 10 |0 5 | 17 |0 – Max Vasyliev Apr 08 '19 at 09:01
  • I am afraid formatting gets lost in comments, maybe you could add this example as code in the original post? Thank you. – Nikolay Vasiliev Apr 08 '19 at 09:35
  • I added the table to the source code. Thank you in advance – Max Vasyliev Apr 08 '19 at 11:00
  • Thank you. From the example you provided it should work as you want it: sorting by `count: desc, price: asc` will give the order of IDs: 2, 3, 4, 1, 5. It means that ES `sort` is not the problem, the problem is likely somewhere else. – Nikolay Vasiliev Apr 08 '19 at 17:30
  • I rather did not give the correct data for example. If we have two products with the same price. But one will be available and the second will not. Then this kind of sorting will put them nearby. And it is necessary for me that the goods with the quantity were first and sorted by price, and the goods without availability were at the end, also sorted. If we change the price of the product with id 5 for 15, then as a result we will get this order id 2,3,5,4,1 – Max Vasyliev Apr 09 '19 at 05:32
  • I am deleting my old answer as irrelevant, and writing a new one. – Nikolay Vasiliev Apr 11 '19 at 17:16

2 Answers2

0

@Nikolay, thanks for the help. Unfortunately, this did not help. I tried rewrote the query - but the result is the same. Here is an example: removed too much left only search and sorting

enter code here
$this->params['body'] = array(
        'from' => ($filters['page'] - 1) * 15,
        'size' => 15,
        'query' => array(
            'bool' => array(
                'must' => array(
                    "query_string" => array(
                        'query' => "*" . $filters['text'] . "*",
                    )
                ),
            )
        ),
        'sort' => array(
            array("shops_count" => "desc"),
            array("site_price" => "asc")
        )
    );
        $result = $this->client->search($this->params);
Max Vasyliev
  • 23
  • 1
  • 5
0

It looks like that you want to achieve behavior similar to UNION in SQL, since you first want to split the result set into 2 groups, sort each group and then attach one group after another.

There are a few ways to do it.

1) By doing 2 queries

Like in this answer, it is suggested to do 2 queries:

POST /orders/_search
{
    "query": {
        "range": {
            "count": {
                "gt": 0
            }
        }
    },
    "sort" : [
        {"site_price": "asc"},
    ]
}

POST /orders/_search
{
    "query": {
        "range": {
            "count": {
                "gte": 0,
                "lte": 0
            }
        }
    },
    "sort" : [
        {"site_price": "asc"},
    ]
}

And then joining them on the client side. There is also a way to do it completely on the Elasticsearch side.

2) By using script sorting

We can use script based sorting and sort first on the availability (count > 0), then by price:

POST /orders/_search
{
    "sort" : [
        {
            "_script" : {
                "type" : "number",
                "script" : {
                    "lang": "painless",
                    "source": "if (doc['count'].value > 0) { 1 } else { 0 } "
                },
                "order" : "desc"
            }
        },
        {"site_price": "asc"}
    ]
}

However, scripting always has performance overhead. Solution #1 is more robust, although it performs 2 queries.

Here is another solution that uses single query and does not use expensive scripting.

3) Adding new field - for sorting

If we add a special field, "available", we will not need to use script sorting.

The documents might look like this:

doc1 = {
    "id": 1,
    "site_price": 10,
    "count": 0,
    "available": 0
}
doc2 = {
    "id": 2,
    "site_price": 5,
    "count": 5,
    "available": 1
}

Then the sorting will look like this:

POST /orders/_search
{
    "sort" : [
        {"available": "desc"},
        {"site_price": "asc"}
    ]
}

This is a common pattern called denormalization which proves useful when tuning for best performance.

Hope that helps!

Nikolay Vasiliev
  • 5,656
  • 22
  • 31