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`,
    `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`, GROUP_CONCAT(DISTINCT c.category_id SEPARATOR ',') 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"
LEFT 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"
LEFT JOIN `zoomall_products_categories_links` AS `c` ON c.product_id = zoomall_products.id
LEFT JOIN `zoomall_products_items_attributes` AS `ia` ON ia.item_id = i.id
LEFT JOIN `zoomall_attributes` AS `a` ON ia.attribute_id = a.id
WHERE `c`.`category_id` IN ('1', '7', '261', '465', '479', '106', '108', '115', '105', '129', '478', '480', '474', '145', '501', '500', '38', '57', '225', '258', '464', '499', '120', '466', '467', '235', '242', '504', '285', '469', '355', '400', '468', '284', '260', '472', '473', '118', '119', '233', '83', '101', '107', '319', '103', '109', '110', '393', '92', '93', '144', '94', '251', '471', '392', '136', '137', '138', '139', '140', '141', '142', '366', '296', '297', '298', '389', '143', '223', '402', '505', '96', '368', '431', '395', '432', '117', '215', '391', '475', '476', '299', '333') AND `zoomall_products`.`status` = 'active' AND (i.price_new > 0 OR i.price_old > 0)
GROUP BY `zoomall_products`.`id`
HAVING MIN(price_new) > 0 AND COUNT(c.id) > 0
ORDER BY `t`.`title` DESC
LIMIT 20