HOWTO: Using the bean instead of SQL all the time.

Sugar Dev Team —  March 23, 2012 — 118 Comments

Editor’s Note: This has become one of the most popular posts on the developer blog, so thank you everyone for your interest.

Do note that if you are using Sugar 6.3 or later, check out this post for an even easier way to load beans.

I had one of our community members who likes to “keep us honest” call me out the other day on Twitter…

And he was referring to a post we did where we did some raw SQL queries in the code example. I’ve updated the code examples since then to be correct. And Jeff don’t worry, as you rightfully so called us out on slipping a bit from our normal quality here :-).

But to start off, why is this important? Here’s a few reasons…

  • Code portability. Even the most conservative SQL code can have issues on a DB you haven’t tested you add-on against. If you are a third-party add-on developer, this is a huge issue you need to deal with, so using bean methods avoids having to worry about whether your code tested on MySQL will also work on SQL Server.
  • Upgrade-safeness. Dealing right with the beans avoids you having to worry about any database structure changes under the hood that may happen between releases, since the bean model layer abstracts that away for you.
  • Avoiding i18n and L10n. When you grab the data right from the DB, you get the data raw back from the DB. Then of course, you have to do all the conversations and translations to present it back to the user. When you use the beans, it’s all done for you.
  • Respects your business logic. Have a few logic hooks, or workflows defined, or maybe some Sugar Logic fields? Doing straight calls to the DB passes right by these hooks, while using the bean methods makes sure they are called as needed.
  • Respects ACLs. The bean methods check to make sure the given user has access to the fields and records being worked with, which raw SQL doesn’t account for.
  • Cleaner code separation. Just about everyone out there will tell you that keeping DB code at the model level makes the most sense for proper code separation ( read more about MVC to learn about this in-depth ). To bring this down to layman’s terms, DB code at the view level is a quick fix that is a long term maintenance nightmare.

So I thought I would use this as a chance to talk about the right way to work with records from the database without using SQL queries as a crutch. I’ll touch on a few common use-cases we see.

Grabbing a record

So the easiest one I see is something like this, trying to search for a single record based on criterion. In SQL terms, think something like this:

select * from accounts where name = 'Foo Bar';

This is pretty simple to redo with the beans instead and no SQL in sight:

$focus = new Account();
$focus->retrieve_by_string_fields(array('name' => 'Foo Bar' ));

With that the $focus object will have all the data for your record. If you are looking up by id you can use the faster retrieve() method like shown below:

$focus = new Account();
$focus->retrieve('my record id');

Grabbing the records related to the current record

Now that you have a record object, you can easily reach out and grab related records as well. In SQL terms, this is what you would do:

select * from contacts where id in ( select contact_id from accounts_contacts where account_id = 'my record id' )

The dreaded subquery comes into play here, and dealing with different databases and subqueries can often give off different performance results. Here’s a cleaner way to do this with the bean object:

$focus = new Account();
$focus->retrieve('my record id');
$focus->load_relationship('contacts');

$list = array();
foreach ($focus->contacts->getBeans() as $contact) {
    $list[$contact->id] = $contact;
}

Do note that if you aren’t using Sugar 6.3 or later, you’ll need to change getBeans() to getBeans(new Contact()) in the code example above.

While many will argue that this causes a few extra queries to happen versus the one above, the queries done are very lightweight versus the heavier one that needs more love to make it perform well.

Save changes to a record

And finally, you can use the bean to update records as well; see the following SQL you have to do before:

update accounts set name = 'Bar Foo' where id = 'my record id'

And here’s the simple way to do this via the bean.

$focus = new Account();
$focus->retrieve('my record id');
$focus->name = 'Bar Foo';
$focus->save();

This also can save you the trouble of having to alter your SQL query in case you are updating the custom fields versus the core fields, as well as deal with all the data conversion needed to get the record into the proper database format.

Adding and removing related records

Another common task here, where it’s often quite tempting to start mucking with the relationship tables themselves. By going this route you don’t need to know the details of what the actual tables in use are, avoiding a query like this for adding a new related record

insert into accounts_contacts (id, contact_id, account_id, date_modified, deleted) values ...

And this for deleting a record

delete from accounts_contacts where contact_id = 'my contact id' and account_id = 'my account id'

This is something you definitely want some to leverage the bean for. Try this instead for adding new related beans:

$focus = new Account();
$focus->retrieve('my account id');
$focus->load_relationship('contacts');
$focus->contacts->add('my contact id');
$focus->save();

And to delete…

$focus = new Account();
$focus->retrieve('my account id');
$focus->load_relationship('contacts');
$focus->contacts->delete($focus->id, 'my contact id');
$focus->save();

Note the save above is import to do to kick off any affected business logic that may depend on values set by the relationship, but isn’t required per say to add or remove the relationship ( the add() and delete() call make that happen ).

Hopefully these examples give you good reason to get away from using raw SQL in your customizations. Have some places you think you can’t get away from it? I encourage you to give me feedback and post in the comments below on other areas you are forced to use ugly SQL in lieu of the cleaner bean methods.

118 responses to HOWTO: Using the bean instead of SQL all the time.

  1. 
    Stewart Osborne August 27, 2013 at 10:56 am

    This could be my ignorance, but shouldn’t

    $list = array();
    foreach ($focus->contacts->getBeans() as $contact) {
    $list[contact->id] = $contact;
    }

    be

    $list = array();
    foreach ($focus->contacts->getBeans() as $contact) {
    $list[$contact->id] = $contact;
    }

    ?

    Stu

    The other thing I’ve noticed if you’re using this in PHP in a logic hook, sometimes GetBean is preferrable to GetBeans because you can pass the disable_row_level_security parameter into GetBean.

    • 

      Can you illustrate a bit more what you mean here?

      • 
        Stewart Osborne August 29, 2013 at 10:35 pm

        You’re correct; I should probably delete this comment. What I meant to say was I found this post and converted one of my logic hooks with the $bean->[module]->getBeans() method you mentioned. My logic hook looks through old tasks on a contact for a task with a certain flag on it and then takes action based on the status of that task. Sometimes the process would fail when one of the users had changed the team on the task I was looking for in code to one for which the logged-in user did not have access. I found that using the getBeans() method cannot accept the disable_row_security parameter that getBean in the BeanFactory employs. I think it’s worth noting that the $bean->[module]->getBeans() method respects the user security of the logged-in user and the php code would be blind to any beans to which the user does not have access. I didn’t find a way of overriding that with a $params statement. Therefore, using a foreach with BeanFactory::getBean($mod, $id) loop worked better in that situation.

      • 

        John, I’ve also come across the ACL/Security issue as well. At times when working with logic hooks, you need access to all of the objects regardless of the ACL Settings for the User. Typically, this is during some sort of re-assign logic where the User may not have access to the entire list view. SugarBean’s get_full_list() and create_new_list_query() both implement the ACL restrictions.

        • 

          Another option here is to offload this to a Job Queue job that can run with elevated permissions.

          • 
            Stewart Osborne May 12, 2014 at 6:32 pm

            I just ran into the security issue with getBeans() again and found my old comment. I honestly understand what you mean by offloading into a cron job and agree; however, this is much more work than simply passing a parameter into getBeans() function to disable row level security. Does SugarCRM ever plan to implement this so we can override the user-level security for getBeans() in the bean factory?

  2. 
    Stewart Osborne August 27, 2013 at 10:58 am

    In your example code, shouldn’t

    $list[contact->id] = $contact;

    be

    $list[$contact->id] = $contact;

    Please delete this comment if I’m incorrect. :)

  3. 

    Great article who helped me a lot with the customisation of my module.

    But i run into a memory leak deal…

    I’ve got a “Presence” module where peoples are coming to events, and when i have to create those presence to events.

    I attach those “presence” to targetlist of people, if i have under 400 people in the target list, it’s quite ok (still using 280mb of server memory) but if i’ve got more of 400 people in target list, i ran in memory exhausted for php…

    350mb is allowed for php (wich is huge ! for well coded program)

    i’ve tried to gc_collect_cycles(); on each iteration of my loops but it doesn’t solve any problem, since the problem come from the beanfactory itself…

    It seems than at each bean->save(); the memory isn’t cleaned up….

    Any way to force it after saving the bean?

  4. 

    I ‘ve got a custom module, where the name field repeats in records. I would like to get_full_list, but with distinct. How do I do this?

  5. 

    I hope that this post is alive!
    I have a problem to load_relationship and to create parent records.
    I created custom module, cbm00_newactivities
    In custom module i have flexrelate field.
    i have logic hook “before_save” in calls and meetings.
    The idea is to create records of custom module for every invitees in calls and meetings. It should fire when the meeting or call created or modified by the user different from the assigned user.

    I was trying to load_relationship calls_users, i checked in studio the correct name of the relationships, it matches. But received an error “Notice: Undefined property: Call::$calls_users occurred in C:\Program Files\sugarcrm-6.5.15-***…\custom\modules\Calls\CallsSaveHook.php on line 54”

    it is the line with code
    “Foreach ($bean->calls_users->getBeans() as $inuser)” at the elseif condition(); it fires when we creating new record.

    Can you guys please help me to solve this issue?
    Thanks in advance!!!

    My code for before_save logic hook class (Call entity) below.
    class CallsSaveHook {

    public function saveInviteesUsers($bean, $event, $arguments) {
    global $current_user;
    $cuser = $current_user->id;

    if ($bean->assigned_user_id != $cuser){
    $call_saved_id = $bean->id;
    $newBtd = new cbm00_newactivities();
    $newBtd->retrieve_by_string_fields(array(‘parent_id’=>$call_saved_id));

    If ($bean->isviewed_c == ‘1’)
    {
    $bean->load_relationship(‘calls_users’);
    Foreach ($bean->calls_users->getBeans() as $inuser)
    {
    $myNewActivity = new cbm00_newactivities();
    $myNewActivity->assigned_user_id = $inuser->id;
    $myNewActivity->name=$bean->name;
    $myNewActivity->description=$bean->description;
    $myNewActivity->activity_type = ‘Call’;
    $myNewActivity->parent_type = “Calls”;
    $myNewActivity->parent_id = $call_saved_id;
    $myNewActivity->parent_name = $bean->name;
    $myNewActivity->save();

    }
    $bean->isviewed_c=’0′;

    }
    elseif (($newBtd->id == null)&&($bean->isviewed_c == ‘0’)) //проверяем, если это новый звонок, и не создано вхождение новой активности, тогда создаем
    {
    //логика по новой записи или коррекции существующей. Под вопросом условие отбора массива
    $bean->load_relationship(‘calls_users’);
    Foreach ($bean->calls_users->getBeans() as $inuser)
    {
    $myNewActivity = new cbm00_newactivities();
    $myNewActivity->assigned_user_id = $inuser->id;
    $myNewActivity->name=$bean->name;
    $myNewActivity->description=$bean->description;
    $myNewActivity->activity_type = ‘Call’;
    $myNewActivity->parent_type = “Calls”;
    $myNewActivity->parent_id = $bean->id;
    $myNewActivity->parent_name = $bean->name;
    $myNewActivity->save();
    }
    }
    }

    }
    }
    ?>

  6. 

    Hello

    I am trying save a value in the database through “$bean” but it is not possible, when i execute the task, the sugar save the default value in the database.
    The default value is “PEND”, however i am informing the value “OK” according to code below, what do i doing wrong? Someone can help me?

    Follow the code that i am using:

    //Create bean and save bean
    $bean = BeanFactory::newBean(‘module name’);
    $bean->name = ‘company name’;
    $bean->import_status = ‘OK’;
    $bean->created_by= true;
    return $bean->save();

  7. 

    Hello

    I am trying save a value in the database through “$bean” but it is not possible, when i execute the task, the sugar save the default value in the database.
    The default value is “PEND”, however i am informing the value “OK” according to code below, what do i doing wrong? Someone can help me? I need so much.

    Follow the code that i am using:

    //Create bean and save bean
    $bean = BeanFactory::newBean(‘module name’);
    $bean->name = ‘company name’;
    $bean->import_status = ‘OK’;
    $bean->created_by= true;
    return $bean->save();

Trackbacks and Pingbacks:

  1. SugarCRM Developer Blog » Blog Archive » Fetching the parent record from the child record - September 2, 2013

    […] talked about navigating thru relationships using the bean methods before, but one question that comes up is if you can go both ways. In other words, the examples […]

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