SugarQuery

Overview Provides an overview of the SugarQuery class that will allow you to generate the applicable SQL for the database backend you are...

Overview

Provides an overview of the SugarQuery class that will allow you to generate the applicable SQL for the database backend you are using.

Instantiating a SugarQuery Object

To use SugarQuery, you will require the SugarQuery class and create a new object:
require_once('include/SugarQuery/SugarQuery.php');
$sugarQuery = new SugarQuery();

Building SQL Queries

The following sections will outline how to build a query using SugarQuery. All examples below ignore team security. To enable team security for your query, you can ignore passing inarray('team_security' => false) as a second parameter to the from clause.

SELECT and FROM Clauses

The SELECT and FROM can be constructed by using the select() and from() methods. The example below will select the id and name fields from the accounts module.
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id, accounts.name FROM accounts WHERE accounts.deleted = 0

WHERE Clauses

To add a WHERE clause to the query you can use the where() method. The WHERE clause below will add records where the assigned_user_id is equal to 'seed_sally_id' and where the namefield begins with the letter "I".
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add the where clause
$sugarQuery->where()

//where the assigned_user_id field is equal to 'seed_sally_id'
->equals('assigned_user_id', 'seed_sally_id')

//where the name field is starts with 'I'
->starts('name', 'I');

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id, accounts.name FROM accounts WHERE accounts.deleted = 0 AND accounts.assigned_user_id = 'seed_sally_id' AND accounts.name LIKE 'I%'

WHERE Grouped Clauses

To add grouped where clauses, you can use the where() method with queryOr() or queryAnd(). 

Grouped Or

The WHERE clause below will add records where the assigned_user_id is equal to 'seed_sally_id' or where the name field begins with the letter "I".
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add the where clause
$sugarQuery->where()

//add grouped or
->queryOr()

//where the assigned_user_id field is equal to 'seed_sally_id'
->equals('assigned_user_id', 'seed_sally_id')

//where the name field is starts with 'I'
->starts('name', 'I');
SQL Result
The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id id, accounts.name name FROM accounts WHERE accounts.deleted = 0 AND (accounts.assigned_user_id = 'seed_sally_id' OR accounts.name LIKE 'I%')

Grouped And

The WHERE clause below will add records where the assigned_user_id is equal to 'seed_sally_id' and where the name field begins with the letter "I".
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add the where clause
$sugarQuery->where()

//add grouped and
->queryAnd()

//where the assigned_user_id field is equal to 'seed_sally_id'
->equals('assigned_user_id', 'seed_sally_id')

//where the name field is starts with 'I'
->starts('name', 'I');
SQL Result
The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id id, accounts.name name FROM accounts WHERE accounts.deleted = 0 AND (accounts.assigned_user_id = 'seed_sally_id' AND accounts.name LIKE 'I%')

JOIN Clauses

To add a relationship join you can use the join() method. The example below will join accounts to contacts.
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add join
$sugarQuery->join('contacts');

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id, accounts.name FROM accounts INNER JOIN accounts_contacts jt1_accounts_contacts ON (accounts.id = jt1_accounts_contacts.account_id AND jt1_accounts_contacts.deleted = '0') INNER JOIN contacts jt0_contacts ON (jt0_contacts.id = jt1_accounts_contacts.contact_id AND jt0_contacts.deleted = 0) WHERE accounts.deleted = 0

LIMIT and OFFSET Clauses

To add a limit and/or offset you can use the corresponding limit() and offset() methods.
//add fields to select
$sugarQuery->select(array('id', 'name'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//set the offset
$sugarQuery->offset(5);

//set the limit
$sugarQuery->limit(10);

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id, accounts.name FROM accounts WHERE accounts.deleted = 0 LIMIT 5,10

GROUP BY Clauses

To add a group by , you can use the corresponding groupBy() method.
//add fields to select
$sugarQuery->select(array('account_type', 'count(account_type)'));

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add group by
$sugarQuery->groupBy('account_type');

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.account_type, count('account_type') FROM accounts WHERE accounts.deleted = 0 GROUP BY accounts.account_type

UNIONS

To add a union, you can use the corresponding union() method. The example below will join two SQL queries:

//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

//specify fields to fetch
$fields = array(
    'id',
    'name'
);

//create first query
$sq1 = new SugarQuery();
$sq1->select($fields);
$sq1->from($bean, array('team_security' => false));
$sq1->Where()->in('account_type', array('Customer'));

//create second query
$sq2 = new SugarQuery();
$sq2->select($fields);
$sq2->from($bean, array('team_security' => false));
$sq2->Where()->in('account_type', array('Investor'));

//create union
$sqUnion = new SugarQuery();
$sqUnion->union($sq1);
$sqUnion->union($sq2);
$sqUnion->limit(5);

SQL Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
(SELECT accounts.id id, accounts.name name FROM accounts WHERE accounts.deleted = 0 AND accounts.account_type IN ('Customer')) UNION ALL (SELECT accounts.id id, accounts.name name FROM accounts WHERE accounts.deleted = 0 AND accounts.account_type IN ('Investor')) LIMIT 0,5

Executing The SQL Query

Retrieving Results

To query the database for a result set, you will use the execute() method. The execute() method will retrieve the results in an array that you can iterate through. An example of fetching records from an account is below:
//fetch the bean of the module to query
$bean = BeanFactory::newBean('Accounts');

require_once('include/SugarQuery/SugarQuery.php');
$sugarQuery = new SugarQuery();

//add fields to select
$sugarQuery->select(array('id', 'name'));

//add from table of the module we are querying
$sugarQuery->from($bean, array('team_security' => false));

//add the where clause
$sugarQuery->where()
    //where the assigned_user_id field is equal to 'seed_sally_id'
    ->equals('assigned_user_id', 'seed_sally_id')
    //where the name field is starts with 'I'
    ->starts('name', 'I');

//fetch the result
$result = $sugarQuery->execute();

Result

The resulting SQL statement from the compileSql() function for the parameters above in MySQL would be:
SELECT accounts.id, accounts.name FROM accounts WHERE accounts.deleted = 0 AND accounts.assigned_user_id = 'seed_sally_id' AND accounts.name LIKE 'I%'
The execute() function will return an array of results that you can iterate through:
Array
(
    [0] => Array
    (
        [id] => f39593da-3f88-3059-4f18-524b4d23d07b
        [name] => International Art Inc
    )
)

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor Aenean massa.Aenean commodo ligula eget dolor Aenean massa.

You Might Also Like

0 comentarios

Flickr Images