Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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"