DeveloperTutorialsOther

Properties Array

Properties Array

Some functions that return results from your website database will accept a parameter that lets you build a query similar in style to SQL.

Show me all the different sale item titles, for any 'adjustment' sales made after the year 2020.

$arrProperties = array();
$arrProperties['joins'][] = array('commerce_sales_items', 'sale_id');
$arrProperties['filter'][] = array('sale_type', '=', 'adjustment');
$arrProperties['filter'][] = 'AND';
$arrProperties['filter'][] = array('sale_datetime', '>', "2020-01-01");
$arrProperties['columns'] = array('sale_item_title');
$arrProperties['group_by'] = array('sale_item_title');

$arrTitles = \Components\Commerce\Sales::getAll($arrProperties);

Example Breakdown

Intialize a new empty array on a single line, so that the lines following can be consistent in style. The order of the joinsfiltercolumns, and group_by can be defined in any order, in practise.

$arrProperties = array();

First we are stating that we want to Join the Sales and Sales Items tables. This means that every result returned will contain all the columns of both. For example take Sale A and Sale B, which have 3 and 2 items respectively:

Sale A
Sale B
Item 001
Item 004
Item 002
Item 005
Item 003

The rows returned after a join in this case would be the following:

[ columns for  Sale A ]
+
[ Columns for Item 001 ]
[ columns for  Sale A ]
+
[ Columns for Item 002 ]
[ columns for  Sale A ]
+
[ Columns for Item 003 ]
[ columns for  Sale B ]
+
[ Columns for Item 004 ]
[ columns for  Sale B ]
+
[ Columns for Item 005 ]

The first parameter for a join is the table name, and the second is the column to use for the join operation. The commerce_sales_items table contains a sale_id column specially so that each item can link back to the sale it belongs to.

$arrProperties['joins'][] = array('commerce_sales_items', 'sale_id');

Next we narrow down the rows we want in our results. (Note that we could refer to columns in Sales Items if we wanted to, since we have joined).

$arrProperties['filter'][] = array('sale_type', '=', 'adjustment');
$arrProperties['filter'][] = 'AND';
$arrProperties['filter'][] = array('sale_datetime', '>', "2020-01-01");

To simplify the results, we specify that we only want one column returned, the title of the Sale Item.

$arrProperties['columns'] = array('sale_item_title');

We only want to know the different Sale Item titles, and here we can remove duplicates by using the group_by property.

$arrProperties['group_by'] = array('sale_item_title');

Lastly we can execute the query on the Sales table and get our results.

$arrTitles = \Components\Commerce\Sales::getAll($arrProperties);

Properties Array Reference

columns
Array of
String column
List the columns that should appear in the output.
joins
Array of
Array (
String table,
String column
)
Splice results with rows from 1 or more other tables.
filter
Array of
Array (
String column,
String comparator,
String value
)
... (elements joined by)
String conjunction
Control the rows returned by filtering on the values of columns. See Database Filters for more details and examples.
start
Number
The first row to return (Starting at 0). For example, start 10, would ignore rows 0 - 9.
limit
Number
The  maximum number of rows to return.
order
Array of
Array (
String column,
'ASC' or 'DESC'
)
Sort the results using the value from one or more columns. 
group_by
Array of
String column
List one or more columns. Any rows that contain the same value for all of the defined columns, will be removed.
having
Identical syntax to 'filter'
Like a filter, but is executed on the results from aggregate SQL functions such as SUM().
columns_excluded
Array of
String column
List one or more columns that will not be included in the results. Columns also listed in 'columns', will not be returned (this property has precedence).
search
String
Search for entities that contain the provided string in any of their string properties. For example when searching Website\Pages, the page title and page content will be searched.

Share This Tutorial: