DBManager

Overview

The DBManager Object provides an interface for working with the database.

Instantiating the DBManager Object

The DBManagerFactory class, located in ./include/database/DBManagerFactory.php, can help instantiate a DBManager object using the getInstance() method.

  $db = \DBManagerFactory::getInstance();

For best practices, we recommend using the global DBManager Object:

  $GLOBALS['db']

Querying The Database

Sugar supports prepared statements. The following sections outline the legacy usage and the newly prepared statement usage.

SELECT queries

For select queries that do not have a dynamic portion of the where clause, you can use the query() method on the DBManager object. For queries that are accepting data passed into the system in the where clause, the following examples demonstrate how best utilize the new Prepared Statement functionality.

Legacy:

  $id = '1234-abcde-fgh45-6789';
$query = 'SELECT * FROM accounts WHERE id = ' . $GLOBALS['db']->quoted($id);
$results = $GLOBALS['db']->query($query);

Best Practice:

Use the getConnection() method to retrieve a Doctrine Connection Object which handles prepared statements.

  $id = '1234-abcde-fgh45-6789';
$query = 'SELECT * FROM accounts WHERE id = ?';
$conn = $GLOBALS['db']->getConnection(); 
$result = $conn->executeQuery($query, [$id]);

In the case that query logic is variable or conditionally built then it makes sense to use Doctrine QueryBuilder directly.

Legacy:

  $query = 'SELECT * FROM accounts'; 
if ($status !== null) {
	$query .= ' WHERE status = ' . $GLOBALS['db']->quoted($status);
}
$results = $GLOBALS['db']->query($query);

Best Practice:

Use the getConnection() method to retrieve the Doctrine Connection Object, and then use the createQueryBuilder() method on the Connection Object to retrieve the QueryBuilder Object.

  $builder = $GLOBALS['db']->getConnection()->createQueryBuilder();
$builder->select('*')->from('accounts');
if ($status !== null) {
	$builder->where('status = ' . $builder->createPositionalParameter($status));
}
$result = $builder->executeQuery();

Retrieving Results

Legacy:

After using the query() method, such as in the Legacy code examples above, you can use the fetchByAssoc() method to retrieve results. The query() method will submit the query and retrieve the results while the fetchByAssoc() method will iterate through the results:

  $sql = "SELECT id FROM accounts WHERE deleted = 0";
$result = $GLOBALS['db']->query($sql);

while($row = $GLOBALS['db']->fetchByAssoc($result) )
{
    //Use $row['id'] to grab the id fields value
    $id = $row['id'];
}

Best Practice:

When using Prepared Statements, both the Doctrine Query Builder and the Doctrine Connection Object will return a Doctrine\DBAL\Result Object to allow iterating through the results of the query. You can use the fetchNumeric() , fetchAssociative() or fetchAllNumeric() or fetchAllAssociative() methods to retrieve results.

fetchAllAssociative() Example

The fetchAllAssociative() method will return the entire result set as an associative array, with each index containing a row of data.

  $conn = $GLOBALS['db']->getConnection();
$result = $conn->executeQuery("SELECT * FROM accounts");
foreach ($result->iterateAssociative() as $row) {
    $name = $row['name'];
    // do something
}

iterateAssociativeIndexed() Example

The iterateAssociativeIndexed() method executes the query and iterate over the result with the key representing the first column and the value is an associative array of the rest of the columns and their values:

  $query = 'SELECT id, name FROM accounts';
$conn = $GLOBALS['db']->getConnection();

foreach ($conn->iterateAssociativeIndexed($query) as $id => $data) {
    // ...
}

Retrieving a Single Result

To retrieve a single result from the database, such as a specific record field, you can use the getOne() method for Legacy query usage.

  $sql = "SELECT name FROM accounts WHERE id = :id";
$conn = $GLOBALS['db']->getConnection();
$row = $conn->fetchAssociative($sql, ['id' => $id]);

// alternative
$sql = "SELECT name FROM accounts WHERE id = '{$id}'";
$name = $GLOBALS['db']->getOne($sql);

Limiting Results

To limit the results of a query, you can add a limit to the SQL string or for legacy query usage you can use the limitQuery() method on the DBManager Object:

Legacy:

  $sql = "SELECT id FROM accounts WHERE deleted = 0";
$offset = 0;
$limit = 1;

$result = $GLOBALS['db']->limitQuery($sql, $offset, $limit);

while($row = $GLOBALS['db']->fetchByAssoc($result) )
{
    //Use $row['id'] to grab the id fields value
    $id = $row['id'];
}

Prepared Statements:

When using the Doctrine Query Builder, you can limit the results of the query by using the setMaxResults() method.

  $builder = $GLOBALS['db']->getConnection()->createQueryBuilder();
$builder->select('*')->from('accounts');
if ($status !== null) {
	$builder->where('status = ' . $builder->createPositionalParameter($status)); 
}
$builder->setMaxResults(2);
$result = $builder->executeQuery();

INSERT queries

INSERT queries can be easily performed using DBManager class.

Legacy:

  $query = 'INSERT INTO table (foo, bar) VALUES ("foo", "bar")';
$GLOBALS['db']->query($query);

Best Practice:

  $fieldDefs = $GLOBALS['dictionary']['table']['fields'];
$GLOBALS['db']->insertParams('table', $fieldDefs, ['foo' => 'foo','bar' => 'bar']);

UPDATE queries

When updating records with known IDs or a set of records with simple filtering criteria, then DBManager can be used:

Legacy:

  $query = 'UPDATE table SET foo = "bar" WHERE id = ' . $GLOBALS['db']->quoted($id);
$GLOBALS['db']->query($query);

Best Practice:

  $fieldDefs = $GLOBALS['dictionary']['table']['fields'];
$GLOBALS['db']->updateParams('table', $fieldDefs, ['foo' => 'bar',], ['id' => $id]);

For more complex criteria or when column values contain expressions or references to other fields in the table then Doctrine QueryBuilder can be used.

Legacy:

  $query = 'UPDATE table SET foo = "bar" WHERE foo = "foo" OR foo IS NULL';
$GLOBALS['db']->query($query);

Best Practice:

  $query = 'UPDATE table SET foo = ? WHERE foo = ? OR foo IS NULL';
$conn = $GLOBALS['db']->getConnection();
$rowCount = $conn->executeStatement($query, ['bar', 'foo']);

Generating SQL Queries from SugarBean

To have Sugar automatically generate SQL queries, you can use the following methods from the bean class.

Select Queries

To create a select query you can use the create_new_list_query() method:

  $bean = BeanFactory::newBean($module);

$order_by = '';
$where = '';
$fields = array(
    'id',
    'name',
);

$sql = $bean->create_new_list_query($order_by, $where, $fields);

Count Queries

You can also run the generated SQL through the create_list_count_query() method to generate a count query:

  $bean = BeanFactory::newBean('Accounts');

$sql = "SELECT * FROM accounts WHERE deleted = 0";

$count_sql = $bean->create_list_count_query($sql);