union method

void union(BaseSelectStatement other)

Appends the other statement as a UNION clause after this query.

The database will run both queries and return all rows involved in either query, removing duplicates. For this to work, this and other must have compatible columns.

The other query must not include a LIMIT or a ORDER BY clause. Compound statements can only contain a single LIMIT and ORDER BY clause at the end, which is set on the first statement (on which union is called). Also, the other statement must not contain compound parts on its own.

As an example, consider a todos table of todo items referencing a categories table used to group them. With that structure, it's possible to compute the amount of todo items in each category, as well as the amount of todo items not in a category in a single query:

  final count = subqueryExpression<int>(selectOnly(todos)
   ..addColumns([countAll()])
   ..where(todos.category.equalsExp(categories.id)));
 final countWithoutCategory = subqueryExpression<int>(db.selectOnly(todos)
       ..addColumns([countAll()])
       ..where(todos.category.isNull()));

 final query = db.selectOnly(db.categories)
   ..addColumns([db.categories.description, count])
   ..groupBy([categories.id]);
 query.union(db.selectExpressions(
     [const Constant<String>(null), countWithoutCategory]));

Implementation

void union(BaseSelectStatement other) {
  _addCompound(_CompoundOperator.union, other);
}