Outlines the relationships between emails, email addresses, and bean records.
|email_addresses||Each record in this table represents an email address in the system. Note that the
|emails||Each record in this table represents an email in the system. Note that emails fall outside the scope of this document, but are mentioned here for clarity, as the two modules are closely related.|
|emails_beans||Similar to the
|<module>||This is used to show how an email address record relates to a record in any module is set on
The following diagram illustrates table relationships between email addresses and other modules, email addresses and email records, and email records and other modules.
Retrieve the Primary Email Address of a Contact
The following query will fetch the email address given a specific contacts id:
SELECT email_address FROM email_addresses JOIN email_addr_bean_rel eabr ON eabr.email_address_id = email_addresses.id WHERE eabr.bean_module = "Contacts" AND eabr.bean_id = "<contact id>" AND email_addresses.invalid_email = 0 AND eabr.deleted = 0 AND eabr.primary_address = 1;
Retrieve All Records Related to an Email Address
The following query will fetch the id and module name of all records related to the specified email address:
SELECT bean_module, bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ON eabr.email_address_id = email_addresses.id WHERE email_addresses.email_address = "<email address>" AND eabr.deleted = 0;
Retrieve All Emails Sent From An Email Address
The following query will fetch all emails sent from a specified email address:
SELECT emails.name, emails.date_sent FROM emails JOIN emails_email_addr_rel eear ON eear.email_id = emails.id JOIN email_addresses ON eear.email_address_id = email_addresses.id WHERE email_addresses.email_address = "<email address>" AND eear.address_type = "from" AND eear.deleted = 0
Cleanup Duplicate Email Addresses
The following queries will remove any duplicate email addresses.
First, create a temporary table with distinct records from the
create table email_addr_bean_rel_tmp_ful SELECT * FROM email_addr_bean_rel WHERE deleted = '0' GROUP BY email_address_id, bean_module, bean_id ORDER BY primary_address DESC;
Next, clear out the
Move the records from the temporary table back to
INSERT INTO email_addr_bean_rel SELECT * FROM email_addr_bean_rel_tmp;
Validate that all of the duplicates have been removed:
SELECT COUNT(*) AS repetitions, date_modified, bean_id, bean_module FROM email_addr_bean_rel WHERE deleted = '0' GROUP BY bean_id, bean_module, email_address_id HAVING repetitions > 1;
Finally, remove the temporary table:
drop table email_addr_bean_rel_tmp;
Email Address Validation
To validate an email address in a server-side context, the
EmailAddress::isValidEmail() static method should be used. For example:
$emailAddress = "firstname.lastname@example.org"; $isValid = EmailAddress::isValidEmail($emailAddress);
EmailAddress::isValidEmail method leverages the PHPMailer library bundled with Sugar, specifically the
PHPMailer::validateAddress method, which validates the address according to the RFC 5321 and RFC 5322 standards.
To validate an email address client-side context, the
app.utils.isValidEmailAddress() function can be used.
var emailAddress = "email@example.com"; var isValid = app.utils.isValidEmailAddress(emailAddress);
Note: This function is more permissive and does not conform exactly to the RFC standards used on the server. As such, the email address will be validated again on the server when the record is saved, which could still fail validation.
Last modified: 2021-01-06 23:28:41