Build and export queries¶
Choose registered columns, add conditions and choose sort keys. Conditions are combined in visible AND, OR and NOT groups, including repeated conditions on the same field. Existing flat conditions retain AND semantics. Only catalog paths and operators are accepted; arbitrary SQL, Python expressions and regular expression lookups are never accepted.
Supported values¶
Field type |
Operators |
Input |
|---|---|---|
CharField / TextField and supported subclasses |
exact, iexact, contains, icontains, startswith, istartswith |
Text, including an empty string |
IntegerField |
exact, gt, gte, lt, lte |
Integer or integer text; not a boolean |
DecimalField |
exact, gt, gte, lt, lte |
Finite decimal text such as |
DateField / DateTimeField |
exact, gt, gte, lt, lte |
Date/datetime text |
BooleanField |
exact |
JSON |
UUIDField |
exact |
UUID text |
Any nullable supported field |
additionally isnull |
JSON |
Decimals are not accepted as JSON floats. Zero and false are preserved. Null
comparison uses isnull rather than a null value for another operator. Naive
datetime input uses Django’s active timezone; ambiguous or nonexistent local
times are rejected. Floating point, JSON, binary/file fields and composite primary
keys are outside the initial supported types.
Full-value lookups run field conversion and validators. Text fragment lookups
(contains/icontains/startswith/istartswith) validate text type and maximum field
length without whole-value format or custom validators. This allows searches such
as EmailField contains @example.com or URLField contains example.com.
Model choices and labels¶
Declared Django choices work for any registered scalar field, including
TextChoices, IntegerChoices, mappings, grouped options and callables.
The builder offers a selector for equality (exact and iexact), showing
the labels and submitting the stored codes. Text fragments and numeric/date
ranges keep their free-value inputs. Equality values are checked on the server;
changing the browser controls does not bypass validation. iexact accepts a
case-insensitive match to a declared text code.
Zero, false and explicitly declared empty-string choices remain valid values.
Use isnull to query NULL; a None choice is not an equality value. Naive
datetime choices and input use Django’s active timezone. Grouped options are
flattened into one selector. JavaScript adapts controls immediately; without
JavaScript, entering a stored code still works and the edit form renders a selector.
HTML results display declared labels. Unknown historical codes remain visible
as their original values. Labels are plain text, including labels marked safe
by host code. Filtering, ordering, pagination and exports continue to use stored values.
For example, HTML displays Open while CSV and Excel export open. Exported data does
not depend on the language of a label or on later label changes.
Choices are captured for authorized fields in each request’s catalog. No option is inferred from database rows or cached across requests. Callable choices are application code: keep them inexpensive and suitable for every user who may see that field. QBE’s configuration checks do not evaluate these callables.
Visual relationship explorer¶
After choosing a report, the builder displays its registered fields grouped by their model and a diagram of relationship paths. Each field includes its full registered path, so repeated display labels remain distinguishable. The diagram identifies forward or reverse relationships and whether they can return one or multiple related values. Search filters only fields in the current authorized catalog.
Use Add column, Add filter or, for eligible fields in Summary mode,
Add summary group to populate the existing builder controls. These shortcuts
do not execute a query or authorize a field: preview still uses the same
server-side validation, registered paths, view permissions and
Report.get_queryset(request) row policies. Every related report must remain
authorized for the current user.
Multi-valued paths, including reverse foreign keys and ManyToMany relations, can be selected as columns or filters. They cannot be used for row ordering, summary grouping or numeric Sum/Average metrics. Result rows keep one root row; related values are combined within that row subject to the configured limit.
The field hierarchy and ordinary builder controls are rendered by Django, so report construction remains available without JavaScript. With JavaScript, the diagram, authorized-field search and shortcuts are added. The explorer never samples business rows or discovers unregistered model paths automatically.
Pagination and ordering¶
Choose ascending or descending ordering for single-valued fields. A primary-key ordering tiebreaker makes pages stable for a fixed dataset. Pages are separate reads, not snapshots: concurrent writes may change later pages.
The bounded count reads at most the row cap plus one to detect truncation. A truncated report does not claim to know the global total. The default page size is 50, the maximum is 200, and the default report cap is 10,000 rows. See Settings and HTTP routes for configurable budgets and PostgreSQL timeouts.
CSV behavior¶
The CSV button exports all matching rows up to the report cap, not only the
current page. For a Summary report it exports completed groups and calculations,
ordered exactly like the preview. Column order, row policies and related-row checks match HTML.
Multi-valued report columns are joined into one cell per root row, in related
primary-key order. The related-value limit applies to HTML pages and the complete
CSV; when exceeded, CSV returns an error before streaming begins.
UTF-8 text supports Unicode, quotes and embedded newlines. Dates/times use ISO
format, decimals preserve precision, booleans use true/false, and null
becomes an empty cell.
Text starting with =, +, - or @ after leading whitespace/BOM is
prefixed with an apostrophe for spreadsheet formula safety. Typed negative numbers
retain their sign. CSV is an interchange format; it cannot preserve spreadsheet
column types. The response header X-QBE-Truncated: true indicates a capped export.
The synchronous iterator targets WSGI. Django may buffer it when adapting to ASGI; ASGI-native streaming is not advertised. A first-row preflight catches early errors. Later errors abort the download and are logged by exception class without SQL or query payloads. Headers already sent cannot change to a new error status. Closing or failing the stream releases its cursor and timeout context.
Excel export¶
Install django-qbe2[excel] to enable the Export Excel button. The workbook
contains one Report worksheet and exports the complete bounded report, including
completed summary groups, in the same order as CSV. Multi-valued relationship values
are joined into one text cell per root row. Text is stored as literal text, so values
beginning with formula characters are not executed as spreadsheet formulas. Numeric,
boolean, date and datetime values use Excel cell types; aware datetimes use Django’s
active timezone and are written without timezone metadata because Excel has no
timezone-aware datetime cell type. Decimal values are numeric cells; Excel itself
limits numeric precision to 15 significant digits, so use CSV when exact arbitrary
decimal precision is required. The same row, summary and related-value limits apply.
Excel is an optional dependency and is imported only when an XLSX export is requested. Without the extra the CSV export remains available and the Excel button is hidden.
Summary execution finishes before CSV or Excel output is returned, so a source-limit or calculation error produces an ordinary error response rather than a partial file.
Query definition v1¶
The UI sends a versioned definition back on pagination, editing and export. This is an HTTP form contract, not a REST API or an authorization token:
{
"version": 1,
"report": "sales.order",
"columns": ["number", "customer__name", "total"],
"filters": [{"field": "total", "operator": "gte", "value": "100.00"}],
"ordering": [{"field": "total", "direction": "desc"}]
}
All five keys are required. Empty filters and ordering arrays are valid. Columns must be nonempty and distinct; ordering fields must be distinct. Extra keys, unknown versions, paths, operators and invalid values are rejected. Result rows are never stored in the session. Explicitly saved definitions are separate database records; see Saved queries and sharing. No result rows are stored.
Condition groups and definition v2¶
Use Match all (AND) when every child must match, Match any (OR) when at
least one must match, and Exclude this group (NOT) to negate the whole group.
For example, (status paid OR overdue) AND NOT delivered uses an AND root,
an OR child with two status conditions, and a negated child with one delivered
condition. These are generic controls for the fields each report exposes.
Add condition and Add group preserve entered values and refresh the builder; they do not execute the report. Parent group and Condition group selectors move controls between groups. Removing a group also removes its descendants on the next builder submission. A failed preview keeps the pending group removal separate from each condition’s own deletion checkbox, so canceling it restores its conditions. Add actions reclaim removed controls before allocating new ones. The root cannot be removed. The controls work without JavaScript; JavaScript additionally arranges moved controls immediately and adapts value widgets.
Group limits count the whole expression: by default 20 conditions, 10 groups including the root, and three group levels including the root. Empty nested groups are rejected on preview. Only an empty, unnegated AND root represents an unfiltered report. A root using OR or NOT must contain a condition.
Flat, unnegated AND controls use v1. Nested groups, OR or NOT use v2, which replaces
the top-level filters key with where:
{
"version": 2,
"report": "sales.order",
"columns": ["number", "total"],
"where": {
"connector": "and",
"negated": false,
"children": [
{"field": "total", "operator": "gte", "value": "100.00"},
{
"connector": "or",
"negated": false,
"children": [
{"field": "status", "operator": "exact", "value": "paid"},
{"field": "status", "operator": "exact", "value": "overdue"}
]
}
]
},
"ordering": []
}
All five top-level keys are required. Each group has exactly connector,
negated and children; connector is and or or, negated is a JSON
boolean, and children is a list of groups or ordinary filter leaves. Every branch
is validated against the current catalog. Unknown keys and excessive trees are
rejected. Form group identifiers do not appear in the saved definition. Rebuilding
controls may reorder siblings; AND/OR results do not depend on sibling order.
Summary reports and definition v3¶
Choose Summary under Report type and apply it. Select zero to three grouping
fields and one to ten calculations. Count records needs no field; Sum and
Average offer only registered integer and decimal fields. Add calculation
works with or without JavaScript and assigns stable m1, m2 identifiers.
The checkboxes in this section choose grouping fields; they are separate from the
columns selected for a row report. For example, to chart subscriptions by plan,
select only plan as the grouping field and use Count records. You can group
by two or three fields for a summary table, but a bar chart requires exactly one.
Switching report type preserves the report and conditions while resetting output
columns, calculations and ordering. It does not update a saved query until you
explicitly save or update it.
With no grouping fields, a summary returns one row. Count is zero for an empty
source; Sum and Average are empty. With grouping fields, an empty source returns
no groups. NULL forms its own group and renders/exports as an empty cell. Count
includes every authorized source row. Sum and Average ignore NULL metric values;
if all values are NULL their result is empty. Decimal values remain Decimal;
Average uses at least six fractional places and half-even rounding. Integer Average
also returns a decimal value.
The source is a distinct set of authorized root primary keys selected from
Report.get_queryset(request) after conditions and related-field policies. This
prevents joins from counting one root row repeatedly while preserving consumer
annotations, filters and valid distinct semantics. The source cap is checked first;
then the database groups the authorized rows and calculates Count, Sum and the
Sum/non-null Count used by Average. Only completed group rows are read into Python
for the final bounded Decimal division, deterministic ordering, pagination, charting
and export. No client SQL is accepted and report execution does not mutate business
data.
Decimal Average uses the database Sum and non-null Count followed by bounded
Decimal division with the documented half-even rounding. On SQLite, Decimal Sum
uses scaled integer aggregation when the field precision and source cap fit SQLite’s
integer range; PostgreSQL uses its exact numeric Sum. This preserves decimal cents
without relying on SQLite’s binary floating-point SUM(DecimalField) behavior.
If a SQLite Decimal field exceeds the safe integer range for the configured source
cap, the summary is rejected with guidance to use PostgreSQL or a lower-precision
field instead of returning a rounded total.
{
"version": 3,
"report": "sales.order",
"where": {
"connector": "and",
"negated": false,
"children": [
{"field": "status", "operator": "exact", "value": "paid"}
]
},
"group_by": ["customer__city"],
"metrics": [
{"id": "m1", "function": "count", "field": null},
{"id": "m2", "function": "sum", "field": "total"},
{"id": "m3", "function": "avg", "field": "total"}
],
"ordering": [
{"type": "metric", "id": "m2", "direction": "desc"},
{"type": "group", "field": "customer__city", "direction": "asc"}
]
}
All six top-level keys are required. Grouping fields and calculations must be
distinct and currently authorized. Metric IDs use m followed by a positive
integer and ordering references a selected group field or metric ID. Every branch
of where uses the same v2 condition-tree rules. Unknown keys, client-provided
labels, unsupported numeric fields and excessive controls are rejected.
The default source cap is 10,000 authorized roots. Crossing it fails the entire summary without returning partial totals. The ordinary 10,000-row output cap is applied later to complete, deterministically ordered groups; preview and CSV then show truncation. Related grouping or metric fields activate the same target report permissions and target row policy used by row reports. Sharing a v3 definition never shares its creator’s source rows or totals.
Summary bar charts¶
When a summary groups by exactly one field and the current page contains groups, QBE displays a horizontal bar chart below the results table. Count, Sum and Average metrics are supported. With one calculation, the chart uses it directly; with several, choose Calculation to chart and select Update chart. Changing the calculation starts again at page 1, while ordinary pagination preserves the selected calculation.
The chart visualizes only the groups on the current page. It does not change the query, its saved definition or the complete CSV/Excel export. The table remains the detailed view and preserves exact values; chart bar positions are scaled for comparison. Zero and negative values use a zero-centered axis. For summaries without a grouping field, with multiple grouping fields, or with no groups on the current page, QBE explains why it cannot show a chart. These results remain available in the table and exports.
The inline SVG includes an accessible title, description, category labels and a numeric axis. Category names are shown in the chart and remain fully available in the adjacent table. The chart adds no runtime dependency and uses the already computed summary page; it performs no additional business-data query. The metric selection is temporary presentation state and is not stored with saved queries.