Skip to content

Rows

Interact with the rows of a SeaTable base.

Get rows

getRows

Get all the rows of the view and return an array.

base.getRows(table: Object/String, view: Object/String);

Examples

const table = base.getTableByName('Table1');
const view = base.getViewByName(table, 'view1');
const rows = base.getRows(table, view);

const rows = base.getRows('Table1', 'view1');

query

Use sql to query a base. SQL-Query is the most powerful function to the data from a base. Most SQL-syntax is support.

await base.query(sql)

Example: Get everything with a wildcard

const data = await base.query('select * from Bill')
output.text(data) // (1)!
  1. Returns for example the following: 
    [
    {"name":"Bob","price":"300","year":"2021"},
    {"name":"Bob","price":"300","year":"2019"},
    {"name":"Tom","price":"100","year":"2019"},
    {"name":"Tom","price":"100","year":"2020"},
    {"name":"Tom","price":"200","year":"2021"},
    {"name":"Jane","price":"200","year":"2020"},
    {"name":"Jane","price":"200","year":"2021"}
]

Example: WHERE

const data = await base.query('select name, price from Bill where year = 2021')
output.text(data) // (1)!

const data = await base.query('select name, price from Bill where name = "Bob"')
output.text(data) // (2)!
  1. Returns for example the following: 
    [
    {"name":"Bob","price":"300"},
    {"name":"Tom","price":"200"},
    {"name":"Jane","price":"200"}
]
  2. Returns for example the following: 
    [
    {"name":"Bob","price":"300","year":"2021"},
    {"name":"Bob","price":"300","year":"2019"}
]

Example: GROUP BY

const data = await base.query('select name, sum(price) from Bill group by name')
output.text(data) // (1)!
  1. Returns for example the following: 
    [
    {'SUM(price)': 600, 'name': 'Bob'},
    {'SUM(price)': 400, 'name': 'Tom'},
    {'SUM(price)': 400, 'name': 'Jane'}
]

Example: DISTINCT

const data = await base.query('select distinct name from Bill')
output.text(data) // (1)!
  1. Returns for example the following: 
    [
    {'SUM(price)': 600, 'name': 'Bob'},
    {'SUM(price)': 400, 'name': 'Tom'},
    {'SUM(price)': 400, 'name': 'Jane'}
]

getGroupedRows

Get rows in the grouped view.

base.getGroupedRows(table: Object/String, view: Object/String);

Example

const table = base.getTableByName('Table1');
const view = base.getViewByName(table, 'GroupedView');
const groupViewRows = base.getGroupedRows(table, view);

const groupViewRows = base.getGroupedRows('Table1', 'GroupedView');

getRow / getRowById (deprecated)

Get a row via its id and return a row object.

base.getRow(table: Object/String, rowId: String);

Examples

const table = base.getTableByName('Table1');
const row = base.getRow(table, "M_lSEOYYTeuKTaHCEOL7nw");

const row = base.getRow('Table1', "M_lSEOYYTeuKTaHCEOL7nw");

Delete row

deleteRow / deleteRowById (deprecated)

Delete a row in a table by its id.

base.deleteRow(table: Object/String, rowId: String);

Examples

const table = base.getTableByName('Table1');
base.deleteRow(table, 'M_lSEOYYTeuKTaHCEOL7nw');

base.deleteRow('Table1', 'M_lSEOYYTeuKTaHCEOL7nw');

Add row

appendRow / addRow(deprecated)

Add a row to a table.

base.appendRow(table: Object/String, rowData: Object, viewName?: String)

Examples

const table = base.getTableByName('Table1');
base.appendRow(table, {'Name': 'Alex', 'Age': '18'});
base.appendRow(table, {'Name': 'Alex', 'Age': '18'}, 'Default View');

base.addRow('Table1', {'Name': 'Alex', 'Age': '18'});
base.addRow('Table1', {'Name': 'Alex', 'Age': '18'}, 'Default View');

Update row(s)

updateRow / modifyRow(deprecated)

Modify a row in the table.

base.updateRow(table: Object/String, row: Object/string, updateRowData: Object);

Examples

const table = base.getTableByName('Table1');
const row = base.getRowById(table, "M_lSEOYYTeuKTaHCEOL7nw");
base.updateRow(table, row, {'Name': 'new name', 'number': 100});

base.updateRow('Table1', 'U_eTV7mDSmSd-K2P535Wzw', {'Name': 'new name', 'number': 100})

modifyRows

Modify multiple rows in the table at once.

base.modifyRow(table: Object/String, rows: Array, updatedRows: Array);

Example

const table = base.getTableByName('Table1');
const rows = base.getRows('Table1', 'Default view');
const selectedColumnName = 'Name';
const selectedRows = [], updatedRows = [];

rows.forEach((row) => {
if (row[columnName] === 'name') {
    selectedRows.push(row);
    updatedRows.push({columnName: 'name1'});
}
});
base.modifyRow(table, selectedRows, updatedRows);

Filter

base.filter allows to pass a conditional statement. It filters the rows that meet the conditions in the table, and returns a querySet object.

filter

base.filter(tableName, viewName, filterExpression)

Example

// Filter out rows whose number column is equal to 5, and return a querySet object
const querySet = base.filter('Table1', 'Default', 'number = 5');

Filter Expressions

filter expressions

The table query will become simpler and more efficiency by using the sql-like statements as a paramter in base.filter() function. In different column types, there are a little differences in the query method and the format of input statement. These are the available query methods:

  • greater-less query: >， >， =， \<， \<=
  • equal-unequal query: =, \<>
  • computation: +, -, *, /, ^, %

Here is an example based on the code queryset = base.filter("Table1", "age>18")

  • age: column name
  • >: operator
  • 18: parameter
Data structure Column type Format of greater-less query Format of equal-unequal query computation
String Text, Long Text, URL,Email, Single Select Unsupported String Unsupported
List Multiple Select Unsupported String Unsupported
Number Number int, float int, float, and empty string "" Supported
Date Date, Created time, Last modified time Patterns: YYYY-MM-DD, YYYY-MM-DD hh:mm, YYYY-MM-DD hh:mm:ss Same patterns as greater-less query Unsupported
Boolean Checkbox Unsupported true, false and empty string "", (case-insensitive) Unsupported

Here are more examples of the different filter expressions pending of the column type.

String-based Column

Column types include Text, Long Text, URL, Email, Checkbox.

# 1. equal-unequal query
base.filter('Table1', 'view_name', "column_name=hello world")
base.filter('Table1', 'view_name', "column_name!=''")

List-based Column

Column types include Multiple Select

# equal-unequal query
base.filter('Table1','view_name', "column_name=A and column_name=B") # Find the rows which contains both 'A' and 'B'

Number-based Column

  1. Column types include Number
# 1. greater-less query
base.filter('Table1', 'view_name', "column_name>18")
base.filter('Table1', 'view_name', "column_name>-10 and column_name<=0")

# 2. equal-unequal query
base.filter('Table1', 'view_name',"column_name<>20")
base.filter('Table1', 'view_name', "column_name=0")
base.filter('Table1', 'view_name',"column_name=''")
  1. Computation
base.filter('Table1', 'view_name', "column_name+3>18")
base.filter('Table1', 'view_name', "column_name*2=18")
base.filter('Table1', 'view_name', "column_name-2=18")
base.filter('Table1', 'view_name', "column_name/2=18")
base.filter('Table1', 'view_name', "column_name^2=18")
base.filter('Table1', 'view_name', "column_name%2=1")

Date-based Column

Column types include Date, Created time, Last modified time

# 1. greater-less query
base.filter('Table1', 'view_name', "column_name>'2020-1-30'")
base.filter('Table1', 'view_name', "column_name>='2019-1-1 5:30' and column_name<='2019-5-1 6:00'")

# 2. equal-unequal query
base.filter('Table1', 'view_name', "column_name='2020-1-1 10:59:59'")
base.filter('Table1', 'view_name', "column_name!=''")

Note that please use the quotes "" when making the date-time query

Boolean-based Column

Column types include Checkbox

# equal-unequal query
base.filter('Table1', 'view_name','column_name=False') # Same as base.filter('Table1', "column_name=''")
base.filter('Table1', 'view_name', "column_name=True")

Filter Queries

The return value of the base.filter function, this object provides some methods to simplify the operation of the filtered data

filter

Pass a conditional statement, filter out the rows that meet the conditions in the table, and return a querySet object.

base.filter(table: Object/String, ??, condition: ??)

Example

// Filter out rows whose number column is equal to 5, and return a querySet object
const querySet = base.filter('Table1', 'Default', 'number = 5');

all

Returns all filtered data in the form of a list

querySet.all(linkId, tableName, linkedTableName, rowId, updatedlinkedRowIds)

Example

const list = querySet.all();

count

Returns the number of filtered rows

Example

const count = querySet.count();

last

Return the last filtered data

Example

const row = querySet.last();

first

Return the first filtered data

Example

const row = querySet.first();

delete

Delete all filtered rows and return the number of successfully deleted

Example

const count = querySet.delete();

update

Modify the row data and return the updated data

Example

// Modify the contents of the Name column of all filtered rows to xxxx
const rows = querySet.update({Name: 'xxxx'});

filter

Further filtering, return a querySet object

Example

// Filter out the rows with the value of Tom in the Name column of the querySe
const querySet1 = querySet.filter('Name = "Tom"');

get

Get a piece of data in the querySet that meets the conditions, and return a row

Example

// Get the first data of Tom in the Name column of the querySet
const row = querySet.get('Name = "Tom"');