SELECT SQL_CALC_FOUND_ROWS * FROM ( SELECT `product_tag`.`product_id` , `product`.* , `brand`.`name` AS `brand` , `i18n`.`name` AS `name_translated` , `slug`.`name` AS `slug` , ROUND(MIN(COALESCE( `pricelist_product_variant_price`.`price` + ( `product`.`vat` * `pricelist_product_variant_price`.`price` / 100), IF (`product_variant_price`.`price_discounted` > 0 , `product_variant_price`.`price_discounted` + ( `product`.`vat` * `product_variant_price`.`price_discounted` / 100) , `product_variant_price`.`price` + ( `product`.`vat` * `product_variant_price`.`price` / 100) ) )), 3) AS `min_price` , ROUND(MAX(COALESCE( `pricelist_product_variant_price`.`price` + ( `product`.`vat` * `pricelist_product_variant_price`.`price` / 100), `product_variant_price`.`price` + ( `product`.`vat` * `product_variant_price`.`price` / 100) )), 3) AS `max_price` , COUNT(*) AS `product_tag_count` FROM `product_tag` INNER JOIN `product` ON `product_tag`.`product_id` = `product`.`id` LEFT JOIN `i18n` ON `i18n`.`item_id` = `product`.`id` AND `i18n`.`model_id` = 1 AND `i18n`.`language` = :lang LEFT JOIN `product_brand` ON `product`.`id` = `product_brand`.`product_id` LEFT JOIN `brand` ON `brand`.`id` = `product_brand`.`brand_id` LEFT JOIN `slug` ON `slug`.`item_id` = `product`.`id` AND `slug`.`shop_id` = :shop_id AND `slug`.`model_id` = 1 AND `slug`.`language` = :lang -- LEFT JOIN `product_variant` ON `product`.`id` = `product_variant`.`product_id` LEFT JOIN `product_variant_price` ON `product_variant`.`id` = `product_variant_price`.`product_variant_id` LEFT JOIN `pricelist_product_variant_price` ON `pricelist_product_variant_price`.`product_variant_price_id` = `product_variant_price`.`id` WHERE `tag_id` IN ( SELECT `product_tag`.`tag_id` FROM `product` INNER JOIN `product_tag` ON `product`.`id` = `product_tag`.`product_id` WHERE `product`.`id` = :id ) AND `product_tag`.`product_id` <> :id AND `product`.`shop_id` = :shop_id AND `product`.`is_active` = 1 AND `product`.`is_visible` = 1 AND `product_variant`.`is_active` = 1 AND `product_variant`.`is_visible` = 1 AND `product_variant_price`.`min_quantity` = 0 GROUP BY `product_tag`.`product_id` ORDER BY `product_tag_count` DESC ) AS `derived`