Residential Listing

SELECT SQL_CALC_FOUND_ROWS uqsbx_posts.ID FROM uqsbx_posts LEFT JOIN uqsbx_term_relationships ON (uqsbx_posts.ID = uqsbx_term_relationships.object_id) LEFT JOIN uqsbx_term_relationships AS tt1 ON (uqsbx_posts.ID = tt1.object_id) WHERE 1=1 AND ( uqsbx_term_relationships.term_taxonomy_id IN (3,862,867,869,870,873,995) AND tt1.term_taxonomy_id IN (981) AND uqsbx_posts.ID NOT IN ( SELECT object_id FROM uqsbx_term_relationships WHERE term_taxonomy_id IN (35) ) ) AND uqsbx_posts.post_type = 'property' AND ((uqsbx_posts.post_status = 'publish')) GROUP BY uqsbx_posts.ID ORDER BY CAST( IF( (SELECT COUNT(ord_ot.term_id) FROM uqsbx_terms as ord_ot JOIN uqsbx_term_relationships as ord_otr ON ord_otr.term_taxonomy_id = ord_ot.term_id JOIN uqsbx_term_taxonomy as ord_ott ON ord_ott.term_id=ord_ot.term_id WHERE ord_ot.slug IN('sale-agreed','under-offer') AND ord_otr.object_id=uqsbx_posts.ID AND ord_ott.taxonomy='property-status' )>0, 999999999999 , IF( (SELECT COUNT(ord_ot.term_id) FROM uqsbx_terms as ord_ot JOIN uqsbx_term_relationships as ord_otr ON ord_otr.term_taxonomy_id = ord_ot.term_id JOIN uqsbx_term_taxonomy as ord_ott ON ord_ott.term_id=ord_ot.term_id WHERE ord_ot.slug IN('sold') AND ord_otr.object_id=uqsbx_posts.ID AND ord_ott.taxonomy='property-status' )>0, 999999999998 , IF( (SELECT COUNT(ot.term_id) FROM uqsbx_terms as ot JOIN uqsbx_term_relationships as otr ON otr.term_taxonomy_id = ot.term_id JOIN uqsbx_term_taxonomy as ott ON ott.term_id=ot.term_id WHERE otr.object_id=uqsbx_posts.ID AND ott.taxonomy='property-location' LIMIT 1)>0, (SELECT CASE WHEN ot.slug='cork' THEN 2 WHEN ot.slug='northern-ireland' THEN 3 WHEN ot.slug IS NULL THEN 4 WHEN ot.slug='' THEN 4 ELSE 1 END AS order_by_name FROM uqsbx_terms as ot JOIN uqsbx_term_relationships as otr ON otr.term_taxonomy_id = ot.term_id JOIN uqsbx_term_taxonomy as ott ON ott.term_id=ot.term_id WHERE otr.object_id=uqsbx_posts.ID AND ott.taxonomy='property-location' GROUP BY otr.object_id ORDER BY order_by_name LIMIT 1 ), 5) ) ) AS SIGNED) ASC , CAST( IF((SELECT COUNT(property_ordering.post_id) FROM uqsbx_postmeta as property_ordering WHERE property_ordering.meta_key='property_ordering_category_3' AND property_ordering.post_id=uqsbx_posts.ID LIMIT 1)>0, (SELECT property_ordering.meta_value FROM uqsbx_postmeta as property_ordering WHERE property_ordering.meta_key='property_ordering_category_3' AND property_ordering.post_id=uqsbx_posts.ID LIMIT 1), 999999) AS SIGNED) ASC LIMIT 0, 12