Жесть (а кому щас легко?)

gistfile1.sql

-- getTotal --

SELECT 
	tfv.*,
	COUNT(*) FROM `#__xfx_brokers` AS t 
LEFT JOIN (
	SELECT fv.`broker_id`, 
	MAX(IF(fv.`field_id` = 1, fv.`field_value`, NULL)) AS field_1, 
	MAX(IF(fv.`field_id` = 3, fv.`field_value`, NULL)) AS field_3, 
	MAX(IF(fv.`field_id` = 5, fv.`field_value`, NULL)) AS field_5 
	FROM `#__xfx_field_values` AS fv 
	GROUP BY fv.`broker_id` 
	ORDER BY fv.`broker_id`
) AS tfv 
ON tfv.`broker_id` = t.`record_id` 
WHERE `company_type` = 1 
AND `published` = 1 
AND tfv.`field_1` = 1 
AND tfv.`field_3` = 1 
AND tfv.`field_5` = 10
ORDER BY `company_name` ASC ;

-- get Rows --

SELECT 
	tfv.*,
	t.*,
	tmp1.count_feedbacks AS feedbacks,
	tmp2.count_comments AS comments,
	tmp3.count_items AS news 
FROM `#__xfx_brokers` AS t 
LEFT JOIN (
	SELECT 
		fv.`broker_id`, 
		MAX(IF(fv.`field_id` = 1, fv.`field_value`, NULL)) AS field_1, 
		MAX(IF(fv.`field_id` = 3, fv.`field_value`, NULL)) AS field_3, 
		MAX(IF(fv.`field_id` = 5, fv.`field_value`, NULL)) AS field_5 
	FROM `#__xfx_field_values` AS fv 
	GROUP BY fv.`broker_id` 
	ORDER BY fv.`broker_id`
) AS tfv 
ON tfv.`broker_id` = t.`record_id` 
LEFT JOIN (
	SELECT 
		f.`broker_id`, 
		COUNT(f.`record_id`) AS count_feedbacks 
	FROM `#__xfx_feedbacks` AS f 
	WHERE f.`published` = 1 
	GROUP BY f.`broker_id`
) AS tmp1 
ON tmp1.`broker_id` = t.`record_id` 
LEFT JOIN (
	SELECT 
		c.`object_id`, 
		COUNT(c.`object_id`) AS count_comments 
	FROM `#__jcomments` AS c 
	WHERE c.`object_group` = 'com_k2' 
	AND c.`published` = 1 
	GROUP BY c.`object_id`
) AS tmp2 
ON tmp2.`object_id` = t.`link_info` 
LEFT JOIN (
	SELECT 
		i.`catid`, 
		COUNT(i.`id`) AS count_items 
	FROM `#__k2_items` AS i 
	WHERE i.`published` = 1 
	GROUP BY i.`catid`
) AS tmp3 
ON tmp3.`catid` = t.`link_news` 
WHERE `company_type` = 1 
AND `published` = 1 
AND tfv.`field_1` = 1 
AND tfv.`field_3` = 1 
AND tfv.`field_5` = 10
ORDER BY `company_name` ASC 
LIMIT 0, 20 ;