Query Array

Oncord's API allows you to query and retrieve sets of database rows in bulk.  These functions are usually named getAll().  For instance:
\Components\Website\Posts::getAll() retrieves all posts in your database.

These getAll() style functions accept a parameter that lets you customise the query, similar to how you might write an SQL query.  This parameter is known as the 'Query Array'.

For instance, to retrieve 10 posts, you can call:
\Components\Website\Posts::getAll(['limit' => 10'])

Usage with a data control

Data controls such as <data:repeater> and <data:table> automatically use the Query Array to populate their data in an efficient manner. These controls inject the query array as the final parameter in your datasource function call.

For instance, <data:repeater paging="true"> allows the user to click "Show More" to view sets of results at a time.  The repeater injects a Query Array into the datasource to set a 'start' and 'limit' to populate itself in an progressive manner to ensure that queries are efficient.

Similarly, <data:table> with <data:column ordercolumn="post_title"> allows the user to reorder data in a table by clicking the heading.  A Query Array is automatically injected into the datasource function to 'order' the data by the column.

It is important to note that data controls always inject the Query Array as the last parameter.  So when paging is enabled: datasource="\Components\Website\Posts::getAllForCategory(1)" will actually invoke \Components\Website\Posts::getAllForCategory(1, ['limit' => 25]).

You can manipulate the Query Array using attributes on data controls using attributes such as datafilter="" and dataorder="". 

For further information on usage with data controls, please see the:

Reference

When directly calling a getAll() style function, you can pass in a Query Array that accepts these properties:

columns
Array of String column

=> ['sale_datetime']
The columns that should appear in the output.

Similar to SELECT in SQL.
columns_excluded
Array of String column

=> ['sale_datetime']
Columns that will not be included in the results.

By default all columns are returned, so this can be used to exclude specific columns.
filter
Array of
Array (
String column,
String comparator,
String value
)
String conjunction
('AND' or 'OR')

 => [
['sale_datetime', '>', 2020],
'AND',
['sale_datetime', '<', 2021]
]
Filter the result.

Similar to a WHERE clause in SQL.

See Database Filters for more details and examples.
start
Number

=> 10
The first row to return (Starting at 0). For example, start 10, would ignore rows 0 - 9.
limit
Number

=> 25
The  maximum number of rows to return.
order
Array of
Array (
String column,
'ASC' or 'DESC'
)

=> [['sale_datetime', 'asc']]
Sort the results using the value from one or more columns. 

Similar to ORDER BY in SQL.
group_by
Array of String column

=> ['contact_id']
List one or more columns. Any rows that contain the same value for all of the defined columns, will be removed.
search
String

=> 'Fred'
Search for rows that contain the provided text string. For example when searching Website\Pages, the page title and page content will be searched.
having
Identical syntax to 'filter'
Like a filter, but is executed on the results from aggregate SQL functions such as SUM().

Advanced users only.
joins
Array of
Array (
String table,
String column
)

=> [
['customers_contacts',
'contact_id'],
]
Join results with rows from other tables.

Advanced users only.

Database table names are normally the lower-case component name with underscores.  Link with primary key as second element in array.

Calling from within PHP

You can make component API calls by specifying your own query array via PHP.

Get all sales, that were not 'adjustment's made after the year 2020.

<?php $arrQuery = [ // SELECT sale_id, sale_datetime, contact_first_name, contact_last_name 'columns' => ['sale_id', 'sale_datetime', 'contact_first_name', 'contact_last_name'], // WHERE sale_type <> adjustment AND sale_datetime > 2020-01-01 'filter' => [ ['sale_type', '!=', 'adjustment'], 'AND', ['sale_datetime', '>', '2020-01-01'] ], ]; $arrSales = \Components\Commerce\Sales::getAll($arrQuery); varDump($arrSales); die(); ?>