SELECT SQL_CALC_FOUND_ROWS 1+1, IF(subproducts_rel.product_id AND media2.path != '', `media2`.`path`, media.path) AS image_path, IF(subproducts_rel.product_id AND media2.name != '', CONCAT(media2.name, media2.extension), CONCAT(media.name, media.extension)) AS image_name, IF(subproducts_rel.product_id AND media2.alt_text != '', `media2`.`alt_text`, media.alt_text) AS alt_text, `products`.`type`, `products`.`product_id`, `products`.`banner_text`, `products`.`order_min_quantity`, `products`.`enabled`, `products`.`order_per_item`, `products`.`require_quotation`, `products`.`sale_price`, `products`.`sale_price_total`, `products`.`sale_price_discount_btw6`, `products`.`sale_price_discount_btw21`, COALESCE(NULLIF(products_data.sale_price_discount, 0), products.sale_price_discount) AS sale_price_discount, COALESCE(products_data.sale_price_discount_start, products.sale_price_discount_start) AS sale_price_discount_start, COALESCE(products_data.sale_price_discount_end, products.sale_price_discount_end) AS sale_price_discount_end, (SELECT GROUP_CONCAT(product_themes.name) FROM product_themes INNER JOIN product_themes_rel ON product_themes.theme_id = product_themes_rel.theme_id WHERE product_themes_rel.product_id = products.product_id) AS theme, IF(products.packet_nr, products.packet_nr, products.nr) AS product_nr, `days` AS `deliveryDays`, `products`.`slug`, `products`.`meta_description`, `products`.`meta_keywords`, COALESCE(products_data.description, products.description) AS description, `products`.`video_url`, `products`.`explanation`, `product_categories_rel_tb`.`category_id`, `categories`.`name` AS `category`, IF ((products.sale_price_discount>0 AND products.sale_price_discount_start <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND products.sale_price_discount_end >= DATE_FORMAT(NOW(), '%Y-%m-%d')), `products`.`sale_price_discount`, products.sale_price) AS price, IF ((products.sale_price_discount>0 AND products.sale_price_discount_start <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND products.sale_price_discount_end >= DATE_FORMAT(NOW(), '%Y-%m-%d')), `products`.`sale_price_discount`, products.sale_price_total) AS price_total, IF ((products.sale_price_discount>0 AND products.sale_price_discount_start <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND products.sale_price_discount_end >= DATE_FORMAT(NOW(), '%Y-%m-%d')), 1, 0) AS discountPrice, `products`.`employee_can_buy`, `products`.`employer_can_buy`, COALESCE(products_data.title, products.title) AS title, COALESCE(products_data.detail_title, products.detail_title) AS detail_title, COALESCE(products_data.is_discontinued, products.is_discontinued) AS is_discontinued, COALESCE(products_data.stock, products.stock) AS stock, COALESCE(products_data.short_description, products.short_description) AS short_description, COALESCE(products_data.keywords, products.keywords) AS keywords, COALESCE(products_data.brand, products.brand) AS brand, IF ((products.employer_points_discount>0
                        AND products.employer_points_discount_start <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                        AND products.employer_points_discount_end >= DATE_FORMAT(NOW(), '%Y-%m-%d')), `products`.`employer_points_discount`, IF(products_data.employer_points, `products_data`.`employer_points`, products.employer_points)) AS employer_price_points, IF ((products.employee_points_discount>0
                        AND products.employee_points_discount_start <= DATE_FORMAT(NOW(), '%Y-%m-%d')
                        AND products.employee_points_discount_end >= DATE_FORMAT(NOW(), '%Y-%m-%d')), `products`.`employee_points_discount`, IF(products_data.employee_points, `products_data`.`employee_points`, products.employee_points)) AS employee_price_points
FROM `products`
LEFT JOIN `product_categories_rel` `product_categories_rel_tb` ON `product_categories_rel_tb`.`product_id`=`products`.`product_id`
LEFT JOIN `categories` ON `categories`.`category_id` = `product_categories_rel_tb`.`category_id` AND `categories`.`site_id` = 1
LEFT JOIN `media` ON `media`.`category`='product-images' AND `media`.`owner`=`products`.`product_id` AND `media`.`orders` = 0
LEFT JOIN `media_subproducts_orders` ON `products`.`product_id` = `media_subproducts_orders`.`configurable_product_id` AND `media_subproducts_orders`.`orders` = 0
LEFT JOIN `subproducts_rel` ON `subproducts_rel`.`product_id` = `media_subproducts_orders`.`configurable_product_id`
LEFT JOIN `media` AS `media2` ON `media2`.`category`='product-images' AND `media2`.`owner`=`media_subproducts_orders`.`product_id` AND `media2`.`orders` = 0
LEFT JOIN `categories` AS `category_parent` ON `category_parent`.`category_id` = `categories`.`parent_id`
LEFT JOIN `categories` AS `category_parent_parent` ON `category_parent_parent`.`category_id` = `category_parent`.`parent_id`
INNER JOIN `product_sites_rel` ON `products`.`product_id`=`product_sites_rel`.`product_id` AND `product_sites_rel`.`site_id`=1
LEFT JOIN `deliveries` ON `deliveries`.`delivery_id` = `products`.`delivery_days`
LEFT JOIN `products_data` ON `products_data`.`item_id` = `products`.`product_id` and `products_data`.`site_id` = '1'
WHERE (`product_categories_rel_tb`.`category_id` IN (165,302,334) OR `categories`.`parent_id` IN (165,302,334) OR `category_parent`.`parent_id` IN (165,302,334) OR `category_parent_parent`.`parent_id` IN (165,302,334))
AND `products`.`enabled` = 1
AND `products`.`product_id` NOT IN(SELECT product_id FROM customer_product)
AND (`products`.`disable_at` IS NULL OR products.disable_at > CURDATE())
AND `products`.`deleted` = 0
GROUP BY `products`.`product_id`
ORDER BY `title` desc
 LIMIT 30