Working with SoQL Queries

Simple filters don't always suit our needs. Sometimes we need to refine our data even further; for that, Socrata has SoQL queries. SoQL Queries can be written manually or you can use the SoqlQuery() class to build a query easily without having to worry about escaping variables and url encoding values.

Example Usage

// Create a SoqlQuery that will be used to filter out the results of a dataset
$soqlQuery = new SoqlQuery();

// Write a SoqlQuery naturally
$soqlQuery->select("date_posted", "state", "sample_type")
          ->where("state = 'AR'")
          ->limit(1);

// $ds is a SodaDataset object
$results = $ds->getDataset($soqlQuery);

Available Functions

To build a SoQL query using the SoqlQuery class, here are the supported functions named appropriately to match their SoQL equivalent.

SELECT

Select only a few columns of data or use aliases.

Select Everything

By default, a SoqlQuery instance will select all of the columns but you may use an empty select() to select everything.

$soqlQuery->select();

Select a Single Column

To select a single column, simply pass it as a parameter.

$soqlQuery->select("foo");

Select Multiple Columns

To select multiple columns, pass all of the column names as parameters.

Tip: You also pass an array containing these values.

$soqlQuery->select("foo", "bar", "baz");

Select Columns with Aliases

To select column(s) with aliases, create an associative array where the key is the column name and the value is the alias.

Tip: Not all column names must have an alias as seen with "baz" in the example below.

$selectWithAliases = array(
    "foo_column" => "f_alias",
    "bar_column" => "b_alias",
    "baz"
);

$soqlQuery->select($selectWithAliases);

WHERE

Add a condition that the results must match in order to be returned in the dataset. This function takes a string that contains the where clause.

Tip: To combine multiple WHERE statements, use the SoQL operators such as AND or OR. Multiple calls to where() will lead to previous values being overwritten.

$soqlQuery->where("magnitude > 3.0 AND source = 'pr'");

ORDER

Select the order the results should be returned in. By default, the results returned are ordered in ascending order based on the :id value.

Warning: Multiple order() calls are only supported in the latest Socrata API; prior to this API update, only a single order() statement can be used per SoQL query.

Tip: SoqlOrderDirection::ASC and SoqlOrderDirection::DESC are provided for convenience should the API every change these constants and their use is recommended, but using ASC and DESC as strings will also work.

API Version 1

Order the results based on a date_updated column in ascending order.

$soqlQuery->order("date_updated", SoqlOrderDirection::ASC);

API Version 2

Order the results based on a date_updated column in ascending order and should there be any results with the same date_updated value, then sort by date_created in ascending value.

$soqlQuery->order("date_updated", SoqlOrderDirection::ASC)
          ->order("date_created", SoqlOrderDirection::ASC);

GROUP

Use SoQL's limited aggregation.

Warning: Multiple group() calls are only supported in the latest Socrata API; prior to this API update, only a single group() statement can be used per SoQL query.

$soql->select("region", "MAX(magnitude)")->group("region");

LIMIT

Limit the number of results returned in a dataset.

Tip: Any number less than 0 or non-number will throw a OutOfBoundsException or InvalidArgumentException, respectively.

$soqlQuery->limit(5);

OFFSET

Set the number of records into a dataset you want to start at. This option is often used in conjunction with limit() in order to "page" through the dataset.

For example, to retrieve the "4th page" of records (records 151 - 200) where you are using limit(50), you’d use offset(150).

Tip: Any number less than 0 or non-number will throw a OutOfBoundsException or InvalidArgumentException, respectively.

$soqlQuery->offset(100);

SEARCH

A full text search is available for use but keep in mind, this will search for a $needle in all of the columns of a dataset; think of this as a search engine instead of a SQL LIKE clause.

$soqlQuery->fullTextSearch("needle");