SugarCRM Cookbook - SugarQuery - The Basics

Post originally written by Jim Bartek.

UPDATE October 3rd 2017

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

  • Comment originally made by Hirak.

    The N-Join seems to be doesn’t work. Here is the query I got:

    SELECT jt0_contacts.salutation rel_full_name_salutation, jt0_contacts.first_name rel_full_name_first_name, jt0_contacts.last_name rel_full_name_last_name, jt2_jt0_contacts_opportunities.name name FROM accounts INNER JOIN (select tst.team_set_id from team_sets_teams tst INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id

    AND team_memberships.user_id = ”

    AND team_memberships.deleted=0 group by tst.team_set_id) accounts_tf on accounts_tf.team_set_id = accounts.team_set_id

    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 AND ( jt0_contacts.team_set_id IN (select tst.team_set_id from team_sets_teams tst

    INNER JOIN team_memberships team_membershipsjt0_contacts ON tst.team_id = team_membershipsjt0_contacts.team_id

    AND team_membershipsjt0_contacts.user_id = ”

    AND team_membershipsjt0_contacts.deleted = 0) ))

    INNER JOIN opportunities_contacts jt3_opportunities_contacts ON (jt3_opportunities_contacts.deleted = 0)

    INNER JOIN opportunities jt2_jt0_contacts_opportunities ON (jt2_jt0_contacts_opportunities.id = jt3_opportunities_contacts.opportunity_id AND jt2_jt0_contacts_opportunities.deleted = 0 AND ( jt2_jt0_contacts_opportunities.team_set_id IN (select tst.team_set_id from team_sets_teams tst

    INNER JOIN team_memberships team_membershipsjt2_jt0_contacts_opportunities ON tst.team_id = team_membershipsjt2_jt0_contacts_opportunities.team_id

    AND team_membershipsjt2_jt0_contacts_opportunities.user_id = ”

    AND team_membershipsjt2_jt0_contacts_opportunities.deleted = 0) )) WHERE accounts.deleted = 0 AND accounts.industry = ‘media’ AND jt2_jt0_contacts_opportunities.sales_stage = ‘closed’

    here opportunities_contacts table was joined like without relate key: INNER JOIN opportunities_contacts jt3_opportunities_contacts ON (jt3_opportunities_contacts.deleted = 0)

  • Comment originally made by Ketan.

    Hey,

    Thanks for such a great post!!!

    Can you please let me know how we can handle the not equal to conditions using $query->where()->equals function?

    Or if there is any other function to handle this case?

    Thanks,

    Ketan

  • Comment originally made by paperless.

    The Sugarquery-aliasing recipe here  (https://gist.github.com/jbartek/11050017#file-sugarquery-aliasing-php) seems to be incorrect

    in referencing industryAccounts

    since it generates only:

    SELECT industryContacts.last_name lname, contactsOpportunities.name name FROM accounts INNER JOIN accounts_contacts jt0_accounts_contacts ON (accounts.id = jt0_accounts_contacts.account_id AND jt0_accounts_contacts.deleted = 0)

    INNER JOIN contacts industryContacts ON (industryContacts.id = jt0_accounts_contacts.contact_id AND industryContacts.deleted = 0)

    LEFT JOIN contacts_cstm industryContacts_cstm ON (industryContacts_cstm.id_c = industryContacts.id)

    INNER JOIN opportunities_contacts jt1_opportunities_contacts ON (jt1_opportunities_contacts.deleted = 0)

    INNER JOIN opportunities contactsOpportunities ON (contactsOpportunities.id = jt1_opportunities_contacts.opportunity_id AND contactsOpportunities.deleted = 0) WHERE accounts.deleted = 0 AND contactsOpportunities.sales_stage = 'closed'

  • Comment originally made by Jeff Bickart (@bickart).

    This doesn't seem to return the Bean.  In Sugar 6 you could use retrieve_by_string_fields. How do do the same thing here?
  • Comment originally made by juliopedrogomes.

    Hello Guys.

    I can to use o SugarQuery to customize the list view of the sugar´s modules?

    For example:

    I created a field called "from country" in the Users module in which I state that the parents of this User, such as Brazil or the United States.

    Also created a field called "visible to" the magnitude of opportunities when the User creates an opportunity, the field's value "from country" will be inserted in the "visible to" field of opportunities.

    Thereafter all users to list the opportunities will only see opportunities related to its country of origin. However I do not know how to customize the query of sugar to make it happen, can someone help me?

    Thanks