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

bsoremsugar —  March 23, 2012 — 115 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.

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

  1. 
    Cedric Mourizard March 23, 2012 at 7:06 am

    To delete a relation, the good code isn’t that ?

     
    $focus =
    new Account();

    $focus->retrieve(‘my
    account id’);

    $focus->load_relationship(‘contacts’);

    $focus->contacts->delete($focus->id, ‘my contact id’);

    $focus->save();
     
    The first argument the focus bean and the second argument should be the related id. If you omit the second argument you will remove all related bean for a many–o-many relation.

  2. 

    Again, nice to see these examples written out together in a single place. I think developers newer to the SugarCRM system will really find this post useful. It took me some time to assemble these techniques.

  3. 

    You missed another reason for using the Beans… all logic_hooks continue to function!

    • 

       I covered that with this point above:

      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.

  4. 

    Great post.  I’m definitely guilty of using straight SQL.  Matthew Poer’s comment was spot on.

    One other benefit… (at least I have been assuming)… is you’re protected from malicious SQL injection attacks as the bean will sanitize the data for you.  For example, when accepting information POSTed through an ajax call you don’t have to remember to sanitize each thing passed in.

    // So, this is safe right?
    $myCallBean->description = $_POST["description"]; 
    $myCallBean->save();

    Related Cartoon From XKCD: http://imgs.xkcd.com/comics/exploits_of_a_mom.png

  5. 

    This is a post that any new and existing Sugar dev should read. Great summary of common use-cases. Thanks Jeff and John!

  6. 

    A suggestion/request I’ve been meaning to make for a long time is to have an optional parameter to the retrieve method that will load a tiny bean for me. something like:

    $bean = new Contact();
    $bean->retrieve(‘the_contact_id’, true); 
    // where the retrieve method looks something like: function retrieve($id, $tiny = false)

    This would load ONLY the bean values into my variable and would preferable do so with the minimum queries.When I’m working on a project that needs to deal with 100s or even 1000s of beans in an operation I simply cannot justify using the ‘best-practices’ approach. Direct SQL is simply too much faster in these cases. 

    If there were a way to enable that option I would completely change my opinion on this :-)

    • 

      Try this…

      $bean = new Contact();
      $beanValues = $bean->retrieve(‘the_contact_id’)->toArray();

      You want to make sure you are going thru retrieve any time you are getting record data, as some modules will need to massage the field data coming back to you so you can work with it correctly. But the above approach should at least save an extra step.

      • 

         You could also do something like that to disable loading or relation and security filter in some cases that could be use but do not use that if you want to manipulate and save you $bean later :

        bean = SugarModule::get(‘Contacts’)->loadBean()
        $bean->disable_row_level_security = true; // disable security if you want
        $bean->relDepth = 99; // disable relationship loading
        $bean->retrieve(‘the_contact_id’);

        • 

          Thank you to both of you for your advice!

          I’ll need to come back to these options next time I have a project involving a high number of records being manipulated in a loop.

          Cedric – Are you saying that by using toArray() I won’t have access to the save() method later on? I guess that makes sense.

          In my first comment I left out the need to still be able to call save() on the bean, my bad.

          Thanks again to both of you!

          • 

            Well, you can repopulate your bean like this:

            $bean = new Contact();
            $beanValues = $bean->retrieve(‘the_contact_id’)->toArray();

            $beanValues['name'] = ‘My record name';

            $bean->fromArray($beanValues);
            $bean->save();

            • 

              Thanks John for the nice tutorial
              I have a doubt regarding the code

              Just assume the situation that i need only the data part of the bean for refer and calculate purpose, no other function or method anything
              so I would use the following piece of code

              $bean = new Contact();
              $beanValues = $bean->retrieve(“contact_id”)->toArray();

              Now my doubt is, in this piece of code also a complete bean object is created, consuming the memory space, leading to situation of memory overhead that I would like to evade.

              Can you please suggest a way out of this situation.
              And some similar solution for get_full_list, as in most of my past cases I only needed the data part for refer and calculate purpose, sometimes from as much as 300 records in a go, and I can not afford such a big Bean array, therefore forced to use sql.

              Please suggest a way around it.

              Thanks In Advance

              • 

                The key is to make sure you cleanup the memory afterwards, either by manually unsetting the $bean object, or letting it go out of reference if it’s in a loop. PHP 5.3+ is quite proactive here in making sure that GC occurs.

  7. 

     In order for $bean->retrieve(‘id’) to work in an independent script, I found that having defined a current user is needed :
    $GLOBALS['current_user']=new User();
    $GLOBALS['current_user']->retrieve_by_string_fields(array(‘user_name’ => ‘the_user_name’));

    • 

      Yes, great point Ben. Note that if you route this via the normal MVC or thru the entrypoint you don’t need to add this.

      • 

        Hi,
        You’re welcome. This has bothered me for weekds before I found out what was wrong.Could you give an example about how to use the MVC with this kind of operations ?

    • 

      You can also use:

      $GLOBALS['current_user']->getSystemUser();

      If you just want to load the admin account.

    • 
      Michael Joyner May 27, 2012 at 9:56 pm

      I am using a CRON job to automate daily TASK creations. Its a valid entry point, but I dont have a :

      $GLOBALS['current_user']->getSystemUser(); 

      It seems to be working, I don’t know how it will do when I put it in crontab. If this program is run from the linux CLI, do I need the above?

      • 

        As I seaid, you need a current user in order to use the ‘retrieve’ method. If you only create new records, I don’t think you need a current user. Note, though, that the records you’ll create won’t have a ‘created by’ user. And setting relationships may not work.
        Setting a current user doesn’t cost much in terms of performance, and in my opinion, it’s a good way to do things.

        • 
          Michael Joyner June 5, 2012 at 5:12 pm

          Ben, 
          I am retrieving without $GLOBALS['current_user']->getSystemUser(); 

          I just ran the program from the CLI and it all worked even getting related beans.

          I would of thought that it shouldnt.

  8. 

    Helo Guys,

    I need 40 fields to retrieve from a related record in onother table. These 40 fields i prefer to make as as non-db fields availabale,  because every one of these fields ( non-db) servesa as criteria for the visibility on the current form. Can I use these 40 non-db fields usable in the formula builder. ( sugar 6.4 ) 

    • 

       Yes, they are standard fields from the bean perspective and can be accessed and edited here. And when you make changes, it will kick off the formulas in the background as well on save; same goes for Logic Hooks and Workflows.

      • 

        in my case there will be only one reord at a time. I assume all these 40 fields will be in the $focus record. So I have to move them to the fields on the current form wich are deciding the visibility of the other boxes. Shoul I do this with a logic hook ? Or do you have another solution ?

  9. 

    Grabbing a record :
    How would you do this if you have multiple columns on the WHERE Clause
    E.g.:
    SELECT COUNT(*) AS NumRecs FROM inventory WHERE inv_date      = ‘2012-05-01′  .                    AND     inv_resort    = 1 .                    AND     tour_type    = 2

    Thank you for your time and help.

  10. 

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

    • 

      Which version of Sugar?

    • 

      I’m having the same issue with Meetings to Contacts.
      $wMeetingFocus = new Meeting();
      $wMeetingFocus->retrieve($bean->idb);
      $wMeetingFocus->load_relationship(“contacts”);
      $wMeetingFocus->contacts->delete($wMeetingFocus->id, $bean->ida);

      This does not work.

      • 

        This appears to have to do with the database. If a row already exists where deleted = 1 for the specified relationship, then the not-deleted row is not deleted.

        There is another bug where if the meeting has a single contact related to it, it will not delete that relationship. In the database, it sets the record to deleted = 1 and then for some reason adds a new identical relationship that is not deleted.

        http://www.sugarcrm.com/support/bugs.html#issue_64811

  11. 

    Thanks John for such a great tutorial. Could you share a link or more tutorial on this topic where we can get all the method and library on module to get the data and update them with their relationship. Waiting for response.Hrishikesh Mishra

  12. 

    How can I check a record exists or not?

    For example.

    $recordID = “78787878”  //Non-existing recordID
    $focus = new Account();
    $focus->retrieve($recordID);

    How can I check that there is no record exists for account record id  78787878 ?

    Waiting for Response.

    Hrishikesh Mishra
           

  13. 

    Hi to All,

    I have facing the following problems,

    Suppose I have two modules

    (a) Ordermaster => To store the order master detail.
    (b) Orderitem => To store order items detail

    And there is one-to-many (One:Ordermaster  — Many:Orderitems) relationship between them and relationship name is: orderamaster_orderitem

    Now, suppose I load the order master data based on record Id
    e.g.

    $focus = new Ordermaster();
    $focus->retrieve($recordId);

    Now want to load the order item relationship with following code.

    $focus->load_relationship(‘orderitem’);

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

    But above code is not working, I am getting error that call of getBeans on non-object.

    If I change the following code
    $focus->load_relationship(‘orderitem’);  with  $focus->load_relationship(‘ordermaster_orderitem’);

    And
    foreach ($focus->ordermaster_orderitem->getBeans() as $orderItem) {

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

    }

    then it work. But I don’t know why such things happening. Because in above tutorial we are using the contacts relationship not account_contact relationship.

    Second things, I want to check whether the child items exists or not.

    Waiting for Response
    Hrishikesh Mishra

    • 

       I think the issue you are seeing is because of the naming of the relationship. In my example above it was named ‘contacts’, but in custom modules it tends to be named ‘module1_module2′ as you saw.

      As for your third thing, don’t you pretty much have that already in your code example? The $orderItem being returned is a record bean for the orderitem module.

    • 

      Which version you are using? I am using SugarCE 6.2.4 and its not working for me.

  14. 

    Hi to All,

    I have added the custom fields to account module through studio like last_name_c. During insert or update operation through Save method the value of this field in accounts_cstm not saved. 

    Please help.

    Hrishikesh Mishra
     

  15. 

    Great post, thanks John.  I refer back often.

    I’m having an issue getting this to work with nulls.  In my code I say:
    $focus->retrieve_by_string_fields(array(‘level_3′ => null, ‘level_2′ => null, ‘level_1_c’ => $level1_c));

    Running a debugger i see this returns the where clause of “WHERE level_3 = ” AND level_2 = ‘Texas’ AND level_1_c = ‘United States’ AND deleted=0″ which does not find the record because it is expecting nulls.  Is there a way to do search for nulls?

  16. 

    Hi,

    When I try to use this method, I get the error:

    PHP Fatal error:  Call to a member function retrieve() on a non-object

    Any idea what i could be doing wrong?

    Regards

    • 

       This post: http://cheleguanaco.blogspot.com/2012/07/logic-hooks-odds-and-ends.html suggests that this is because I am using this method in a ‘before_save’ logic hook. If this is the case, is there a similar method I can use at the ‘beofr_save’ stage of the process?

    • 

       Can you show your entire code?

      • 

         Thanks… Essentially:

        retrieve($bean->netsubscription_id);
                //…… other stuff which is not run because a FATAl error is thron on the line above ……
            }
            //other hook functions below (all using the same method
        }
        ?>

        //For the record, I ended up creating a function to retrieve a specific database row in order to just get it to work… not ideal, I know, but i needed to make it work… I still want to use Sugar bean methods and functions for the many resons you outlined above, so any help you offer would be appreciated.

        Thanks,

        JTM

        • 

           something happened on the way to heaven… trying to repost:

          $GLOBALS['log'] = LoggerManager :: getLogger(‘SugarCRM’); //create the logger
          $GLOBALS['current_user']->getSystemUser();
          require_once(‘data/SugarBean.php’);
          require_once(‘include/utils.php’);
          require_once(‘modules/Accounts/Account.php’); //include your module class
          require_once(‘modules/Devices/Device.php’); //include your module class
          require_once(‘modules/DeviceIPs/DeviceIP.php’);
          require_once(‘modules/Routes/Route.php’);
          require_once(‘modules/ClientLocations/ClientLocation.php’);
          require_once(‘modules/MPLSClients/MPLSClient.php’);
          require_once(‘modules/NetSubscriptions/NetSubscription.php’);
          require_once(‘modules/NetServices/NetService.php’);

          class MPLSProvision {
             
              function updateDevice (&$bean, $event, $arguments) {
                  //get subscription details
                  $subscription = new NetSubscription();
                  $subscription->retrieve($bean->netsubscription_id);
                  //other stuff which does not run because a FATAL error is thrown on line above
              }
          }

  17. 

    Concerning raw SQL, there are times when a non-Bean approach is better. For example, during the JJWDesign Google Maps project, I quickly realized that a bean by bean approach would end up exceeding allowed memory. Even the create_new_list_query() and process_list_query() methods would sometimes exceed memory. So, for this project some raw SQL queries provided a more memory efficient approach. Of course, I still use the $db object with escaping.

    • 

      It would be great if you could illustrate those cases so we could work with you on making the needed API changes to support this.

      • 

        Sugar OnDemand maximum allowed queries of 1000 was one big issue that I’ve hit. If I’m mapping 10,000 records on a map, I must be very careful with method calls.

  18. 

    Hi

    Thanks for your article as i found it very helpful.

    Is there a way to perform a search using the bean method?

    For e.g there might be more than one person with the same last_name and I maybe want to list them in a array().

    Thanks in advanced

  19. 

    Hi john, that really is a great post!

    i need some help on logic. With all these methods, i’m totally lost.
    I use Advanced OpenSales and did a logic hook on AOS_Quotes module to add products depending on the duration of a rent.

    My products to add are displaying under 2 differents category. Say Cat01 and Cat02. These categories are added by a custom fields and are stored into aos_products_cstm table.

    Each category contains 6 products named ‘Rent1Month’, ‘Rent1Week’, ‘Rent1Day’, ‘Rent1MonthMore’,’Rent1WeekMore’ and ‘Rent1DayMore’. These ones are stored under aos_products table

    I would like to get the price of each product, and then, depending of a rent duration entered by the user into a new Quote, add products line in this (current) Quote. these products are related to aos_products table by a relationship aos_products_quotes. So, normally, when i want to add a product by the user interface, I have to search into AOS_Products module.

    Here is an example :
    0 – retrieve products to get prices (without displaying them)
    1 – user enter rent duration (let’s say 1Month, and 1week more)
    2 – calculation of rent packages : Package of prices sum(Rent1Month + Rent1WeekMore) or Package of Rent1Month + Rent1MonthMore.. (it is rent logic)
    3 – depending on wich package is the more advantageous, we add the right products for each category. Let’s say here the best package is Rent1Month + Rent1WeekMore,
    we should add Cat01 – Rent1Month lines, Cat01 – Rent1WeekMore lines, and Cat02 – Rent1Month lines with Cat02 – Rent1WeekMore lines.

    For 3, i could test to do $bean->myProductLineName = ‘Rent1Month'; etc… but to be in the database, it should be done before_save…

    So, how can i translate this logic with $record->retrieve, or get_list, or add ?

    4th question : for adding a product to quote, i should do :

    $focus = new AOS_Quotes();
    $focus->retrieve(‘my quote id’);
    $focus->load_relationship(‘aos_products_quotes’);
    $focus->aos_products_quotes->add(‘my product id’);
    $focus->save();
    But what about all my fields? product id, notes, etc.. ?
    do i have to add them like this :??

    $focus->aos_products_quotes->add(‘my product id’,’my product name’, ‘my product date entered’);

    Sorry for the very long post, hope you could help me and hope i’m not boring… Thanks by advance for your time!

  20. 

    Hi,
    How do I rollback db save in case of any error?

  21. 

    just in case anyone else has problems loading relationships with the above code in custom modules:

    the line
    $focus->load_relationship(‘contacts’);
    didn’t work for me.

    however, since the name of the relationship was something like “myproject_contacts” this one worked:
    $focus->load_relationship(‘myproject_contacts’);

  22. 

    I had some trouble programmatically getting the relationship name to use in the $focus->load_relationship(‘relationship_name_here’) until I found function findRelationship($from,$to) in modules/Leads/views/view.convertleads.php that given the two beans in question, returns the appropriate relationship name to use in the load_relationship. If anyone has a more efficient method than looping through all the fields as this function does please do share.

  23. 

    I simplified the lookup to get the relationship name from two module names as follows:

    function getRelationshipByBeans ($m1, $m2){
    global $db,$dictionary,$beanList;
    $rel = new Relationship;
    if($rel_info = $rel->retrieve_by_sides($m1, $m2, $db)){
    $class = $beanList[$m1];
    $bean = new $class();
    $rel_name = $rel_info['relationship_name'];
    foreach($bean->field_defs as $field=>$def){
    if(isset($def['relationship']) && $def['relationship']==$rel_name) return(array($def['name'], $m1));
    }
    }elseif($rel_info = $rel->retrieve_by_sides($m2, $m1, $db)){
    $class = $beanList[$m2];
    $bean = new $class();
    $rel_name = $rel_info['relationship_name'];
    foreach($bean->field_defs as $field=>$def){
    if(isset($def['relationship']) && $def['relationship']==$rel_name) return(array($def['name'], $m2));
    }
    }
    return(FALSE);
    }

  24. 

    Great post! very useful….

    Anyway, I am dealing with a problem over the opportunity close date format when using this in an after_retrieve logic hook, and using $bean->save() to end the process; I am getting 18497 “deprecated” error in the log (related to the format of the close date. The date is not manipulated at all in my logic. The custom format for date is dd/mm/yyyy, and when opening the record for editing the next time, is presented as yyyy-mm-dd If I use the same code in a “before_save” hook without $bean->save() everything goes fine.

  25. 

    I’m trying … how do you replace the following query using this method?

    select * from accounts where name like “%Foo%”;

    • 

      Based on the solution from @twitter-126485906:disqus below…

      $bean = BeanFactory::getBean(‘Accounts’);
      $contact_list = $bean->get_list(“”, “accounts.name LIKE ‘%Foo%'”);

      You can then iterate over the $contact_list for the array of beans returned matching the list.

  26. 

    In your example:

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

    This essentially does a SQL query where the WHERE clause is:

    WHERE name = ‘Foo Bar’

    Right? What if I wanted to check ISNULL? For instance, I want my WHERE clause to be:

    WHERE ISNULL(name, ”) = ‘Foo Bar’

    Is this possible? Thanks!

    • 

      You could use the get_list method like this…

      $bean->get_list(“”, “ISNULL(name, ”) = ‘Foo Bar'”);

      Note that this returns an array of beans, and not just the first one.

      • 

        Very cool! Thanks! I’m trying to find one location that kind of outlines all the various Sugar methods that you can use. Happen to know of such a place?

  27. 
    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?

  28. 
    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. :)

  29. 

    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?

  30. 

    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?

Trackbacks and Pingbacks:

  1. Write PHP code to access custom module - Page 2 - March 28, 2012

    [...] [...]

  2. SugarCRM Developer Blog » Blog Archive » New for Sugar 6.5: Grab a filtered list of related record to the current record using beans - April 26, 2012

    [...] remember this blog post from a few weeks back, where we talked about how to add, edit, and navigate beans using the bean [...]

  3. get all related Beans to a Bean - May 7, 2012

    [...] [...]

  4. Logic hook to create call to each contact of an account - June 13, 2012

    [...] [...]

  5. Best way to get field values from other modules? - June 19, 2012

    [...] [...]

  6. Populate Custom Module via Results of Custom Job - June 22, 2012

    [...] [...]

  7. Logic hook not saving relationship between Email module and Opportunity module - June 22, 2012

    [...] [...]

  8. Database Question - Module Development - June 27, 2012

    [...] [...]

  9. Adding a new entry on related module automatically - July 6, 2012

    [...] [...]

  10. Logic Hooks - Retrieve Data From Another Table - Version 6.2.0 - July 25, 2012

    [...] [...]

  11. Insert a new row in a module: can it be done with beans? - August 23, 2012

    [...] [...]

  12. Create a new Entry using Backend php code - September 11, 2012

    [...] [...]

  13. How to clear a $bean->field_name variable - October 11, 2012

    [...] [...]

  14. Using sugar bean instead of sql : WHERE & AND conditions - October 26, 2012

    [...] 5.1.2 package on archlinux) I try to use sugar bean in a logic_hook instead of SQL, and i've read this wonderful blog entry by jmertic What i want to do is to retrieve all the fields from two tables for a certain record [...]

  15. Simple get Bean : How to do Select MYTABLE.name from MYTABLE - October 30, 2012

    [...] 'name' from my table aos_products with sugar bean… I saw a lot of blog's posts, like this one : HOWTO: Using the bean instead of SQL all the time., but i don't understand how you could just do [...]

  16. adding a record by SQL : auto-increment ID - November 2, 2012

    [...] [...]

  17. SugarCRM Developer Blog » Blog Archive » Getting a set of beans by conditions without SQL - November 12, 2012

    [...] remember here on this very developer blog, we’ve been evangelizing developers to move away from using custom SQL statements to the built in bean methods provided by Sugar instead. One big hole was the ability to get a set of bean objects based on a condition, a common request [...]

  18. Add records to database using custom textboxes in editiew of a modules - November 26, 2012

    [...] [...]

  19. Sending email to selected Account from Cases in logic hooks - December 3, 2012

    [...] my problem that I fixed now. I want to send email to selected Account's email id. I referred to John's blog and found a solution. [...]

  20. SugarCRM Developer Blog » Blog Archive » Get the Primary Email Address for a record using the bean - December 26, 2012

    [...] always like to see people leveraging articles such as this one to better explore all the SugarBean model layer provides the developer, in the process helping them [...]

  21. Edit existing tasks via logic hooks - Page 2 - April 2, 2013

    [...] [...]

  22. Generate URL with {fields} from Accounts and User module - April 8, 2013

    [...] [...]

  23. Delete multiple rows using bean? - May 22, 2013

    [...] [...]

  24. SugarCRM Developer Blog » Blog Archive » Programmatically Find the Name of the Relationship between two Modules - May 29, 2013

    [...] often find myself referencing Jeff Bickart’s “HOWTO: Using the bean instead of SQL all the time.” [...]

  25. 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