Creating relationship between large number of rows (30,000+)

💬 General Discussion
1

Hi all,

I have created a custom module called Sites, which will be a list of 30,000+ hospitals/companies and their sites. This sites module has a many to one relationship with Accounts, whereby accounts are NHS Trusts across england/wales/scotland/ireland.

The rows in both sites/accounts tables are populated with accurate NHS data and include the hospital / trust organisation codes. The hospitals have a parent organisation code, so I can link them up via this value.

With the sheer number of rows, it obviously can’t be manually linked through the SuiteCRM front end. With the suiteCRM guid number, I’m unable to create the relationship with mysql code or via phpmyadmin.

What is the best way to create the relationship in my sites_sites_accounts_c table?

I thought a workflow might work for this; e.g. when importing a new Site, if the site.parent_organisation_code = account.organisation_code then create a new relationship. But this doesn’t seem to work (I’m guessing the relationship must exist in the first place for the condition to match, rather than it searching all accounts for the organisation code?).

Would a logic hook on insert be the best solution?

2

Managed to resolve it with the following;

# Join sites parental organisation code with account organisation code
INSERT INTO `sites_sites_accounts_c`(
    `id`,
    `date_modified`,
    `deleted`,
    `sites_sites_accountsaccounts_ida`,
    `sites_sites_accountssites_sites_idb`
    )
SELECT
       UUID(), NOW(), '0', accounts_cstm.id_c, sites_sites.id
FROM
     sites_sites
                    JOIN accounts_cstm ON accounts_cstm.organisation_code_c = sites_sites.parent_organisation_code;

I realise this is very specific to my issues but I didn’t realise MySQL could generate the same UUID used by SuiteCRM. I’ve only ever used the integer based primary key system. Good to know! Hopefully someone else may find this useful.