SELECT
CASE WHEN LOWER(a.title) LIKE LOWER('%слово%')
THEN 5 ELSE 0 END +
CASE WHEN LOWER(a.title) LIKE LOWER('%поиска%')
THEN 5 ELSE 0 END AS relevance,
a.title AS title,
a.metadesc,
a.metakey,
a.created AS created,
a.language,
a.catid,
CONCAT(a.introtext,a.fulltext) AS text,
c.title AS section,
CASE WHEN CHAR_LENGTH(a.alias) != 0
THEN CONCAT_WS(':', a.id, a.alias)
ELSE a.id END as slug,
CASE WHEN CHAR_LENGTH(c.alias) != 0
THEN CONCAT_WS(':', c.id, c.alias)
ELSE c.id END as catslug,
'2' AS browsernav
FROM db_content AS a
INNER JOIN db_categories AS c
ON c.id=a.catid
WHERE ((LOWER(a.title) LIKE LOWER('%слово%')
OR LOWER(a.introtext) LIKE LOWER('%слово%')
OR LOWER(a.fulltext) LIKE LOWER('%слово%')
OR LOWER(a.metakey) LIKE LOWER('%слово%')
OR LOWER(a.metadesc) LIKE LOWER('%слово%'))
AND (LOWER(a.title) LIKE LOWER('%поиска%')
OR LOWER(a.introtext) LIKE LOWER('%поиска%')
OR LOWER(a.fulltext) LIKE LOWER('%поиска%')
OR LOWER(a.metakey) LIKE LOWER('%поиска%')
OR LOWER(a.metadesc) LIKE LOWER('%поиска%')))
AND a.state=1
AND c.published = 1
AND a.access IN (1,1,2,3,6)
AND c.access IN (1,1,2,3,6)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2021-06-08 16:24:40')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2021-06-08 16:24:40')
GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.language, a.catid, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id
ORDER BY relevance DESC, a.created DESC
LIMIT 20
