GetRows

GetRows method retrieves a number of rows from specified custom data table, according to specified criteria. This is an equivalent of SQL SELECT query.

Different parameters allow specifying what table is used, how many rows are returned, what criteria they must match, sorting etc.

This method may be useful, if you want to display custom data in emails or do some conditional formatting that depends on it.

1. Method signature

rows = GetRows(table, top, columns, filters, order, filterOperator)

Parameter definition

Filter object parameter definition

Order object parameter definition

NOTE: Typically, Filter and Order objects are created using constructors, which is more convenient — see examples for more information.

2. Using values returned by GetRows method

Values returned by GetRows method are arrays (enumerables). This means they are not simple values that can be displayed directly (such as ${SubscriberEmail}), but complex values that must be processed first to access individual column values. There are several ways to do it, the most common are enumerating and accessing directly by index.

2.1. Enumerating rows and columns

Enumerating is going through all values in an array without the need to know how many values it contains. This is useful if we do not know e.g. how many records will be returned or how many columns there are in a single record.

The easiest way to enumerate is to use «each» attribute. This attribute can be applied to any element and will duplicate it as many times as the enumerated array contains elements. In case of GetRows, it may be the number of rows or the number of columns in specific row.

Example — Simple table with contents read from database. <tr> elements are created from enumerating rows. <td> elements are created from enumerating columns in a single row.

<table>
  <tr each="var row in GetRows('MyTable')">
    <td each="var column in row">
      ${column.Value}
    </td>
  </tr>
</table>

2.2. Accessing rows and columns directly

Rows and columns can be accessed directly using array indexing. Rows are indexed by numbers (starting from 0) and columns are indexed by their names.

This method requires knowing exactly what the returned array contains. If wrong index is used (e.g. column name is misspelled or row index is out of bounds), it will result in error.

Example — Displaying contents of 3 rows read from the database. GetRows result is stored in a variable and each row is accessed directly using index.

<var rows="GetRows('MyTable', 3)"/>
<div>Row no 1: ${rows[0]['MyColumn']}</div>
<div>Row no 2: ${rows[1]['MyColumn']}</div>
<div>Row no 3: ${rows[2]['MyColumn']}</div>

2.3 Mixing access methods

Of course, both methods can be mixed if necessary.

Example — List with contents read from database. <li> elements are created from enumerating rows. Each row is created by accessing columns directly by their names.

<ul>
  <li each="var row in GetRows('MyTable')">
    <a href="mailto: ${row['Email']}">${row['Firstname']} ${row['Lastname']}</a>
  </li>
</ul>

3. Using GetRows parameters to create more complicated requests

Below you can find several examples of calling GetRows with additional parameters. These calls can be inserted into previous examples to create a complete email template.

Example 1 — The most simple call

All records from specified table are returned. No additional criteria.

GetRows('MyTable')

Equivalent SQL query:

SELECT * FROM MyTable

Example 2 — Selecting at most 5 records and only specified columns.

GetRows('MyTable', 5, new [] {'Firstname', 'Lastname', 'Email'})

Equivalent SQL query:

SELECT TOP 5 Firstname, Lastname, Email FROM MyTable

Example 3 — displaying all rows matching specific criteria. E.g. all entries connected to specific customer with some ID.

GetRows('MyTable', null, null, new [] {new Filter('CustomerId', EQ, 1234)})

Equivalent SQL query:

SELECT * FROM MyTable WHERE CustomerId = 1234

Example 4 — using multiple filters.

GetRows('MyTable', null, null, new [] {new Filter('CustomerId', EQ, 1234), new Filter('Price', GT, 24.99)})

Equivalent SQL query:

SELECT * FROM MyTable WHERE CustomerId = 1234 AND Price > 24.99

Example 5 — using subscriber properties in filters

GetRows('MyTable', null, null, new [] {new Filter('CustomerId', EQ, SubscriberProperty('customer_id'))})

Equivalent SQL query:

SELECT * FROM MyTable WHERE CustomerId = @customer_id

NOTE: @customer_id is a parameter that will be filled with actual subscriber property value.

Example 6 — Using filterOperator

GetRows('MyTable', null, null, new [] {new Filter('Color', EQ, 'red'), new Filter('Color', EQ, 'blue')}, null, OR)

Equivalent SQL query:

SELECT * FROM MyTable WHERE Color = 'red' OR Color = 'blue'

Example 7 — displaying 10 most recent records (sorted by date column)

GetRows('MyTable', 10, null, null, new [] {new Order('CreationDate', DESC)})

Equivalent SQL query:

SELECT TOP 10 * FROM MyTable ORDER BY CreationDate DESC

Example 8 — Multisorting

GetRows('MyTable', null, null, null, new [] {new Order('CreationDate', DESC), new Order('ProductName', ASC)})

Equivalent SQL query:

SELECT * FROM MyTable ORDER BY CreationDate DESC, ProductName ASC