{"id":3880,"date":"2023-09-21T13:55:32","date_gmt":"2023-09-21T12:55:32","guid":{"rendered":"https:\/\/help.expertsender.com\/?post_type=es&p=3880"},"modified":"2024-03-19T13:25:09","modified_gmt":"2024-03-19T12:25:09","slug":"getrows","status":"publish","type":"es","link":"https:\/\/help.expertsender.com\/pl\/emp\/dynamic-content\/data-tables\/getrows\/","title":{"rendered":"GetRows"},"content":{"rendered":"\n

GetRows<\/code> method retrieves a number of rows from specified custom data table, according to specified criteria. This is an equivalent of SQL SELECT<\/code> query.<\/p>\n\n\n\n

Different parameters allow specifying what table is used, how many rows are returned, what criteria they must match, sorting etc.<\/p>\n\n\n\n

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

1. Method signature<\/strong><\/p>\n\n\n\n

<\/path><\/path><\/svg><\/span>
rows <\/span>=<\/span> <\/span>GetRows<\/span>(table<\/span>,<\/span> top<\/span>,<\/span> columns<\/span>,<\/span> filters<\/span>,<\/span> order<\/span>,<\/span> filterOperator)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Parameter definition<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
table<\/td>string<\/td>Required. Table name.<\/td><\/tr>
top<\/td>int<\/td>Optional. Maximum number of records that will be retrieved. If set to null, this setting will default to 500. If set to more than 500, this setting will be ignored.<\/td><\/tr>
columns<\/td>array of string<\/td>Optional. Array of columns that will be retrieved. If set to null, all columns will be retrieved.<\/td><\/tr>
filters<\/td>array of Filter objects<\/td>Optional. Array of objects defining elements of WHERE<\/code> phrase of SELECT<\/code> query. Use this parameter, to restrict selected rows to those matching your criteria. If null, no criteria will be applied. If several filters are defined, by default returned rows will match all of them (equivalent to logical AND). Default behavior can be overridden using filterOperator<\/code> parameter.<\/td><\/tr>
order<\/td>array of Order objects<\/td>Optional. Array of objects defining sorting of returned rows (equivalent to ORDER BY<\/code>). If null, rows will not be sorted (will be returned in order supplied by the database). If several order objects are defined, table will be multi-sorted.<\/td><\/tr>
filterOperator<\/td>enumeration<\/td>Optional. Logical operator used for filters. Can be one of the following:AND (filter1 AND filter2 AND filter3…) – this is the default valueOR (filter1 OR filter2 OR filter3…)ANDNOT (NOT filter1 AND NOT filter2 AND NOT filter3…)<\/td><\/tr>
rows<\/td>array of rows<\/td>Return value. Array of rows. Every row itself is an array of key-value pairs (column name => column value). Column name is string. Column value type depends on actual type in the database. Array is empty if no data was found.
Rows table can be enumerated upon, e.g. to display in form of HTML table. See examples for more information<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Filter<\/code> object parameter definition<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
Name<\/td>string<\/td>Column name<\/td><\/tr>
Modifer<\/td>enumeration<\/td>Operation modifier. Can be one of the following:EQ (equals, “=”)GT (greater than, “>”)LT (lower than, “<“)LIKE (SQL LIKE<\/code> operator)<\/td><\/tr>
Value<\/td>object<\/td>Column value. Value type depends on column type, e.g. when filtering by number column, integer value should be supplied, string for text columns etc. Simple type conversion is also supported, e.g. string “123” representing correct integer value 123 will be accepted. This also works for dates, floating point numbers etc.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Order<\/code> object parameter definition<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
Name<\/td>string<\/td>Column name<\/td><\/tr>
Desc<\/td>enumeration<\/td>Order direction. Can be one of the following:ASC (ascending, e.g. oldest forst for dates, alphabetical sorting for texts, smallest first for numbers etc)DESC (descending, e.g. newest first for dates, reverse alphabetical sorting for texts, biggest first for numbers etc)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
NOTE: Typically, Filter<\/code> and Order<\/code> objects are created using constructors, which is more convenient – see examples for more information.<\/a><\/p>\n\n\n\n
2. Using values returned by GetRows<\/code> method<\/strong><\/p>\n\n\n\n
Values returned by GetRows<\/code> method are arrays (enumerables). This means they are not simple values that can be displayed directly (such as ${SubscriberEmail}<\/code>), 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.<\/p>\n\n\n\n
2.1. Enumerating rows and columns<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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<\/code>, it may be the number of rows or the number of columns in specific row.<\/p>\n\n\n\n
Example – Simple table with contents read from database. <tr><\/code> elements are created from enumerating rows. <td><\/code> elements are created from enumerating columns in a single row.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
<<\/span>table<\/span>><\/span><\/span>\n  <\/span><<\/span>tr<\/span> <\/span>each<\/span>=<\/span>"<\/span>var row in GetRows('MyTable')<\/span>"<\/span>><\/span><\/span>\n    <\/span><<\/span>td<\/span> <\/span>each<\/span>=<\/span>"<\/span>var column in row<\/span>"<\/span>><\/span><\/span>\n      $<\/span>{<\/span>column<\/span>.<\/span>Value<\/span>}<\/span><\/span>\n    <\/span><\/<\/span>td<\/span>><\/span><\/span>\n  <\/span><\/<\/span>tr<\/span>><\/span><\/span>\n<\/<\/span>table<\/span>><\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
2.2. Accessing rows and columns directly<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Example – Displaying contents of 3 rows read from the database. GetRows<\/code> result is stored in a variable and each row is accessed directly using index.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
<<\/span>var<\/span> <\/span>rows<\/span>=<\/span>"<\/span>GetRows('MyTable', 3)<\/span>"<\/span>\/><\/span><\/span>\n<<\/span>div<\/span>><\/span>Row no 1: $<\/span>{<\/span>rows[<\/span>0<\/span>][<\/span>'<\/span>MyColumn<\/span>'<\/span>]<\/span>}<\/<\/span>div<\/span>><\/span><\/span>\n<<\/span>div<\/span>><\/span>Row no 2: $<\/span>{<\/span>rows[<\/span>1<\/span>][<\/span>'<\/span>MyColumn<\/span>'<\/span>]<\/span>}<\/<\/span>div<\/span>><\/span><\/span>\n<<\/span>div<\/span>><\/span>Row no 3: $<\/span>{<\/span>rows[<\/span>2<\/span>][<\/span>'<\/span>MyColumn<\/span>'<\/span>]<\/span>}<\/<\/span>div<\/span>><\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
2.3 Mixing access methods<\/strong><\/p>\n\n\n\n
Of course, both methods can be mixed if necessary.<\/p>\n\n\n\n
Example – List with contents read from database. <li><\/code> elements are created from enumerating rows. Each row is created by accessing columns directly by their names.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
<<\/span>ul<\/span>><\/span><\/span>\n  <\/span><<\/span>li<\/span> <\/span>each<\/span>=<\/span>"<\/span>var row in GetRows('MyTable')<\/span>"<\/span>><\/span><\/span>\n    <\/span><<\/span>a<\/span> <\/span>href<\/span>=<\/span>"<\/span>mailto: ${row['Email']}<\/span>"<\/span>><\/span>$<\/span>{<\/span>row[<\/span>'<\/span>Firstname<\/span>'<\/span>]<\/span>}<\/span> $<\/span>{<\/span>row[<\/span>'<\/span>Lastname<\/span>'<\/span>]<\/span>}<\/<\/span>a<\/span>><\/span><\/span>\n  <\/span><\/<\/span>li<\/span>><\/span><\/span>\n<\/<\/span>ul<\/span>><\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
3. Using GetRows parameters to create more complicated requests<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Example 1 – The most simple call<\/p>\n\n\n\n
All records from specified table are returned. No additional criteria.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
Example 2 – Selecting at most 5 records and only specified columns.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>5<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>'<\/span>Firstname<\/span>'<\/span>,<\/span> <\/span>'<\/span>Lastname<\/span>'<\/span>,<\/span> <\/span>'<\/span>Email<\/span>'<\/span>}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT TOP <\/span>5<\/span> Firstname<\/span>,<\/span> Lastname<\/span>,<\/span> Email FROM MyTable<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
Example 3 – displaying all rows matching specific criteria. E.g. all entries connected to specific customer with some ID.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> 1234<\/span>)}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable WHERE CustomerId <\/span>=<\/span> <\/span>1234<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
Example 4 – using multiple filters.<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> 1234<\/span>),<\/span> new <\/span>Filter<\/span>(<\/span>'<\/span>Price<\/span>'<\/span>,<\/span> <\/span>GT<\/span>,<\/span> 24.99<\/span>)}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable WHERE CustomerId <\/span>=<\/span> <\/span>1234<\/span> AND Price <\/span>><\/span> <\/span>24.99<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
Example 5 – using subscriber properties in filters<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> <\/span>SubscriberProperty<\/span>(<\/span>'<\/span>customer_id<\/span>'<\/span>)<\/span>)<\/span>}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable WHERE CustomerId <\/span>=<\/span> <\/span>@<\/span>customer_id<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
NOTE: @customer_id<\/code> is a parameter that will be filled with actual subscriber property value.<\/p>\n\n\n\n
Example 6 – Using filterOperator<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Filter<\/span>(<\/span>'<\/span>Color<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> <\/span>'<\/span>red<\/span>'<\/span>),<\/span> new <\/span>Filter<\/span>(<\/span>'<\/span>Color<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> <\/span>'<\/span>blue<\/span>'<\/span>)},<\/span> <\/span>null,<\/span> OR)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable WHERE Color <\/span>=<\/span> <\/span>'<\/span>red<\/span>'<\/span> OR Color <\/span>=<\/span> <\/span>'<\/span>blue<\/span>'<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Example 7 – displaying 10 most recent records (sorted by date column)<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>10<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Order<\/span>(<\/span>'<\/span>CreationDate<\/span>'<\/span>,<\/span> <\/span>DESC<\/span>)}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT TOP <\/span>10<\/span> <\/span>*<\/span> FROM MyTable ORDER BY CreationDate DESC<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Example 8 – Multisorting<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
GetRows<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>null,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new <\/span>Order<\/span>(<\/span>'<\/span>CreationDate<\/span>'<\/span>,<\/span> <\/span>DESC<\/span>),<\/span> new <\/span>Order<\/span>(<\/span>'<\/span>ProductName<\/span>'<\/span>,<\/span> <\/span>ASC<\/span>)}<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
SELECT <\/span>*<\/span> FROM MyTable ORDER BY CreationDate DESC<\/span>,<\/span> ProductName ASC<\/span><\/span><\/code><\/pre><\/div>\n","protected":false},"author":6,"featured_media":0,"parent":3874,"menu_order":213,"template":"","format":"standard","emp-category":[],"class_list":["post-3880","es","type-es","status-publish","format-standard","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3880","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es"}],"about":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/types\/es"}],"author":[{"embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/users\/6"}],"version-history":[{"count":2,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3880\/revisions"}],"predecessor-version":[{"id":8866,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3880\/revisions\/8866"}],"up":[{"embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3874"}],"wp:attachment":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/media?parent=3880"}],"wp:term":[{"taxonomy":"emp-category","embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/emp-category?post=3880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}