SugarCRM Cookbook – SugarQuery – The Basics

sugarcrmiapps —  April 22, 2014 — 6 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.

6 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

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