foo.sql

SELECT 
                *,
                (
                CASE
                    WHEN title LIKE "%keyword%" AND crdate > 1400000090 THEN 1
                    WHEN textmodules LIKE "%keyword%" AND crdate > 1400000090 THEN 2
                    WHEN title LIKE "%keyword%" THEN 3
                    WHEN textmodules LIKE "%keyword%" THEN 4
                    ELSE 10
                END
                ) as relevance
            , ACOS(
                                SIN(RADIANS(51.9074)) * SIN(RADIANS(latitude))
                                + COS(RADIANS(51.9074)) * COS(RADIANS(latitude))
                                * COS(RADIANS(8.3767) - RADIANS(longitude))
                            ) * 6380 AS distance
FROM tx_mfcompana_domain_model_offer
HAVING (pid IN (123)) AND (
                title LIKE "%keyword%"
                OR textmodules LIKE "%keyword%"
                OR keywords LIKE "%keyword%"
            ) AND (distance < 1 OR (job_zip LIKE "Gütersloh%" OR job_zip = "Gütersloh" OR job_city = "Gütersloh" OR job_city LIKE "%Gütersloh%" OR job_zip IN (33330,33332,33334,33335))) AND tx_mfcompana_domain_model_offer.deleted=0 AND tx_mfcompana_domain_model_offer.t3ver_state<=0 AND tx_mfcompana_domain_model_offer.pid<>-1 AND tx_mfcompana_domain_model_offer.hidden=0 AND tx_mfcompana_domain_model_offer.starttime<=1485338640 AND (tx_mfcompana_domain_model_offer.endtime=0 OR tx_mfcompana_domain_model_offer.endtime>1485338640)
ORDER BY relevance ASC
LIMIT 0,10