SELECT DISTINCT(id), data, sort_name , email, phone, state_province, country
FROM (
( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, phone, street_address, city, ste.name, coy.name ) as data , sort_name, email, phone, street_address, city, ste.name as state_province, coy.name as country
FROM civicrm.civicrm_contact cc LEFT JOIN civicrm.civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 ) LEFT JOIN civicrm.civicrm_phone phe ON ( cc.id = phe.contact_id AND phe.is_primary = 1 ) LEFT JOIN civicrm.civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) LEFT JOIN civicrm.civicrm_state_province ste ON ( sts.state_province_id = ste.id ) LEFT JOIN civicrm.civicrm_country coy ON ( sts.country_id = coy.id )
WHERE ( email LIKE 'el' OR sort_name LIKE 'el' ) AND cc.is_deleted = 0
LIMIT 0, 10 )
UNION
( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, phone, street_address, city, ste.name, coy.name ) as data , sort_name, email, phone, street_address, city, ste.name as state_province, coy.name as country
FROM civicrm.civicrm_contact cc LEFT JOIN civicrm.civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 ) LEFT JOIN civicrm.civicrm_phone phe ON ( cc.id = phe.contact_id AND phe.is_primary = 1 ) LEFT JOIN civicrm.civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1) LEFT JOIN civicrm.civicrm_state_province ste ON ( sts.state_province_id = ste.id ) LEFT JOIN civicrm.civicrm_country coy ON ( sts.country_id = coy.id )
WHERE ( email LIKE '%el%' OR sort_name LIKE '%el%' ) AND cc.is_deleted = 0
ORDER BY sort_name
LIMIT 0, 10 )
) t
ORDER BY exactFirst, sort_name;
Starting Query
Finished Query
Results
stdClass Object
(
[id] => 219193
[data] => Hill, Dan :: daniel_hill54@hotmail.com
[sort_name] => Hill, Dan
[email] => daniel_hill54@hotmail.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 202212
[data] => , :: jebell98271@frontier.com
[sort_name] => ,
[email] => jebell98271@frontier.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 202219
[data] => , :: jphelps2011@gmail.com
[sort_name] => ,
[email] => jphelps2011@gmail.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 203318
[data] => , :: tiashell76@gmail.com
[sort_name] => ,
[email] => tiashell76@gmail.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 203391
[data] => , :: scottonln@stjoelive.com
[sort_name] => ,
[email] => scottonln@stjoelive.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 203408
[data] => , :: landyn.howell@chiefind.com
[sort_name] => ,
[email] => landyn.howell@chiefind.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 204041
[data] => , :: stephenmdaniels@sbcglobal.net
[sort_name] => ,
[email] => stephenmdaniels@sbcglobal.net
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 204126
[data] => , :: timsangel007@yahoo.com
[sort_name] => ,
[email] => timsangel007@yahoo.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 213059
[data] => , :: byron.harrell@gmail.com
[sort_name] => ,
[email] => byron.harrell@gmail.com
[phone] =>
[state_province] =>
[country] =>
)
stdClass Object
(
[id] => 213081
[data] => , :: standby@centurytel.net
[sort_name] => ,
[email] => standby@centurytel.net
[phone] =>
[state_province] =>
[country] =>
)
