Joining two SugarCRM modules on linked field using SugarQuery()

Question

Background and Context

I am attempting to create a custom REST module in SugarCRM 8.0 that joins two modules on a linked field. The two modules are;

• Accounts ( a OOTB module )
• ConsumerLeads ( a custom module )

These two modules are a linked field named w002_consumerleads_accounts_1 such that when I invoke the following endpoint I get a JSON response with all the ConsumerLead data associated with that specific account uuid.

/rest/v11_4/Accounts/{ account_uuid }/link/w002_consumerleads_accounts_1

However, instead of returning the joined results based on the account uuid, my custom module returns the the joined results based on a different account attribute that uniquely identifies an account.

/rest/v11_4/customer/{ customer_id }/leads

The reason I am attempting to build a custom module for this ( instead of using the predefined rest api listed above ) is due to the fact that the system invoking this call does not know the sugar account uuid, and instead knows a business key that uniquely identifies the account ( and is an attribute on the Accounts module ).

The Issue

When I use SugarQuery in my custom rest module to join Accounts and ConsumerLeads my results do not match the results specified in the first rest api ( the predefined rest api ). The result only returns the Accounts module, and does not join the ConsumerLeads module.

The issue is with the joining of the two modules on the linked field; the issue is not related to using the customer uuid v. the account uuid.

Reference Implementations

Based on the SugarCRM Developers Guide I written the following piece of code.

    public function GetLinkLeads($api, $args) {

        try {

            $query = new SugarQuery();
            $query->from(BeanFactory::getBean('Accounts'));
            $query->join('w002_consumerleads_accounts_1');
            $query->limit(10);
            $results = $query->execute();

            return $results;

        }  catch ( Exception $e ) {
            return $e->getCode();
        }

        return 1;
    }

Based on all the information I can gather, this function should return the first 10 account records joined with their ConsumerLeads. However, the response only contains the Accounts module data, it does not join the ConsumerLeads data.

Additionally, I have also tried the following way.

public function GetLinkLeads($api, $args) {

    try {

        $account = BeanFactory::getBean('Accounts', '4606e963-9213-7764-d83f-4cc050c8473d');

        if ( $account->load_relationship('w002_consumerleads_accounts_1') ) {

            $lead = $account->w002_consumerleads_accounts_1->getBeans();

            return $lead;
        }

    }  catch ( Exception $e ) {
        return $e->getCode();
    }

    return 1;
}

Where 4606e963-9213-7764-d83f-4cc050c8473d is an account uuid that has associated ConsumerLeads, but I still cannot get ConsumerLeads data to return.

Question

I would like to either:

• Use SugarQuery() to join these two modules based on a linked field
• Use the default rest api to join the two modules based on an attribute on the Accounts module (not the account uuid)

I have found this exact question on Stack Overflow and the implementation above is based off the recommendation from that post. However, it still is not joining the records.

Answer 1

What you want to achieve should work OOTB using the filter API and specifying the required field's contents in your requests filter definition. Then you should receive the linked record you wanted.

It should work similar to https://stackoverflow.com/a/50608304/4068476

As quick test you could try calling this in your browser or program (here: GET, that's why I pass filter in query string)

/rest/v11_4/w002_ConsumerLeads?filter[0][w002_consumerleads_accounts_1.consumer_id]={ customer_id }

Notes:

• I guessed the spelling of the module name in the URL, make sure to get uppercase/lowercase right.
• If you want to receive both the Accounts and the ConsumerLeads in a single request - just use the /bulk API to wrap two requests into one.

Answer 2

Try the following code:

function outright_load_relationship( $left_bean, $rel_name,$return_beans = 0 ) {
    $rel_obj =  $left_bean->load_relationship( $rel_name );

    if ( !$rel_obj ){
        $rel_name =outright_get_bean_fields( $rel_name );
        $rel_obj =  $left_bean->load_relationship( $rel_name );
    }

    if( $return_beans ){
        $relatedBeans = $left_bean->$rel_name->getBeans();
        return $relatedBeans;
    }

    return $left_bean->$rel_name;

}

function outright_get_bean_fields( $left_bean, $fld_name = false, $fld_key = false ) {
    if( !$fld_name && !$fld_key ) {
        return $left_bean->field_defs;
    } else {
        foreach( $left_bean->field_defs as $key=>$value ) {
            if( $fld_name && $key == $fld_name ) {
                return $left_bean->field_defs[$key];

            } else if( $fld_key && $value[$fld_key] == $fld_name ){
                return $left_bean->field_defs[$key];
            }
        }
    }
}

All you need to pass correct $rel_name in above functions. Please let me know if this does not work.