Db.selectRows

Return the result of a SQL 'select' statement as a List of Maps.

Each Map represents one row of the result.

Note: A value will be converted to a Date object if it is a date/time column type, or if the column name ends in “date”.

$query = sql'SELECT id, name FROM users ORDER BY name'

$users = Db.selectRows($query)

// [
//     { id: 1, name: 'ann' }
//     { id: 3, name: 'bob' }
//     { id: 2, name: 'cody' }
// ]

foreach $users as $user {
    print($user.name)
}

//= 'ann'
//= 'bob'
//= 'cody'

Where Map 

If $whereMap is given, a dynamic WHERE clause will be appended to the query.

In addition to column names, keys can include limit, orderBy, and orderByDesc.

$where = {
    isDeleted: 0
    'publishDate >': Date.create('1 year ago')
    orderByDesc: 'publishDate'
    limit: 10
}

Db.selectRows('SELECT * FROM posts', $where)

// Runs Query:
//   SELECT * FROM posts
//   WHERE isDeleted = 0
//   AND publishDate > '2020-06-13 00:00:00'
//   ORDER BY publishDate DESC
//   LIMIT 10

Where In Values 

You can pass a list of values for a where in query, like this:

$sql = sql'''
    SELECT * FROM products
    WHERE productId IN ({productIds})
'''

$sql.fill({ productIds: [11, 22, 33] })

You can also pass it in via $whereMap.

$whereMap = { 'productId in': [11, 22, 33] }
Db.selectRows(sql'SELECT * FROM products', $whereMap)

See Also