String sorting with elasticsearch in symfony

11/03/2016 - ELASTICSEARCH, SYMFONY

There will be times we want to be able to use full-text search on a string field and also order by using it. Full-text search would need us to index the field as analyzed but this would cause problem when sorting by same field. We would need not_analyzed index for sorting correctly. Example below shows us how to apply two indexes on a same field. This example depends on FOSElasticaBundle. For more information, read String Sorting and Multifields page.


Dummy data


Just to let you know, there are more than 10 records in database.


mysql> SELECT * FROM post LIMIT 10;
+----+-------+-------------+--------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at          |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
|  1 | tit   | ion         | ed     | 2010 |  2.50 |            1 | 2016-03-11 14:08:29 |
|  2 | alo   | de          | a edie | 2010 |  4.00 |            0 | 2016-03-11 14:08:29 |
|  3 | ula   | tion        | ed jon | 2010 |  1.00 |            1 | 2016-03-11 14:08:29 |
|  4 | des   | ion         | eddy   | 2005 |  2.50 |            0 | 2016-03-11 14:08:29 |
|  5 | alo   | de          | ed     | 2015 |  1.00 |            0 | 2016-03-11 14:08:29 |
|  6 | tit 1 | des         | eddy   | 2000 |  4.00 |            1 | 2016-03-11 14:08:29 |
|  7 | des   | john        | ed jon | 2015 |  4.00 |            0 | 2016-03-11 14:08:29 |
|  8 | des   | des         | edie   | 2005 |  5.55 |            1 | 2016-03-11 14:08:29 |
|  9 | 2 tit | de 1        | ed jon | 2015 |  5.55 |            1 | 2016-03-11 14:08:29 |
| 10 | tit   | des         | jo     | 2005 |  5.55 |            1 | 2016-03-11 14:08:29 |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
10 rows in set (0.00 sec)

Analyzed


Results don't match each other. We can do full-text search on title field but cannot order by it.


Config


fos_elastica:
    ....
    mappings:
        title:
            type: string
    ....

DB: Query and Result


mysql> SELECT * FROM post ORDER BY title ASC, id ASC LIMIT 2 OFFSET 0;
+----+-------+-------------+--------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at          |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
|  9 | 2 tit | de 1        | ed jon | 2015 |  5.55 |            1 | 2016-03-11 14:08:29 |
| 23 | 2 tit | io des      | ed jon | 2010 |  3.99 |            0 | 2016-03-11 14:08:29 |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
2 rows in set (0.02 sec)

ES: Query and Result


# Query
{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": []
        }
      ]
    }
  },
  "sort": [
    {
      "title": {
        "order": "asc"
      }
    }
  ],
  "from": 0,
  "size": "2"
}

# Result
1) "id": 59 - "title": "tit 1"
2) "id": 78 - "title": "tit 1"

Not Analyzed


Results now match each other. We can order by title field but have just lost the full-text search capabilities so we still have a problem. We can only do an exact value search.


Config


fos_elastica:
    ....
    mappings:
        title:
            type: string
            index: not_analyzed
    ....

DB: Query and Result


mysql> SELECT * FROM post ORDER BY title ASC, id ASC LIMIT 2 OFFSET 0;
+----+-------+-------------+--------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at          |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
|  9 | 2 tit | de 1        | ed jon | 2015 |  5.55 |            1 | 2016-03-11 14:08:29 |
| 23 | 2 tit | io des      | ed jon | 2010 |  3.99 |            0 | 2016-03-11 14:08:29 |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
2 rows in set (0.02 sec)

ES: Query and Result


# Query
{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": []
        }
      ]
    }
  },
  "sort": [
    {
      "title": {
        "order": "asc"
      }
    }
  ],
  "from": 0,
  "size": "2"
}

# Result
1) "id": 9 - "title": "2 tit"
2) "id": 23 - "title": "2 tit"

Analyzed plus Not Analyzed


Results now match each other. We can order by title field and do a full-text search.


Config


fos_elastica:
    ....
    mappings:
        title:
            type: string
            analyzer: english
            fields:
                raw:
                    type: string
                    index: not_analyzed
    ....

DB: Query and Result


mysql> SELECT * FROM post ORDER BY title ASC, id ASC LIMIT 2 OFFSET 0;
+----+-------+-------------+--------+------+-------+--------------+---------------------+
| id | title | description | author | year | price | is_published | created_at          |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
|  9 | 2 tit | de 1        | ed jon | 2015 |  5.55 |            1 | 2016-03-11 14:08:29 |
| 23 | 2 tit | io des      | ed jon | 2010 |  3.99 |            0 | 2016-03-11 14:08:29 |
+----+-------+-------------+--------+------+-------+--------------+---------------------+
2 rows in set (0.02 sec)

ES: Query and Result


Make sure to use title key in the query section if you search for a specific keyword and title.raw in sorting.


# Query
{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": []
        }
      ]
    }
  },
  "sort": [
    {
      "title.raw": {
        "order": "asc"
      }
    }
  ],
  "from": 0,
  "size": "2"
}

# Result
1) "id": 9 - "title": "2 tit"
2) "id": 23 - "title": "2 tit"