SQL :
SELECT
CASE
WHEN jt1_following_link.id IS NOT NULL THEN 1
ELSE 0
END following,
sf_contacts.id my_favorite,
contacts.salutation,
contacts.first_name,
contacts.last_name,
contacts.title,
jt2_accounts.id account_id,
jt2_accounts.assigned_user_id account_id_owner,
jt2_accounts.name account_name,
jt2_accounts.assigned_user_id account_name_owner,
jt3_email_addresses_primary.email_address email,
contacts.phone_work,
contacts.date_modified,
contacts.date_entered,
contacts.id,
contacts.assigned_user_id,
contacts.created_by,
contacts_erased.data erased_fields
FROM
contacts
LEFT JOIN
erased_fields contacts_erased ON contacts_erased.bean_id = contacts.id
AND contacts_erased.table_name = ‘contacts’
LEFT JOIN
sugarfavorites sf_contacts ON (sf_contacts.module = ‘Contacts’)
AND (sf_contacts.record_id = contacts.id)
AND (sf_contacts.assigned_user_id = ‘1’)
AND (sf_contacts.deleted = ‘0’)
LEFT JOIN
subscriptions contacts_following ON (contacts.id = contacts_following.parent_id)
AND (contacts_following.deleted = ‘0’)
AND (contacts_following.parent_type = ‘Contacts’)
AND (contacts_following.created_by = ‘1’)
LEFT JOIN
users jt1_following_link ON (jt1_following_link.id = contacts_following.created_by)
AND (jt1_following_link.deleted = ‘0’)
LEFT JOIN
accounts_contacts accounts_contacts ON (contacts.id = accounts_contacts.contact_id)
AND (accounts_contacts.deleted = ‘0’)
AND (accounts_contacts.primary_account = ‘1’)
LEFT JOIN
accounts jt2_accounts ON (jt2_accounts.id = accounts_contacts.account_id)
AND (jt2_accounts.deleted = ‘0’)
LEFT JOIN
email_addr_bean_rel contacts_email_addresses_primary ON (contacts.id = contacts_email_addresses_primary.bean_id)
AND (contacts_email_addresses_primary.deleted = ‘0’)
AND (contacts_email_addresses_primary.bean_module = ‘Contacts’)
AND (contacts_email_addresses_primary.primary_address = ‘1’)
LEFT JOIN
email_addresses jt3_email_addresses_primary ON (jt3_email_addresses_primary.id = contacts_email_addresses_primary.email_address_id)
AND (jt3_email_addresses_primary.deleted = ‘0’)
WHERE
contacts.deleted = ‘0’
ORDER BY contacts.date_modified DESC, contacts.id DESC
LIMIT 21
Tables structure:
CREATE TABLE contacts (
id char(36) NOT NULL,
date_entered datetime DEFAULT NULL,
date_modified datetime DEFAULT NULL,
modified_user_id char(36) DEFAULT NULL,
created_by char(36) DEFAULT NULL,
description mediumtext,
deleted tinyint(1) DEFAULT ‘0’,
salutation varchar(255) DEFAULT NULL,
first_name varchar(100) DEFAULT NULL,
last_name varchar(100) DEFAULT NULL,
title varchar(150) DEFAULT NULL,
facebook varchar(100) DEFAULT NULL,
twitter varchar(100) DEFAULT NULL,
googleplus varchar(100) DEFAULT NULL,
department varchar(255) DEFAULT NULL,
do_not_call tinyint(1) DEFAULT ‘0’,
phone_home varchar(100) DEFAULT NULL,
phone_mobile varchar(100) DEFAULT NULL,
phone_work varchar(100) DEFAULT NULL,
phone_other varchar(100) DEFAULT NULL,
phone_fax varchar(100) DEFAULT NULL,
primary_address_street varchar(150) DEFAULT NULL,
primary_address_city varchar(100) DEFAULT NULL,
primary_address_state varchar(100) DEFAULT NULL,
primary_address_postalcode varchar(20) DEFAULT NULL,
primary_address_country varchar(255) DEFAULT NULL,
alt_address_street varchar(150) DEFAULT NULL,
alt_address_city varchar(100) DEFAULT NULL,
alt_address_state varchar(100) DEFAULT NULL,
alt_address_postalcode varchar(20) DEFAULT NULL,
alt_address_country varchar(255) DEFAULT NULL,
assistant varchar(75) DEFAULT NULL,
assistant_phone varchar(100) DEFAULT NULL,
picture varchar(255) DEFAULT NULL,
lead_source varchar(255) DEFAULT NULL,
dnb_principal_id varchar(30) DEFAULT NULL,
reports_to_id char(36) DEFAULT NULL,
birthdate date DEFAULT NULL,
portal_name varchar(255) DEFAULT NULL,
portal_active tinyint(1) DEFAULT ‘0’,
portal_password varchar(255) DEFAULT NULL,
portal_app varchar(255) DEFAULT NULL,
preferred_language varchar(255) DEFAULT NULL,
campaign_id char(36) DEFAULT NULL,
mkto_sync tinyint(1) DEFAULT ‘0’,
mkto_id int(11) DEFAULT NULL,
mkto_lead_score int(11) DEFAULT NULL,
assigned_user_id char(36) DEFAULT NULL,
team_id char(36) DEFAULT NULL,
team_set_id char(36) DEFAULT NULL,
acl_team_set_id char(36) DEFAULT NULL,
g_assistant_email varchar(255) DEFAULT NULL COMMENT ‘Assistant Email Address.’,
g_client_type varchar(255) DEFAULT NULL COMMENT ‘Client Type, Member,Prospect etc.’,
g_gold_contact_id int(50) DEFAULT NULL COMMENT ‘Non-unique MDM ID.’,
g_linkedin varchar(255) DEFAULT NULL COMMENT ‘Linkedin details.’,
g_phone_mobile_formatted varchar(100) DEFAULT NULL COMMENT ‘Formatted mobile number.’,
g_phone_work_formatted varchar(100) DEFAULT NULL COMMENT ‘Formatted work number.’,
g_ppl_code int(11) DEFAULT NULL COMMENT ‘Athena ppl_code.’,
g_external_id varchar(50) DEFAULT NULL COMMENT ‘External source system id.’,
g_external_source varchar(10) DEFAULT NULL COMMENT ‘External source system Name.’,
g_rb_premium_service_id char(36) DEFAULT NULL,
dp_business_purpose mediumtext,
dp_consent_last_updated date DEFAULT NULL,
g_assistant_phone_formatted varchar(100) DEFAULT NULL COMMENT ‘Formatted phone number from assistant_phone field.’,
g_job_function varchar(255) DEFAULT NULL COMMENT ‘Job Function details’,
g_job_role varchar(255) DEFAULT NULL COMMENT ‘Job Role details’,
g_whatsapp varchar(50) DEFAULT NULL COMMENT ‘WhatsApp Number’,
g_seatholder tinyint(1) DEFAULT ‘0’ COMMENT ‘A calculated flag for contacts who are associated with and active purchased product.’,
g_level_classification varchar(50) DEFAULT NULL COMMENT ‘Classification Level’,
g_suffix_name varchar(20) DEFAULT NULL COMMENT ‘Contact Suffix Name’,
g_is_receivables_contact tinyint(1) DEFAULT ‘0’ COMMENT ‘Is receivables contact flag’,
g_role varchar(255) DEFAULT NULL,
g_function varchar(255) DEFAULT NULL,
g_tier varchar(50) DEFAULT NULL COMMENT ‘Field to segment the Prospects for cross sell and outreach’,
g_campaign_tag varchar(500) DEFAULT NULL COMMENT 'Generic field to identify prospects tagged with a Campaign/Program for targeted outreach ',
PRIMARY KEY (id),
UNIQUE KEY unq_ppl_cd (g_ppl_code),
UNIQUE KEY unq_ppl_code (g_ppl_code),
KEY idx_contacts_date_modfied (date_modified),
KEY idx_contacts_id_del (id,deleted),
KEY idx_contacts_date_entered (date_entered),
KEY idx_contacts_last_first (last_name,first_name,deleted),
KEY idx_contacts_first_last (first_name,last_name,deleted),
KEY idx_contacts_del_last (deleted,last_name),
KEY idx_cont_del_reports (deleted,reports_to_id,last_name),
KEY idx_reports_to_id (reports_to_id),
KEY idx_del_id_user (deleted,id,assigned_user_id),
KEY idx_cont_assigned (assigned_user_id),
KEY idx_contact_title (title),
KEY idx_contact_mkto_id (mkto_id),
KEY idx_contacts_assigned_del (assigned_user_id,deleted),
KEY idx_contact_external_id (g_external_id),
KEY idx_contacts_external_id (g_external_id),
KEY idx_g_extended_contact_id (g_rb_premium_service_id),
KEY idx_contacts_g_external_id (g_external_id),
KEY idx_contacts_tmst_id (team_set_id,deleted),
KEY idx_contacts_acl_tmst_id (acl_team_set_id,deleted),
KEY idx_contacts_date_modified_id (date_modified,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE erased_fields (
bean_id char(36) NOT NULL,
table_name varchar(128) NOT NULL,
data mediumtext,
PRIMARY KEY (bean_id,table_name),
KEY idx_erased_fields_bean_id (bean_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4