SQL or Bean - Related Module Help

I’m having some difficulty coming up with the logic to display records which do not have a related record.

I have a custom module named “Insurances” which has a many-one relationship with Accounts.

I need to be able to generate a list (dashlet) of Active Accounts classified as Subcontractors which do not have any (non-deleted) relationships with an “Insurances” record which is marked as Active.

The use here is to generate a list of Active Accounts classified as Subcontractors for which I do not have Insurance (or current insurance if others are marked as archived) on file.

I’ve been using Custom SQL for Dashlets, but if bean works I’m open to it.

Any suggestions on the Logic? This one’s got me a bit stumped.

The Accounts table is (obviously) “accounts”, type as “account_type”.
Insurance table is “ins_insurances”, archived as “archived” tinyint
Relationship table is “accounts_ins_insurances_1_c”, related account is “accounts_ins_insurances_1accounts_ida”, related insurance is “accounts_ins_insurances_1ins_insurances_idb”, and deleted “deleted”

Get all accounts then loop through them and check if it has any related Insurances, if it does not then add the account to an array then use that array to generate the dashlet.

Here is very rough code, its not tested but should give you the general idea. Also check out http://developer.sugarcrm.com/2012/03/23/howto-using-the-bean-instead-of-sql-all-the-time/


//get all accounts
$account = new $Account();
$result = $Account->get_full_list(); 
$empty_accounts = array();

foreach($result as $account){
      $account->load_relationship('accounts_ins_insurances_1'); 
    //count relations	
     $count = 0;
      foreach ($account->accounts_ins_insurances_1->getBeans() as $Insurances )[
                $count++;
      }
     
      if($count == 0){
             $empty_accounts[] = $account;
      }
}

2 Likes

Thanks Andy,

I was able to accomplish it using the below SQL, but will look into changing this to the bean as I do understand there are advantages.

$lvsParams = array( 
  'custom_select' => '', 
  'custom_from' => ' LEFT JOIN accounts_ins_insurances_1_c AS insRel ON insRel.accounts_ins_insurances_1accounts_ida=accounts.id LEFT JOIN ins_insurances AS ins ON ins.id=insRel.accounts_ins_insurances_1ins_insurances_idb', 
   'custom_where' => ' AND (accounts.id NOT IN (SELECT accounts_ins_insurances_1accounts_ida FROM accounts_ins_insurances_1_c AS AI INNER JOIN ins_insurances AS I ON AI.accounts_ins_insurances_1ins_insurances_idb=I.id WHERE AI.deleted <> 1 AND I.archive<>1) AND accounts.account_type="Subcontractor")',
  'distinct' => true							
);