SugarCRM SugarBean

Sugar has its own built-in ORM, called the SugarBean.  Sort of a neat name that is thrown about frequently when discussing software development in Sugar.  For those of you familiar with PHP Object relational mappers, like Doctrine2 or Propel, the SugarBean will be easily understood.  For those of you unfamiliar with ORM's, let me take a slight digression to explain.

ORM's convert SQL statements into objects.  Before ORM's, the way to get information from the database was to open a database connection, create an SQL query, run the SQL query, which brought back an SQL object with the needed data, then you had to run a PHP fetch command to get the data out of the database object, and save it as an array or variable to be used in your code.  Many steps, all fraught with the potential to create errors.  The database runs on one system, PHP on another.  This is what is known as "Object-relational impedance mismatch."

ORM's were developed to help with this problem, and cut down on some of the coding labor.  The ORM software connects to the database, and makes PHP objects of tables and fields in the database.  Thus, all the developer needs to do is to instantiate a new ORM object in PHP, and then point to the required data, no SQL, and no fooling with the database, everything stays on the PHP side of the house.  If you would like to go a little deeper than this, see my ORM example from a previous article.

The SugarBean is an ORM.  So, how do we use the SugarBean ORM?  The SugarBean works hand-and-hand with Sugar modules.  It will create, retrieve, save, and delete information from the database for a particular module.  When you think about it, this makes a lot of sense, since each module in Sugar has its own set of tables in the database.

To use the SugarBean, you first want to create a SugarBean object based on a particular module.

Before version 6.3 this was done, like so,

$acctBean = new Accounts();

After version 6.3, you could use a factory, like so,

$acctBean =  BeanFactory::getBean($accounts);

Either method still works, I prefer the first, probably because its less typing.  The object created contains the entire accounts and accounts_cstm table information for that module with this one call.  Once you instantiate the object you can use it to do all your database CRUD.  Let's take a look.

READING INFORMATION FROM THE DATABASE

If you want, for example, a list of all the names in the account table, you would do this,

$namearr = array();
$namearr = $acctBean->get_full_list('name');

If you wanted to get all the accounts with the same name, and search for a particular name,

$bobarr = $acctBean->retrieve_by_string(array('name' => 'Bob'));

If you wanted to just bring back one record in the table for a particular id, and then get the name for that account record,

$acctBean->retrieve('the record id');
$name = $acctBean->name;

And so on for any field in the accounts, or accounts_cstm table.  Sugar has a whole list of methods that work with the SugarBean to make your development life easier.  These methods can be found in the API documentation, under SugarBean.

UPDATING THE DATABASE

Suppose we wanted to change, or update, a particular database record with new data

$acctBean->retrieve('the record id');
$acctBean->name = 'Tom';
$acctBean->city = 'Baltimore';
$acctBean->save();

That's it.

DELETE A RECORD

Sugar does not delete anything, ever.  Every table in the database has a "deleted" field, when set to 1 the record is considered deleted.

$acctBean->retrieve('the record id');
$acctBean->mark_deleted(); 
$acctBean->save();

CREATE A NEW RECORD

Sugar has a unique id for every record in the database, no matter what module your in, each record has a unique id.  Sugar has provided us with a method, create_guid(), to create a unique id for new records.

$dbid = create_guid();

If you wanted to use the SugarBean to do an insert, or create a new database record, you would do something like this.

$acctBean->id = $dbid;
$acctBean->name = 'Dale';
$acctBean->city = 'Atlanta';
$acctBean->save();

There is one other trick that is needed from time to time. If your using a before_save hook, for example, how do you tell if a record has been created?  What's its id?  Well, before the record is actually saved, the new record id is gotten by Sugar with the create_guid() method, and it is stored in $this->bean->id.

You can check the database using retrieve, and if the value returns empty, the record has not yet been created.  Let's take a look.  I'll start putting all the commands down so you can see the sequence better.

$acctBean = new Accounts();
$checkRecord = $acctBean->retrieve($this->bean->id);
if ( ! empty($checkRecord)) {
   // the record is in the database
} else {
    echo "if you are using a before_save logic hook this will most likely be the one to get executed, because the record has not been included in the database yet ";
}

Here's another way to get the id of a newly created record.

if (!empty($this->bean->fetched_row['id'])) {
    // record is new
}

Or yet another to check for the elusive record

if (!is_array($this->bean->fetched_row)) {
   // record is new
}

RELATIONSHIPS

This is all well and good, but what about modules that have relationships with other modules?  In Studio, when you create a relationship with another module, other tables are created in the database to track that relationship, called join tables.  We can check these relationships out with the SugarBean.  Let's say accounts has a relationship to the contacts module, where many contacts belong to one account.  How do we access contact information that relates to one account?

$acctBean = new Accounts();

$acctBean->load_relationship('contacts');

Now we want to get the contact information related to one account.

$acctBean->retrieve('the record id');  //for the account you want

$relatedBeans = $acctBean->contacts->getBeans();

Or

$name = $acctBean->contacts->name

Suppose we want to get all the contact records related to the one account

$acctBean->retrieve("the id of the account you're interested in");
$acctBean->load_relationship('contacts');
$list = array();
foreach ($acctBean->contacts->getBeans() as $aContact) {
   $list[] = $aContact
}

There's one other advantage to using the bean.  Sugar is database agnostic.  It works with MySQL, Microsoft's SQL Server, and the Oracle database.  All the above commands work, regardless of what database your using, which would not be the case, if you were writing SQL queries.

Finally, if your curious how the bean is defined, how the bean knows each module's fields, and how it gets related data from other modules, all the individual modules field properties, definitions, and relationships are defined in a large associative array located in vardefs.php at the top directory level for each module.  So when you say "$acctBean = new accounts;" Sugar opens up the vardefs.php file and loads the field information needed for the bean to get the needed information from the database.

Pretty slick.  Did you notice that with all these statements, there was not one mention of SQL, and that's the beauty of an ORM.  Now, you pay for that with performance, but usually that is a minor consideration, unless, of course, the application gets big, and if it does, maybe you should consider using something other than Sugar for your application.

Comments are closed.