1 Topic by apmuthu (edited by apmuthu 12/25/2014 09:26:49 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Topic: Navigating the contacts table - tutorial and speedup fixes

This tutorial is also wiki-ed.

Navigating 0_crm_contacts table

The 0_crm_contacts table performs a many to many relationship between the 0_persons table and the a few entity_id tables such as 0_debtors_master, 0_cust_branch, 0_suppliers. Furthermore, this implementation in FA leverages several non standard constructs in php/mysql code that roughly translates into the following SQL statements: 

-- Select a CustomerCode (for example) from '''debtor_no''' field in the '''0_debtor_master''' table
SET @debtor_entity_id:=600;

-- Get the HQ Person ID
SET @branch_person_id:=(SELECT person_id FROM `0_crm_contacts` 
    WHERE `type`='customer' AND `action`='general' AND person_id=@debtor_entity_id);

-- Get the BranchCodes 
SET @branch_codes:=(SELECT GROUP_CONCAT(branch_code) FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id);

-- Show the Input Parameters and Extracted Parameters
SELECT @debtor_entity_id AS CustomerCode, @branch_person_id AS HQPerson, @branch_codes AS BranchCodes;


-- HQ Branch Person details
SELECT * FROM `0_crm_persons` WHERE `id` IN (
SELECT person_id FROM `0_crm_contacts` WHERE `type`='cust_branch' AND `action`='general' AND entity_id IN (
        SELECT branch_code FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id
    ) AND person_id = @branch_person_id
);

-- Non HQ Branch Person Details
SELECT * FROM `0_crm_persons` WHERE `id` IN (
SELECT person_id FROM `0_crm_contacts` WHERE `type`='cust_branch' AND `action`='general' AND entity_id IN (
        SELECT branch_code FROM `0_cust_branch` WHERE debtor_no=@debtor_entity_id
    ) AND person_id <> @branch_person_id
);

To effectively leverage this mechanism, a few indices are in order to speedup the process by over a many times (9000ms down to 3.5ms when queried from among 2500 records): 

ALTER TABLE `0_crm_contacts` ADD INDEX `entity_id` (`entity_id`);
ALTER TABLE `0_crm_contacts` ADD INDEX `person_id` (`person_id`);

apmuthu's Website