SugarCRM Cookbook – SugarQuery – The Basics

sugarcrmiapps —  April 22, 2014 — 14 Comments

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 compileSql().

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

Now lets roll through some examples.

3. Joins

Let’s now get all the contacts on these media accounts.

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!

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!

6. Order the madness

Now you have this data, but you want to sort it, make it make some sense.  Thats EASY!

7. Groups!

Time to group by!

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

Conclusion

SugarQuery is your one stop shop for getting your data out of the database.  In part two we can examine more advanced options.

14 responses to SugarCRM Cookbook – SugarQuery – The Basics

  1. 

    Is this for the new System 7 versions of Sugar only or does this also apply to v6.5.16?

  2. 

    SugarQuery is available for Sugar7 and above. Thanks!

  3. 

    Interesting. I didn’t see a reason though for implementing this over just straight querying the db directly.

  4. 

    Hey guys.
    How i can use SugarQueries for customize the queries of the Sugar 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

  5. 

    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

  6. 

    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?

  7. 

    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’

  8. 

    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

  9. 

    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)

Trackbacks and Pingbacks:

  1. SugarCRM 7 - Create a custom subpanel - Shane Dowling - August 13, 2014

    […] argument $sugar_query is a new SugarQuery object, the details of which are documented here.  What you essentially need to do is extend this query with whatever join/filters you wish to add. […]

  2. SugarCRM 7 - Custom subpanels - Shane Dowling - August 13, 2014

    […] argument $sugar_query is a new SugarQuery object, the details of which are documented here.  What you essentially need to do is extend this query with whatever join/filters you wish to add. […]

  3. Creating Subpanels with Custom Results in Sugar 7.5 « Sugar Developer Blog – SugarCRM - May 18, 2015

    […] SugarQuery then you should get familiar with it because it’s awesome. There is a very helpful blog entry on SugarQuery which is a great place to start. The SugarQuery object coming into this method is a basic query […]

  4. TethrOn presents their Sugar Integration process « Sugar Developer Portal – SugarCRM - November 9, 2015

    […] API or a “higher level” API, choose the “higher level” API. Example: where possible use SugarQuery instead of raw SQL queries to implement your […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s