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.