# `Cardbox.SQL`
[🔗](https://github.com/hattoriai/cardbox/blob/v0.1.0/lib/cardbox/sql.ex#L1)

A query's filter, search and sorts, in the SQL of the app's database: an
`Ecto.Query` of `Cardbox.Record` that the database filters, sorts and
counts, reading only the records it returns.

The values of records are a JSON map (`jsonb` on PostgreSQL, JSON text on
SQLite), read with the database's own JSON functions. Each value is read
only when it is of the field's type, so a comparison never fails on a
value of another type.

Cardbox runs on PostgreSQL (`Ecto.Adapters.Postgres`) and SQLite
(`Ecto.Adapters.SQLite3`, with its JSON functions, which every build of
SQLite has had since 3.38). On SQLite, a search and `contains` compare
without case only the letters of ASCII, as SQLite's `LIKE` does.

# `dialect`

```elixir
@type dialect() :: :postgres | :sqlite
```

# `dialect`

```elixir
@spec dialect(module()) :: dialect()
```

The dialect of a repo's database.

# `group`

```elixir
@spec group(
  Cardbox.Collection.t(),
  Cardbox.Query.t(),
  dialect(),
  [{Cardbox.Field.t(), atom() | nil}],
  term(),
  keyword()
) :: Ecto.Query.t()
```

The records that meet a query, grouped by fields and aggregated in the
database: a row for each group, `%{"groups" => [value, ...], "value" =>
aggregate}`, where a group's value is as the records keep it (a
choice's id, a day as text) and a day field takes a `unit` (`:day`,
`:month` or `:year`, as `"2026-10"` for a month).

`by` is a list of `{field, unit}` (`unit` is nil but for dates), and
`aggregate` `:count` or `{:sum | :mean | :min | :max, field}` of a
number field.

# `query`

```elixir
@spec query(Cardbox.Collection.t(), Cardbox.Query.t(), dialect(), keyword()) ::
  Ecto.Query.t()
```

The records of a collection that meet a query, in its order: an
`Ecto.Query` to read, count or narrow further.

`opts`: `:me`, `:time_zone` and `:now`, which the query's conditions are
read with (see `Cardbox.Filter`).

---

*Consult [api-reference.md](api-reference.md) for complete listing*