prepare method

QueryId prepare({ List<Sql> columns = const [], Sql? search, Sql? where, List<QueryOrdering> order = const [], QueryDistinction distinction = QueryDistinction.rowid, int limit = -1, })

Prepares an advanced SELECT-based query for subsequent executions.

This method returns a new QueryId identifier backed by a new Query object. The method's named arguments are called query configuration parameters in this documentation; they establish the behavior of the query in the following way:

• the columns parameter defines, in the form of SQL expressions, additional columns for the query to retrieve;
• the search parameter must be null when querying the mapping table or, for full-text search queries, it defines the FROM clause using the table-valued function syntax;
• the where parameter defines the WHERE clause condition;
• the order parameter lists expressions — with a sorting order for each expression — to define the ordering of the resulting rows (this list must include neither rowid nor keyid columns);
• the distinction parameter defines whether the automatically added last sorting column will be rowid, which is the default, or keyid;
• the limit parameter defines the value for the LIMIT clause.

The SELECT statement of the query has the following columns: rowid, keyid, SQL expressions from order, and SQL expressions from columns. Each resulting row returned by the query method contains a FullId of the row in the QueryRow.id field, values of the expressions from the order parameter in the QueryRow.ordered field, and values of the expressions from the columns parameter in the QueryRow.columns field. That is, for every order[i].sql expression, the row r contains the r.ordered[i] value, and for every columns[j] expression, the row r contains the r.columns[j] value.

In the following example, the mapping table with virtual columns starName and distance is queried to find the star closest to Earth among those that are at least 43 parsecs away:

QueryId qid = sqlite.prepare(
  where: SqlI("distance") >= SqlL(43),
  columns: [SqlI("starName")],
  order: [queryOrdering(SqlI("distance"), QuerySorting.ascending)],
  limit: 1,
);
if (sqlite.query(qid) case [QueryRow r])
  log.info("Star ${r.columns[0]}, distance ${r.ordered[0]} parsecs.");
/* Star Sigma Andromedae, distance 43.003 parsecs. */

In the following example, the timetable FTS index with columns skyObject, earthLocation, and months is queried to find three object-location pairs suitable for stargazing in January:

FtsQ ftsQuery = FtsI(["months"], FtsP.quote("january"));
QueryId qid = sqlite.prepare(
  search: SqlT("timetable", SqlS(ftsQuery)),
  columns: [SqlI("skyObject"), SqlI("earthLocation")],
  limit: 3,
);
for (QueryRow r in sqlite.query(qid))
  log.info("${r.columns[0]}: ${r.columns[1]}");
/* Orion Nebula: Northern Hemisphere
   Large Magellanic Cloud: Atacama Desert
   Carina Nebula: Atacama Desert */

See also: free and query.

Implementation

QueryId prepare({
  List<Sql> columns = const [],
  Sql? search,
  Sql? where,
  List<QueryOrdering> order = const [],
  QueryDistinction distinction = QueryDistinction.rowid,
  int limit = -1,
}) {
  checkActive();
  QueryId id = _nextQueryId++;
  _queries[id] = Query(
    this,
    columns,
    search,
    where,
    order,
    distinction,
    limit,
  );
  return id;
}