bM Binarymission.NET, Web & AI Products
Contact Get a License
v2.0.0

<bmx-query-builder>

A visual query builder: rules of a field, a condition and a value, in groups joined by AND or OR (with NOT on a group), nested as deep as allowed.

22 properties · 1 events · 14 methods · 9 parts

Example

Check the query Clear

The same query, filtering records in the page

toPredicate() gives a function for Array.prototype.filter: no server, no query language.

Name Country Age Spent Limit Plan Joined Tags

Read-only, as sentences

With readonly, the same query reads as text: for a summary beside a saved report.

In a form

With a name, the query goes into the form as JSON. required asks for at least one finished rule, and AND is the only join offered here.

Save the alert
Show markup 
<bmx-query-builder id="ex-qb" preview="sql mongo odata jsonlogic json text" sql-dialect="postgres" label="Customer filter"></bmx-query-builder>

<div class="row" style="margin-block-start: 0.75rem; gap: 0.75rem; flex-wrap: wrap; align-items: center">
  <label>SQL dialect
    <select id="ex-qb-dialect">
      <option value="ansi">ANSI</option>
      <option value="postgres" selected>PostgreSQL</option>
      <option value="mysql">MySQL</option>
      <option value="sqlserver">SQL Server</option>
      <option value="sqlite">SQLite</option>
      <option value="oracle">Oracle</option>
    </select>
  </label>
  <label><input type="checkbox" id="ex-qb-case" checked> Ignore case</label>
  <bmx-button variant="outline" tone="neutral" id="ex-qb-check">Check the query</bmx-button>
  <bmx-button variant="outline" tone="neutral" id="ex-qb-clear">Clear</bmx-button>
</div>
<p class="note" id="ex-qb-out" role="status"></p>

<h3>The same query, filtering records in the page</h3>

<p class="note"><code>toPredicate()</code> gives a function for <code>Array.prototype.filter</code>: no server, no query language.</p>
<div style="max-block-size: 18rem; overflow: auto; border: 1px solid var(--bmx-border, #d5dce3); border-radius: 8px">
  <table id="ex-qb-table" style="inline-size: 100%; border-collapse: collapse; font-size: 0.8125rem">
    <thead>
      <tr style="text-align: start; position: sticky; top: 0; background: var(--bmx-surface-sunken, #f6f8fa)">
        <th style="padding: 0.375rem 0.5rem; text-align: start">Name</th>
        <th style="padding: 0.375rem 0.5rem; text-align: start">Country</th>
        <th style="padding: 0.375rem 0.5rem; text-align: end">Age</th>
        <th style="padding: 0.375rem 0.5rem; text-align: end">Spent</th>
        <th style="padding: 0.375rem 0.5rem; text-align: end">Limit</th>
        <th style="padding: 0.375rem 0.5rem; text-align: start">Plan</th>
        <th style="padding: 0.375rem 0.5rem; text-align: start">Joined</th>
        <th style="padding: 0.375rem 0.5rem; text-align: start">Tags</th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>

<h3>Read-only, as sentences</h3>

<p class="note">With <code>readonly</code>, the same query reads as text: for a summary beside a saved report.</p>
<bmx-query-builder id="ex-qb-read" readonly></bmx-query-builder>

<h3>In a form</h3>

<p class="note">With a <code>name</code>, the query goes into the form as JSON. <code>required</code> asks for at least one finished rule, and AND is the only join offered here.</p>
<form id="ex-qb-form" style="display: grid; gap: 0.75rem">
  <bmx-query-builder id="ex-qb-alert" name="alertRule" required combinators="and" max-depth="2" label="Alert when"></bmx-query-builder>
  <div class="row" style="gap: 0.5rem">
    <bmx-button type="submit">Save the alert</bmx-button>
  </div>
</form>
<pre class="note" id="ex-qb-form-out" style="white-space: pre-wrap" hidden></pre>

<script type="module">
  await customElements.whenDefined('bmx-query-builder');

  const countries = [
    ['UK', 'United Kingdom'],
    ['IE', 'Ireland'],
    ['FR', 'France'],
    ['DE', 'Germany'],
    ['ES', 'Spain'],
    ['IT', 'Italy'],
    ['NL', 'Netherlands'],
    ['SE', 'Sweden'],
    ['US', 'United States'],
    ['CA', 'Canada'],
    ['AU', 'Australia'],
    ['JP', 'Japan'],
    ['IN', 'India'],
    ['BR', 'Brazil'],
  ].map(([value, label]) => ({ value, label }));

  const fields = [
    { name: 'name', label: 'Name', group: 'Customer', placeholder: 'Text to match' },
    { name: 'email', label: 'Email', group: 'Customer', pattern: '^[^@\\s]*@?[^@\\s]*$', patternMessage: 'Part of an email address, with no spaces.' },
    { name: 'country', label: 'Country', type: 'list', options: countries, group: 'Customer' },
    { name: 'address.city', label: 'City', group: 'Customer' },
    { name: 'age', label: 'Age', type: 'number', integer: true, min: 16, max: 120, group: 'Customer' },
    { name: 'plan', label: 'Plan', type: 'list', options: [{ value: 'free', label: 'Free' }, { value: 'team', label: 'Team' }, { value: 'business', label: 'Business' }], group: 'Account' },
    { name: 'spent', label: 'Spent (£)', type: 'number', compareTo: true, group: 'Account', description: 'Lifetime spend' },
    { name: 'limit', label: 'Credit limit (£)', type: 'number', group: 'Account' },
    { name: 'joined', label: 'Joined', type: 'date', group: 'Account' },
    { name: 'lastOrder', label: 'Last order', type: 'datetime', group: 'Account' },
    { name: 'member', label: 'Newsletter', type: 'boolean', group: 'Account' },
    { name: 'tags', label: 'Tags', type: 'list', multiple: true, options: ['vip', 'trade', 'new', 'at-risk', 'reseller'].map(value => ({ value })), group: 'Account' },
  ];

  const today = new Date();
  const day = n => {
    const d = new Date(today);
    d.setDate(d.getDate() - n);
    return d.toISOString().slice(0, 10);
  };
  const first = ['Ann', 'Ben', 'Chloe', 'Dev', 'Ella', 'Finn', 'Gita', 'Hugo', 'Isla', 'Jon', 'Kemi', 'Liam', 'Maya', 'Noah', 'Orla', 'Pavel'];
  const last = ['Smith', 'Okafor', 'Martin', 'Patel', 'Rossi', 'Novak', 'Kim', 'Dubois', 'Garcia', 'Brown'];
  const cities = { UK: 'York', IE: 'Cork', FR: 'Lyon', DE: 'Bremen', ES: 'Seville', IT: 'Turin', NL: 'Utrecht', SE: 'Malmö', US: 'Denver', CA: 'Halifax', AU: 'Perth', JP: 'Osaka', IN: 'Pune', BR: 'Recife' };
  const plans = ['free', 'team', 'business'];
  const tagSets = [['vip'], [], ['trade', 'reseller'], ['new'], ['at-risk'], ['vip', 'trade'], [], ['new', 'at-risk']];
  const people = Array.from({ length: 40 }, (_, i) => {
    const country = countries[(i * 5) % countries.length].value;
    return {
      name: `${first[i % first.length]} ${last[(i * 3) % last.length]}`,
      email: `${first[i % first.length].toLowerCase()}@example.${country.toLowerCase()}`,
      country,
      address: { city: cities[country] },
      age: 18 + ((i * 13) % 60),
      plan: plans[(i * 5) % 3],
      spent: Math.round(((i * 977) % 9000) + 120),
      limit: [1000, 2500, 5000, 10000][i % 4],
      joined: day(3 + ((i * 41) % 900)),
      lastOrder: `${day((i * 5) % 60)}T${String(8 + (i % 10)).padStart(2, '0')}:15`,
      member: i % 3 !== 0,
      tags: tagSets[i % tagSets.length],
    };
  });

  const qb = document.getElementById('ex-qb');
  const read = document.getElementById('ex-qb-read');
  const out = document.getElementById('ex-qb-out');
  const tbody = document.querySelector('#ex-qb-table tbody');
  qb.fields = fields;
  read.fields = fields;
  qb.value = {
    combinator: 'and',
    rules: [
      { field: 'country', operator: 'in', value: ['UK', 'IE', 'FR', 'DE'] },
      { field: 'age', operator: 'between', value: [25, 60] },
      {
        combinator: 'or',
        rules: [
          { field: 'spent', operator: 'greater', value: 'limit', valueSource: 'field' },
          { field: 'tags', operator: 'hasAny', value: ['vip', 'trade'] },
        ],
      },
      { field: 'joined', operator: 'inLast', value: { amount: 2, unit: 'year' } },
    ],
  };

  const label = value => countries.find(c => c.value === value)?.label ?? value;
  const cell = (text, end) => {
    const td = document.createElement('td');
    td.textContent = text;
    td.style.padding = '0.3rem 0.5rem';
    td.style.borderBlockStart = '1px solid var(--bmx-border, #e5e9ef)';
    if (end) td.style.textAlign = 'end';
    return td;
  };
  const money = new Intl.NumberFormat('en-GB', { style: 'currency', currency: 'GBP', maximumFractionDigits: 0 });

  async function refresh() {
    const keep = await qb.toPredicate();
    const rows = people.filter(keep);
    tbody.replaceChildren(
      ...rows.map(p => {
        const tr = document.createElement('tr');
        tr.append(cell(p.name), cell(label(p.country)), cell(p.age, true), cell(money.format(p.spent), true), cell(money.format(p.limit), true), cell(p.plan), cell(p.joined), cell(p.tags.join(', ')));
        return tr;
      }),
    );
    out.textContent = `${rows.length} of ${people.length} customers match.`;
    read.value = await qb.getQuery();
  }

  qb.addEventListener('bmxQueryChange', refresh);
  await refresh();

  document.getElementById('ex-qb-dialect').addEventListener('change', e => (qb.sqlDialect = e.target.value));
  document.getElementById('ex-qb-case').addEventListener('change', async e => {
    qb.ignoreCase = e.target.checked;
    await refresh();
  });
  document.getElementById('ex-qb-check').addEventListener('click', async () => {
    const errors = await qb.validate();
    out.textContent = errors.length ? `${errors.length} rule${errors.length === 1 ? ' needs' : 's need'} attention: ${errors[0].message}` : 'Every rule is finished and valid.';
  });
  document.getElementById('ex-qb-clear').addEventListener('click', () => qb.clear());

  const alert = document.getElementById('ex-qb-alert');
  alert.fields = [
    { name: 'cpu', label: 'CPU (%)', type: 'number', min: 0, max: 100 },
    { name: 'memory', label: 'Memory (%)', type: 'number', min: 0, max: 100 },
    { name: 'host', label: 'Host name' },
    { name: 'env', label: 'Environment', type: 'list', options: [{ value: 'prod', label: 'Production' }, { value: 'test', label: 'Test' }] },
  ];
  const form = document.getElementById('ex-qb-form');
  const formOut = document.getElementById('ex-qb-form-out');
  form.addEventListener('submit', e => {
    e.preventDefault();
    formOut.hidden = false;
    formOut.textContent = `alertRule = ${new FormData(form).get('alertRule')}`;
  });
</script>

The page describes its fields - a name, a label and a type (text, number, date, date and time, yes/no, a list of choices or several of them, or a custom type with an editor of its own) - and each field offers the conditions that suit it, with a value editor to match. Rules and groups are added, copied, switched off, removed, and moved by dragging or with the arrow keys.

The query is JSON in and out, and the builder writes it as a parameterised SQL condition (ANSI, PostgreSQL, MySQL, SQL Server, SQLite, Oracle), a MongoDB filter, an OData $filter, JsonLogic, a sentence, or a function that filters an array in the page (toPredicate). The same conversions are exported for a server. Nothing is sent anywhere.

qb.fields = [{ name: 'country', type: 'list', options }, { name: 'age', type: 'number' }]; const { sql, params } = await qb.toSql();

Keyboard: on a rule's or group's grip, Up and Down move it through the query, into and out of groups, and say where it went. The AND/OR choice is a pair of radio buttons (arrow keys), and the preview's outputs are tabs.

Properties

PropertyAttributeTypeDefaultDescription
allowClone allow-clone boolean true Offers a button to copy a rule or a group.
allowDisable allow-disable boolean true Offers a button to switch a rule or a group off without removing it.
allowDrag allow-drag boolean true Rules and groups can be moved by dragging and with the arrow keys.
allowGroups allow-groups boolean true Offers groups inside groups.
allowNot allow-not boolean true Offers NOT on groups.
combinators combinators string 'and or' The joins offered: and or (default), and or or.
disabled disabled boolean false Turns every control off.
fields fields BmxQueryField[] | string [] The fields rules can test: [{ name, label, type, options }], as a property or JSON.
ignoreCase ignore-case boolean true Text compares without case, in every conversion.
label label string — The builder's accessible name.
locale locale string — The language numbers and dates are written in. The reader's, by default.
maxDepth max-depth number 5 The most levels of groups, the outer one included.
name name string — With a name, the query goes into an enclosing <form> as JSON under it.
operators property only BmxQueryOperator[] — Operators of the page's own, or conversions for built-in ones. A property.
preview preview string '' Outputs to show under the builder, in tabs: sql mongo odata jsonlogic json text. None, by default.
readonly readonly boolean false Shows the query as sentences rather than as controls.
required required boolean false An enclosing form will not send without at least one finished rule.
searchThreshold search-threshold number 12 With more fields or choices than this, the pickers can be typed into to search.
sqlDialect sql-dialect BmxQuerySqlDialect 'ansi' The SQL the preview shows and toSql writes by default.
strings strings Partial<BmxQueryBuilderStrings> | string — Replacements for the builder's wording, as a property or JSON.
validateOn validate-on 'blur' | 'change' | 'submit' 'blur' When a rule shows its problem: once its value is left (blur), as it changes (change), or only on validate() and form sending (submit).
value value BmxQueryGroup | string | null null The query, as a property or JSON. Changes write back here, with ids. Queries from jQuery QueryBuilder and react-querybuilder are read too.

Events

EventDetailDescription
bmxQueryChange BmxQueryChangeDetail The query changed.

Methods

MethodSignatureDescription
addGroupTo addGroupTo(groupId?: string, group?: Partial<BmxQueryGroup>) => Promise<string> Adds a group to a group (the outer one by default) and gives its id.
addRuleTo addRuleTo(groupId?: string, rule?: Partial<BmxQueryRule>) => Promise<string> Adds a rule to a group (the outer one by default) and gives its id.
clear clear() => Promise<void> Removes every rule.
getQuery getQuery(keepIds?: boolean) => Promise<BmxQueryGroup> The query as JSON: with ids (default), or without.
removeItem removeItem(id: string) => Promise<void> Removes a rule or a group.
setFocus setFocus() => Promise<void> Puts focus on the first control.
setQuery setQuery(query: unknown) => Promise<void> Replaces the query. Queries from jQuery QueryBuilder and react-querybuilder are read too.
toJsonLogic toJsonLogic(options?: BmxQueryConvertOptions) => Promise<unknown> The query as JsonLogic.
toMongo toMongo(options?: BmxQueryMongoOptions) => Promise<Record<string, unknown>> The query as a MongoDB filter.
toOData toOData(options?: BmxQueryODataOptions) => Promise<string> The query as an OData $filter expression.
toPredicate toPredicate(options?: BmxQueryConvertOptions) => Promise<(record: unknown) => boolean> A function that says whether a record matches: rows.filter(await qb.toPredicate()).
toSql toSql(options?: BmxQuerySqlOptions) => Promise<BmxQuerySql> The query as a parameterised SQL condition, in sql-dialect unless the options say otherwise.
toText toText(options?: BmxQueryTextOptions) => Promise<string> The query as a sentence.
validate validate() => Promise<BmxQueryError[]> Every problem, shown on the rules. An empty list when the query is valid.

CSS shadow parts

PartDescription
builder the outer box.
code the output itself.
error
group a group of rules.
group-header a group's join, NOT and buttons.
handle the grip a rule or group is moved by.
items the list of a group's rules and groups.
preview the output panel.
rule a rule's row.

CSS custom properties

PropertyDescription
--bmx-query-builder-accent The chosen join, the rail and the drop marker.
--bmx-query-builder-background Behind the outer group.
--bmx-query-builder-border Lines around groups and the preview.
--bmx-query-builder-code-background Behind the preview's output.
--bmx-query-builder-group-background Behind a group inside another.
--bmx-query-builder-not-color A group's NOT when it is on.
--bmx-query-builder-or-accent The rail and join of an OR group.
--bmx-query-builder-radius Corners of groups and rules.
--bmx-query-builder-rule-background Behind a rule's row.