You are here

SuiteCRM / SugarCRM Cheat Sheet

Contents

  • 1. Find id of record to unlink.
    SQL> SET @uid = 'f232e8d0-9b82-20f7-7c35-5c1a5ce94e0a';
    
  • 2. Find entry in Target List table:
    SQL> SELECT plp.id, plp.related_type, plp.date_modified, plp.deleted
         , pl.name
      FROM prospect_lists_prospects AS plp
      JOIN prospect_lists AS pl ON plp.prospect_list_id = pl.id
     WHERE plp.related_id = @uid
    ;
    
  • 3. Update entry in Target List table:
    SQL> START TRANSACTION;
    SQL> UPDATE prospect_lists_prospects
       SET deleted = 1
     WHERE related_id = @uid
       AND id = '5b3047b2-3b45-1a6e-e6fb-5d3fe1efa93b'
    ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    SQL> COMMIT;
    

Find Target Lists of Target/Prospect

Result:

+--------------------------------------+--------------+------------+-----------+------------------------+
| id                                   | first_name   | last_name  | type      | target_lists           |
+--------------------------------------+--------------+------------+-----------+------------------------+
| e9678a2a-ee96-dbc6-6fe0-5d0e7be4ac45 | Thomas       | Benderskiy | Prospects | Target EN              |
| e97b4caf-317a-9d97-58b0-5d0e38b81959 | Marko        | Marklund   | Prospects | Target DACH            |
| e98c07e8-64e7-8731-8ad9-5d0e7a6e8844 | Avik         | Beyer      |           |                        |
| e98106c2-f0cf-f453-162b-5d0e80422eb9 | Christian    | Ribas      | Prospects | Target DACH, Target EN |
+--------------------------------------+--------------+------------+-----------+------------------------+

Accounts:

SELECT a.id, a.name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM accounts AS a
  JOIN accounts_cstm AS ac ON a.id = ac.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = a.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE ac.interests_c LIKE '%^brman^%'
   AND a.deleted = 0
 GROUP BY a.id, a.name, plp.related_type
;

Contacts:

SELECT c.id, c.first_name, c.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM contacts AS c
  JOIN contacts_cstm AS cc ON c.id = cc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = c.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE cc.interests_c LIKE '%^brman^%'
   AND c.deleted = 0
 GROUP BY c.id, c.first_name, c.last_name, plp.related_type
;

Leads:

SELECT l.id, l.first_name, l.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM leads AS l
  JOIN leads_cstm AS lc ON l.id = lc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = l.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE lc.interests_c LIKE '%^brman^%'
   AND l.deleted = 0
 GROUP BY l.id, l.first_name, l.last_name, plp.related_type
;

Targets / Prospects:

SELECT p.id, p.first_name, p.last_name
     , IFNULL(plp.related_type, '') AS type
     , IFNULL(GROUP_CONCAT(pl.name), '') AS target_lists
  FROM prospects AS p
  JOIN prospects_cstm AS pc ON p.id = pc.id_c
  LEFT JOIN prospect_lists_prospects AS plp ON plp.related_id = p.id AND plp.deleted = 0
  LEFT JOIN prospect_lists AS pl ON pl.id = plp.prospect_list_id AND pl.deleted = 0
 WHERE p.deleted = 0
 GROUP BY p.id, p.first_name, p.last_name, plp.related_type
;

List all email addresses of all accounts

SELECT ea.email_address, eabr.bean_module
  FROM email_addr_bean_rel AS eabr
  JOIN email_addresses AS ea ON ea.id = eabr.email_address_id
 WHERE eabr.bean_module = 'Accounts'
  AND eabr.deleted = 0
;
+------------------------+-------------+
| email_address          | bean_module |
+------------------------+-------------+
| info@top.org           | Accounts    |
| first.last@company.com | Accounts    |
+------------------------+-------------+

Objects without Target List

Contacts without Target List:

SELECT c.first_name, c.last_name
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Hans       | Maier     |
| Fritz      | Müller    |
+------------+-----------+

Add Contacts without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, c.id, 'Contacts', CURRENT_TIMESTAMP(), 0
  FROM contacts AS c
  LEFT JOIN prospect_lists_prospects AS plp ON c.id = plp.related_id AND plp.deleted = 0
 WHERE c.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Leads without Target List:

SELECT l.first_name, l.last_name
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;

Add Leads without Target List to a Target List:

SQL> SELECT id, name FROM prospect_lists WHERE deleted = 0 ORDER BY name;
+--------------------------------------+----------------------------------------+---------+
| id                                   | name                                   | deleted |
+--------------------------------------+----------------------------------------+---------+
| 1bd03c4e-b3f3-b3eb-f237-5d0e26413ae9 | Target DACH                            |       0 |
| 1c0901f1-41b2-cf42-074d-5d0cdc12b47d | Target EN                              |       0 |
+--------------------------------------+----------------------------------------+---------+

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, l.id, 'Leads', CURRENT_TIMESTAMP(), 0
  FROM leads AS l
  LEFT JOIN prospect_lists_prospects AS plp ON l.id = plp.related_id AND plp.deleted = 0
 WHERE l.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Prospects without Target List:

SELECT p.first_name, p.last_name, p.primary_address_country
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;

Add Prospects without Target List to a Target List:

SQL> SET @plid = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
SQL> START TRANSACTION;
SQL> INSERT INTO prospect_lists_prospects
(id, prospect_list_id, related_id, related_type, date_modified, deleted)
SELECT uuid(), @plid, p.id, 'Prospects', CURRENT_TIMESTAMP(), 0
  FROM prospects AS p
  LEFT JOIN prospect_lists_prospects AS plp ON p.id = plp.related_id AND plp.deleted = 0
 WHERE p.deleted = 0
   AND plp.id IS NULL
;
SQL> COMMIT;

Find PLP without Objects (Contacts/Leads/Prospects)

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN prospects AS p on p.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Prospects'
   AND p.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN leads AS l on l.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Leads'
   AND l.id IS NULL
;

SELECT plp.related_id, plp.related_type
  FROM prospect_lists_prospects AS plp
  LEFT JOIN contacts AS c on c.id = plp.related_id
 WHERE plp.deleted = 0
   AND plp.related_type = 'Contacts'
   AND c.id IS NULL
;

Find Prospect List Entries per Prospect Type

SELECT related_type, COUNT(*)
  FROM (
SELECT DISTINCT plp.related_type, plp.related_id
  FROM prospect_lists_prospects AS plp
 WHERE plp.deleted = 0
) AS d GROUP BY related_type
;
+--------------+----------+
| related_type | COUNT(*) |
+--------------+----------+
| Contacts     |     1959 |
| Leads        |     5891 |
| Prospects    |     8970 |
+--------------+----------+