select distinct `main_category_id`, `min_value`, `price_by_min_value`, `pin`, `only_free`, `sell_type`, `only_contact`, `price_by_option`, `tbl_shop`.`pid`, `market_id`, `sku`, `preview_picture`, `tbl_shop`.`price`, `discount`, `expire_discount_date`, `count`, `tbl_shop`.`type`, `score`, `brand`, `order_count`, `view_count`, `comment_count`, `total_price`, `tbl_shop`.`priority`, `home`, `offer`, `tbl_shop`.`rate`, `weight`, `alternative_link`, `shipping_is_free`, `tbl_shop`.`status`, `date_added`, `modified_date`, `tbl_shop_language`.`title`, `tbl_shop_language`.`subtitle`, `tbl_shop_language`.`slug`, `tbl_shop_language`.`description`, `tbl_shop_language`.`keywords`, `min_unstock_count`, `min_value`, if(count - if(min_unstock_count is null, 0, min_unstock_count) > 0, 1, 0) as stock, if(price > 100, `price`, if(price > 0 and price < 101, (select price from tbl_price_list where pid = tbl_shop.price), price)) as price, if(default_option_list_id is null, 0, (select value from tbl_shop_option_list where pid = tbl_shop.default_option_list_id)) as default_price, case when trim(tbl_shop_language.title) = 'بهترین مدل مانتو' then 3 when trim(tbl_shop_language.title) like 'بهترین مدل مانتو%' then 2 when trim(tbl_shop_language.title) like '%بهترین مدل مانتو' then 2 when trim(tbl_shop_language.title) like '%بهترین مدل مانتو%' then 1 else 0 end as relevance
from `tbl_shop`
join `tbl_shop_language` on `tbl_shop_language`.`shop_id` = `tbl_shop`.`pid`
join `tbl_shop_category` on `tbl_shop_category`.`product_id` = `tbl_shop`.`pid`
right join `tbl_market` on `tbl_market`.`pid` = `tbl_shop`.`market_id`
where `tbl_shop_language`.`language_id` = 'persian'
and `tbl_shop`.`type` <> 3
and `tbl_shop`.`status` = 1
and `unseen` = 0
and   (
trim(tbl_shop_language.title) like '%بهترین مدل مانتو%' escape '!'
or  trim(tbl_shop_language.search_tag) like '%بهترین مدل مانتو%' escape '!'
 )
and `deleted_time` is null
and `category_id` = '1786'
and `tbl_market`.`state` = 1
order by `stock` desc, `date_added` desc, `count` desc, `relevance` desc