Post originally written by Jim Bartek.
UPDATE October 3rd 2017Removed references to compileSql() function which was deprecated and removed in Sugar 7.9.
You have found yourself in a bind, and you need to query the database directly. There is no other recourse than to write a query to get the data you need. This cookbook entry is going to give you some examples on how to use our new SugarQuery API instead of direct SQL.
1. What is SugarQuery?
SugarQuery is a SQL query builder for retrieving data directly from the database. It is used extensively within the core of the application. For instance, the FilterAPI uses it.
It uses a bean, the beans relationships, and visibility models to build a SQL query that can be used to retrieve data.
2. The Basics
SugarQuery has a very simple interface for building queries.
The basic methods you will need to create a query are:
- select($fields) - accepts an array of fields you would like to select
- from($bean) - validates the query against a SugarBean at generation
- where() - creates an AND where object
- orWhere() - creates an OR where Object
- join($link) - accepts a link from the from bean to create the correct join(s)
- orderBy($field, $direction) - accepts a field and a direction to sort upon
- groupBy($field) - accepts a field to group by
- having($condition) - accepts a condition (see below)
There is also conditions that can be used when building out your query. This conditions can be used with the where and having.
To execute a query you call execute() on the object. If you would like to see the sql it generated you can call $query->compile()->getSQL() and $query>compile()->getParameters().
The execute() method by default returns an array of the records selected. You may also choose to return the db result object execute('db') or return as json execute('json').
Lets try a simple example of selecting all Accounts that have an industry of 'Media'.
sugarquery-accounts.php
<?php
$query = new SugarQuery();
$query->select(array('id','name'));
$query->from(BeanFactory::getBean('Accounts'));
$query->where()->equals('industry','Media');
$results = $query->execute();
Now lets roll through some examples.
3. Joins
Let's now get all the contacts on these media accounts.
sugarquery-acconts-contacts.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$contacts = $query->join('contacts')->joinName();
$query->select(array("$contacts.full_name"));
$query->where()->equals('industry','Media');
$results = $query->execute();
4. N-Deep Joins
Lets say you need to get all media industry accounts, contacts, that are attached to closed opportunities. Why? Because you can!
sugarquery-n-deep-joins.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$contacts = $query->join('contacts')->joinName();
$opp = $query->join('opportunities', array('relatedJoin' => $contacts))->joinName();
$query->select(array("$contacts.full_name", "$opp.name"));
$query->where()->equals('industry','media');
$query->where()->equals("$opp.sales_stage", 'closed');
$results = $query->execute();
As you can see, you can prefix a field with the link name, and when the SQL is generated it will replace those with the system generated aliases.
5. But I want my own aliases!
So you want to alias things yourself? Alright! You can set aliases everywhere!
sugarquery-aliasing.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select(array(array('industryAccounts.first_name', 'fname'), array('industryContacts.last_name', 'lname'), 'contactsOpportunities.name'));
$query->where()->equals('industryAccounts.industry','Media');
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$results = $query->execute();
6. Order the madness
Now you have this data, but you want to sort it, make it make some sense. Thats EASY!
sugarquery-orderby.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select(array(array('industryContacts.first_name', 'fname'), array('industryContacts.last_name', 'lname'), 'contactsOpportunities.name'));
$query->where()->equals('industryAccounts.industry','Media');
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$query->orderBy('industryContacts.name', 'DESC');
$results = $query->execute();
7. Groups!
Time to group by!
sugarquery-groupby.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select()->setCountQuery();
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$query->groupBy('accounts.industry');
$results = $query->execute();
8. Having!
For a having we need to setup a condition object. To do this, instantiate the SugarQuery_Builder_Condition object and set the operator, field, and values.
sugarquery-having.php
<?php
$query = new SugarQuery();
$query->from(BeanFactory::getBean('Accounts'));
$query->join('contacts', array('alias' => 'industryContacts'));
$query->join('opportunities', array('relatedJoin' => 'industryContacts', 'alias' => 'contactsOpportunities'));
$query->select()->setCountQuery();
$query->where()->equals('contactsOpportunities.sales_stage', 'closed');
$havingCondition = new SugarQuery_Builder_Condition($query);
$havingCondition->setField('contactsOpportunities.amount')->setOperator('>')->setValues('1000');
$query->having($havingCondition);
$results = $query->execute();
Conclusion
SugarQuery is your one stop shop for getting your data out of the database. In part two we can examine more advanced options.
