SELECT p.id, pt.name, i.image, pt.slug,
		    COUNT( tmp.id ) as cnt,
		    COUNT( prods.id ) as p_cnt
	     FROM pages p
	     INNER JOIN pages_text pt ON p.id = pt.page_id AND pt.language_id = 1
	     LEFT JOIN images i ON p.id = i.page_id
	     LEFT JOIN products prods ON p.id = prods.category AND prods.status != 'deleted' 
	     LEFT JOIN ( SELECT p.id, p.relation_id FROM pages p INNER JOIN products pr ON ( pr.category = p.id  OR pr.subcategory = p.id OR pr.subsubcategory = p.id) WHERE p.status != 'deleted' ) as tmp ON p.id = tmp.relation_id
	     WHERE p.status != 'deleted'
	     AND p.category = 'category'
	     AND p.relation_id IN (  )
	     GROUP BY p.id
	     HAVING p_cnt > 0
	     ORDER BY p.order_id
	     
	    

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')
	     GROUP BY p.id
	     HAVING p_cnt > 0
	     ORDER BY p.order_id' at line 11