# Transform mysql query to elastic search
**URL:** https://discuss.elastic.co/t/transform-mysql-query-to-elastic-search/44010
**Category:** Elasticsearch
**Created:** [March 10, 2016, 10:36am UTC](https://discuss.elastic.co/t/transform-mysql-query-to-elastic-search/44010 "2016-03-10T10:36:28Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@lasith\_Gunawardana](https://discuss.elastic.co/u/lasith_Gunawardana)
#### Post date: [March 10, 2016, 10:36am UTC](https://discuss.elastic.co/t/transform-mysql-query-to-elastic-search/44010/1 "2016-03-10T10:36:28Z")
Hi I'm new to elastic search and looking to transform this mysql query to elastic search query
select title, id, slug, is\_cinema, release\_date
from media
where media.is\_active = 1
AND media\_type\_id IN (1,3)
and CASE media.media\_type\_id WHEN 1 THEN is\_home = 1 ELSE is\_home = 0 END
and MATCH(title) AGAINST("_foo bar_" IN BOOLEAN MODE)
ORDER by is\_cinema DESC, CASE WHEN title LIKE "foo bar%" THEN 0 ELSE 1 END ASC, CASE media.media\_type\_id WHEN 3 THEN 0 when 1 then 1 ELSE 3 end ASC, release\_date DESC limit 10;
I've done some search queries and got the result. But i need to add above "CASE media.media\_type\_id WHEN 1 THEN is\_home = 1 ELSE is\_home = 0 END" and order by the exact match to the top "ORDER by is\_cinema DESC, CASE WHEN title LIKE "foo bar%" THEN 0 ELSE 1 END ASC, CASE media.media\_type\_id WHEN 3 THEN 0 when 1 then 1 ELSE 3 end ASC, release\_date DESC limit 10". I've used prefix but it doesn't work for me as well.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 11:09pm UTC](https://discuss.elastic.co/t/transform-mysql-query-to-elastic-search/44010/2 "2017-07-05T23:09:33Z")