New for Sugar 6.5: Grab a filtered list of related record to the current record using beans

bsoremsugar —  April 26, 2012 — 14 Comments

Editor’s Note: As Sugar 6.5 is rolling into RC mode, we are kicking off a series of blog posts talking about the new functionality for developers. In this part, we’ll talk about the enhancements made to bean relationship handling.

You’ll remember this blog post from a few weeks back, where we talked about how to add, edit, and navigate beans using the bean class framework rather than just using SQL queries. I got a question from a partner about the post, where he was asking how to grab all the record beans related to the current one. Fortunately this is pretty easy, as you remember from this blog post. But now with the enhancements to the Link2 class, we have a new easier and more flexable getBeans() method we can use, as you can see in the below example:

$account = new Account();
$account->retrieve($id);

$contacts = $account->contacts->getBeans();

if(!empty($contacts)) {
    foreach($contacts as $contact) {
        echo $contact->name."<br/>";
    }
}

But let’s say the account has lots of contracts, and you are only looking for those in Ohio. Now in Sugar 6.5 you can pass in an array parameter to the getBeans() to specify a where parameter to filter the beans coming back to only return the ones you are interested in. So to filter by billing state ‘OH’, change the above example as follows:

$account = new Account();
$account->retrieve($id);
$params = array(
    'where' => array(
        'lhs_field' => 'billing_address_state',
        'operator' => '=',
        'rhs_value' => 'OH',
        ),
    );
$contacts = $account->contacts->getBeans($params);

if(!empty($contacts)) {
    foreach($contacts as $contact) {
        echo $contact->name."<br/>";
    }
}

And there’s more! Let’s say you only want 2 records back. You can add a limit parameter here as well as follows:

$account = new Account();
$account->retrieve($id);
$params = array(
    'where' => array(
        'lhs_field' => 'billing_address_state',
        'operator' => '=',
        'rhs_value' => 'OH',
        ),
    'limit' => '2',
    );
$contacts = $account->contacts->getBeans($params);

if(!empty($contacts)) {
    foreach($contacts as $contact) {
        echo $contact->name."<br/>";
    }
}

And you can specify to look at deleted records as well like this:

$account = new Account();
$account->retrieve($id);
$params = array(
     'deleted' => '1',
    );
$contacts = $account->contacts->getBeans($params);

if(!empty($contacts)) {
    foreach($contacts as $contact) {
        echo $contact->name."<br/>";
    }
}

This new method will make it easier for you to navigate thru related record without resorting to SQL queries. Let us know what you think in the comments.

14 responses to New for Sugar 6.5: Grab a filtered list of related record to the current record using beans

  1. 
    Ashley J Smith June 12, 2012 at 6:17 am

    We only need to resort to SQL queries when you took this functionality out of the get_linked_beans in 6.4.
    Does this “new” functionality cover fields in the cstm table of a module?  Now that would be pretty cool.

    • 

       get_linked_beans() basically uses getBeans() at the core to do what it needs to do. And getBeans() does a complete bean load, so you will get the related field and custom fields.

      • 
        Ashley J Smith June 12, 2012 at 11:36 am

        Sorry, I meant the where clause.  Could you use custom fields in the filter
        $params = array(
            ‘where’ => array(
                ‘lhs_field’ => ‘custom_field_c’,
                ‘operator’ => ‘=’,
                ‘rhs_value’ => ‘OH’,
                ),
            ‘limit’ => ‘2’,
            );as previously get_linked_beans “where array” params couldn’t.Thanks :)

        • 

           That should work, but do let us know if it doesn’t

          • 

            Hi this is not working for custom fields.

            if i do this:

            $account = new Account();
            $account->retrieve($invoice->billing_account_id);
            $account->load_relationship(‘contacts’);

            $params = array(

            ‘where’ => array(

            ‘lhs_field’ => ‘first_name’,

            ‘operator’ => ‘=’,

            ‘rhs_value’ => ‘Jaime’,
            ),
            ‘limit’ => ‘1’,

            );
            ;
            $contacts = $account->contacts->getBeans($params);

            if(!empty($contacts)) {

            foreach($contacts as $contact) {

            }

            }

            in this case everything is fine. But if i use a custom field like:

            $params = array(

            ‘where’ => array(

            ‘lhs_field’ => ‘billingcontact_c’,

            ‘operator’ => ‘=’,

            ‘rhs_value’ => ‘1’,

            ),

            ‘limit’ => ‘1’,

            );
            ;

            In this case i get an empty array

  2. 

    Could you explain “rhs” and “lhs” and this line $contacts = $account->contacts->getBeans($params);

    please?

    and What about lots of “WHERE” and “AND” conditions?

    I want to have the price (or full informations) of products named “Something2″ Where the custom field choix_loc_c is ‘Something1′.
    (data table aos_products and aos_products_cstm from a third part module Advanced OpenSales created by Salesagility)


    $producti = BeanFactory::getBean('AOS_Products')->retrieve();
    $params = array(
    'where' => array(
    'lhs_field' => 'choix_loc_c',
    'operator' => '=',
    'rhs_value' => 'Something1',
    ),
    'and'=>array(
    'lhs_field'=>'name',
    'operator'=>'=',
    'rhs_value'=>'Something2'
    ),
    );

    $prices = $producti->price->getBeans($params);
    if (!empty($prices)) {
    foreach ($prices as $price) {
    die($price->price . "");
    }
    }

  3. 

    Does this work with using Order By as a param? and if so how?

  4. 

    Is ->getBeans() only available in the commercial versions of Sugar?

    I am on CE and I get a blank result when I use ->getBeans() but it works when I use ->get()

  5. 

    In my case ‘where’ was not working as an array. So i go into the core functionality and found that it also accepts where clause as string and my problem was solved.
    Note: Field I used for filter was created using vardefs, may be that was the reason that Query Failed.
    Here is a sample code:
    if ($accounts->load_relationship(‘cn_creditnotes_accounts’)) {
    $params = array(
    ‘where’ => ‘cn_creditnotes.cn_allocated = 0′,
    );
    $array_relationship = $accounts->cn_creditnotes_accounts->getBeans($params);
    }

Trackbacks and Pingbacks:

  1. change the assigned user of all the related records in whole sugar - October 15, 2012

    [...] [...]

  2. Adding params in get_linked_beans or getBeans - December 28, 2012

    [...] [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s