SELECT
|
CASE
|
WHEN jt8_favorite_link.id IS NOT NULL THEN
|
1
|
ELSE
|
0
|
END my_favorite,
|
CASE
|
WHEN jt1_following_link.id IS NOT NULL THEN
|
1
|
ELSE
|
0
|
END following,
|
contacts.salutation full_name__salutation,
|
contacts.first_name full_name__first_name,
|
contacts.last_name full_name__last_name,
|
contacts.salutation salutation,
|
contacts.first_name first_name,
|
contacts.last_name last_name,
|
contacts.title title,
|
contacts_cstm.hug_last_branch_c hug_last_branch_c,
|
contacts_cstm.hug_status_c hug_status_c,
|
contacts_cstm.hug_postcode_c hug_postcode_c,
|
jt3_accounts.id account_id,
|
jt3_accounts.`name` account_name,
|
jt5_email_addresses_primary.email_address email,
|
contacts.phone_work phone_work,
|
jt7_assigned_user_link.first_name assigned_user_name__first_name,
|
jt7_assigned_user_link.last_name assigned_user_name__last_name,
|
contacts.date_modified date_modified,
|
contacts.date_entered date_entered,
|
contacts_cstm.hug_credit_limit_c hug_credit_limit_c,
|
contacts_cstm.hug_customer_c hug_customer_c,
|
contacts_cstm.hug_creation_date_c hug_creation_date_c,
|
contacts.assigned_user_id assigned_user_id,
|
contacts.id id,
|
contacts.created_by created_by
|
FROM
|
contacts
|
LEFT JOIN sugarfavorites sf_contacts ON (
|
contacts.id = sf_contacts.record_id
|
AND sf_contacts.deleted = 0
|
AND sf_contacts.module = 'Contacts'
|
AND sf_contacts.created_by = '1'
|
)
|
LEFT JOIN subscriptions jt2_subscriptions ON (
|
contacts.id = jt2_subscriptions.parent_id
|
AND jt2_subscriptions.deleted = 0
|
AND jt2_subscriptions.parent_type = 'Contacts'
|
AND jt2_subscriptions.created_by = '1'
|
)
|
LEFT JOIN users jt1_following_link ON (
|
jt1_following_link.id = jt2_subscriptions.created_by
|
AND jt1_following_link.deleted = 0
|
)
|
LEFT JOIN users_cstm jt1_following_link_cstm ON (
|
jt1_following_link_cstm.id_c = jt1_following_link.id
|
)
|
{color:red}LEFT JOIN accounts_contacts jt4_accounts_contacts ON (
|
contacts.id = jt4_accounts_contacts.contact_id
|
AND jt4_accounts_contacts.deleted = 0
|
AND jt4_accounts_contacts.primary_account = '1'
|
){color}
|
LEFT JOIN accounts jt3_accounts ON (
|
jt3_accounts.id = jt4_accounts_contacts.account_id
|
AND jt3_accounts.deleted = 0
|
)
|
LEFT JOIN email_addr_bean_rel jt6_email_addr_bean_rel ON (
|
contacts.id = jt6_email_addr_bean_rel.bean_id
|
AND jt6_email_addr_bean_rel.deleted = 0
|
AND jt6_email_addr_bean_rel.primary_address = '1'
|
AND jt6_email_addr_bean_rel.bean_module = 'Contacts'
|
)
|
LEFT JOIN email_addresses jt5_email_addresses_primary ON (
|
jt5_email_addresses_primary.id = jt6_email_addr_bean_rel.email_address_id
|
AND jt5_email_addresses_primary.deleted = 0
|
)
|
LEFT JOIN users jt7_assigned_user_link ON (
|
contacts.assigned_user_id = jt7_assigned_user_link.id
|
AND jt7_assigned_user_link.deleted = 0
|
)
|
LEFT JOIN users_cstm jt7_assigned_user_link_cstm ON (
|
jt7_assigned_user_link_cstm.id_c = jt7_assigned_user_link.id
|
)
|
LEFT JOIN users jt8_favorite_link ON (
|
jt8_favorite_link.id = sf_contacts.modified_user_id
|
AND jt8_favorite_link.deleted = 0
|
)
|
LEFT JOIN users_cstm jt8_favorite_link_cstm ON (
|
jt8_favorite_link_cstm.id_c = jt8_favorite_link.id
|
)
|
LEFT JOIN contacts_cstm ON contacts_cstm.id_c = contacts.id
|
WHERE
|
contacts.deleted = 0
|
ORDER BY
|
contacts.date_modified DESC,
|
contacts.id DESC
|
LIMIT 20,
|
21
|