SELECT
    `zoomall_products`.`id` AS `id`,
    `zoomall_products`.`is_gift` AS `is_gift`,
    `zoomall_products`.`flag` AS `flag`,
    `zoomall_products`.`created_at` AS `created_at`,
    COUNT(zoomall_products.id) OVER() AS `total_count`,
    (
        MATCH(
            t.description,
            t.categories_text,
            t.meta_keywords,
            t.title,
            t.short_title
        ) AGAINST('*аквариуми* *риби*' IN BOOLEAN MODE)
    ) AS `relevance`,
    `t`.`title` AS `title`,
    `t`.`short_title` AS `short_title`,
    `t`.`slug` AS `slug`,
    `b`.`id` AS `brand_id`,
    `b`.`title` AS `brand_title`,
    `b`.`slug` AS `brand_slug`,
    `im`.`filename` AS `image`,
    MAX(price_new) AS `max_price`,
    MIN(price_new) AS `min_price`,
    `i`.`price_old` AS `price_old`,
    COALESCE(AVG(rating), 0) AS `avg_rating`,
    `c`.`category_id` AS `categories`
FROM
    `zoomall_products`
    INNER JOIN `zoomall_products_translations` AS `t` ON zoomall_products.id = t.product_id
    AND t.language = 'bg'
    LEFT JOIN `zoomall_brands` AS `b` ON zoomall_products.brand_id = b.id
    AND b.status = "active"
    LEFT JOIN `zoomall_products_images` AS `im` ON zoomall_products.id = im.product_id
    AND im.is_main = 1
    AND im.status = "active"
    INNER JOIN `zoomall_products_items` AS `i` ON zoomall_products.id = i.product_id
    and i.status = "active"
    LEFT JOIN `zoomall_products_reviews` AS `r` ON zoomall_products.id = r.product_id
    AND r.status = "active"
    INNER JOIN `zoomall_products_categories_links` AS `c` ON c.product_id = zoomall_products.id
WHERE
    `zoomall_products`.`status` = 'active'
    AND (
        i.price_new > 0
        OR i.price_old > 0
    )
    AND MATCH(
        t.description,
        t.categories_text,
        t.meta_keywords,
        t.title,
        t.short_title
    ) AGAINST('*аквариуми* *риби*' IN BOOLEAN MODE)
    OR MATCH(
        t.description,
        t.categories_text,
        t.meta_keywords,
        t.title,
        t.short_title
    ) AGAINST('*akvariumi* *ribi*' IN BOOLEAN MODE)
GROUP BY
    `zoomall_products`.`id`
HAVING
    MIN(price_new) > 0
    AND COUNT(c.id) > 0
ORDER BY
    `relevance` DESC
LIMIT
    '20'