Very large target lists need phpMyAdmin. Can anyone help with a SQL query?

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hi,

We have problems with creating large target lists within Suite. “rahulner” has suggested a more direct approach using SQL and phpMyAdmin on this post on GitHub:
https://github.com/salesagility/SuiteCRM/issues/3534

70k target list in 1.8 seconds. Very good. However, I have to do it in two steps (see below). Does anyone know how I can combine these two queries into a single query, without the stop-gap dummy search? I’m at the limit of my SQL knowledge! I can’t get the INSERT statement to work with INNER JOINs.

Many thanks in advance.

STEP 1 query

UPDATE contacts_cstm

INNER JOIN email_addr_bean_rel
ON email_addr_bean_rel.bean_id = contacts_cstm.id_c

INNER JOIN email_addresses
ON email_addresses.id = email_addr_bean_rel.email_address_id

SET contacts_cstm.ascl_target_identifier_c = 'STOP_GAP_DUMMY_SEARCH'

WHERE email_addr_bean_rel.bean_module = "Contacts"
AND contacts_cstm.region_c = 'EUROPE'
AND contacts_cstm.type_c IN (1, 2, 3)
AND email_addresses.invalid_email = 0
AND email_addresses.opt_out = 0

STEP 2 query:

INSERT INTO prospect_lists_prospects

SELECT UUID() id, pl.id prospect_list_id, ccstm.id_c related_id, 'Contacts' related_type, now() date_modified , 0 deleted

FROM contacts_cstm ccstm, prospect_lists pl

WHERE ccstm.ascl_target_identifier_c = 'STOP_GAP_DUMMY_SEARCH'

AND pl.id = {relevant prospect list ID}

AND NOT EXISTS ( SELECT 1 from prospect_lists_prospects plp where plp.prospect_list_id= pl.id and plp.related_id = ccstm.id_c )
2

**bump for anyone who might be able to help with SQL?

3

To INSERT into two separate tables, doing 2 separate INSERTs is correct, I think.

You can just feed both queries together into phpMyAdmin, and it will do them in sequence, in one step.

4

Never mind my previous answer - I hadn’t really payed enough attention to your queries.

It seems your 1st query is simply testing a few conditions, setting a field, and then this field is tested in the 2nd query.

So you could try (I’m doing this without testing in SQL, so I’m not sure it will work) turning your 1st query into a select:

SELECT id 
FROM contacts_cstm

INNER JOIN email_addr_bean_rel
ON email_addr_bean_rel.bean_id = contacts_cstm.id_c

INNER JOIN email_addresses
ON email_addresses.id = email_addr_bean_rel.email_address_id

WHERE email_addr_bean_rel.bean_module = "Contacts"
AND contacts_cstm.region_c = 'EUROPE'
AND contacts_cstm.type_c IN (1, 2, 3)
AND email_addresses.invalid_email = 0
AND email_addresses.opt_out = 0

Make sure this query returns a list of the records that you intend to include.

Now use that in the WHERE of the second query:

INSERT INTO prospect_lists_prospects

SELECT UUID() id, pl.id prospect_list_id, ccstm.id_c related_id, 'Contacts' related_type, now() date_modified , 0 deleted

FROM contacts_cstm ccstm, prospect_lists pl

WHERE ccstm.id IN (
	SELECT id 
	FROM contacts_cstm

	INNER JOIN email_addr_bean_rel
	ON email_addr_bean_rel.bean_id = contacts_cstm.id_c

	INNER JOIN email_addresses
	ON email_addresses.id = email_addr_bean_rel.email_address_id

	WHERE email_addr_bean_rel.bean_module = "Contacts"
	AND contacts_cstm.region_c = 'EUROPE'
	AND contacts_cstm.type_c IN (1, 2, 3)
	AND email_addresses.invalid_email = 0
	AND email_addresses.opt_out = 0
)

AND pl.id = {relevant prospect list ID}

AND NOT EXISTS ( SELECT 1 from prospect_lists_prospects plp where plp.prospect_list_id= pl.id and plp.related_id = ccstm.id_c )