Table Backend Filters

How frontend DataTable filters connect to backend query parameters, validation, and SQL query construction.

Overview

When a user interacts with a DataTable filter in the frontend, the following flow occurs:

Each filter requires changes in up to four layers:

Layer	File	Purpose
Frontend filter	ListView.vue (or equivalent)	UI control definition
Frontend defaults	table.defaults.js	Default filter visibility
Route validation	*.routes.js	Joi schema for query params
Backend filter	*.controller.js or *.util.js	SQL WHERE clause construction

Db.whereBuilder Utility

The core utility for building conditional SQL WHERE clauses.

Location: backend/config/database.js

pool.whereBuilder = (whereEvaluations) => {
    let where = '';
    whereEvaluations.forEach((item) => {
        const evaluation = item.evaluation ?? item.evluation;
        if (evaluation) {
            where += `${item.pass} \n`;
        } else {
            where += item.fail ? `${item.fail} \n` : '';
        }
    });
    return where;
};

How It Works

Db.whereBuilder receives an array of condition objects. Each object has:

Property	Type	Description
evaluation	any	Truthy value triggers the condition (also accepts evluation for backward compat)
pass	string	SQL fragment appended when evaluation is truthy
fail	string	(Optional) SQL fragment appended when evaluation is falsy

Example

const whereBuilder = Db.whereBuilder([
    {
        evaluation: data.follow,
        pass: `AND p.follow_up_id IN (${Db.escapeMultiString(data.follow)})`,
    },
    {
        evaluation: data.type === 'company',
        pass: `AND p.company_id IS NOT NULL`,
    },
    {
        evaluation: data.search,
        pass: `AND p.name LIKE ${Db.escape(`%${data.search}%`)}`,
    },
]);
// Result (if follow='5' and search='acme'):
// "AND p.follow_up_id IN ('5') \n AND p.name LIKE '%acme%' \n"

Security: Input Escaping

Always escape user input:

Method	Usage	Example
Db.escape(value)	Single value	Db.escape(data.search) → 'acme'
Db.escapeMultiString(csv)	Comma-separated values	Db.escapeMultiString('1,2,3') → '1','2','3'

IMPORTANT

Never concatenate raw user input into SQL. Always use Db.escape() or Db.escapeMultiString().

constructFilters Pattern

Complex tables extract filter logic into a utility class method. This keeps the controller clean and centralizes filter construction.

Location: backend/libraries/prospects.util.js

async constructFilters(request) {
    const data = request.query;

    const whereBuilder = Db.whereBuilder([
        {
            evaluation: data.follow,
            pass: `AND p.follow_up_id IN (${Db.escapeMultiString(data.follow)})`,
        },
        {
            evaluation: data.type === 'company',
            pass: `AND p.company_id IS NOT NULL`,
        },
        {
            evaluation: data.search,
            pass: `AND (
                p.name LIKE ${Db.escape(`%${data.search}%`)}
                OR p.email LIKE ${Db.escape(`%${data.search}%`)}
            )`,
        },
    ]);

    const whereDate = Db.whereBuilder([
        {
            evaluation: data.startDate,
            pass: `
                AND created_at >= ${Db.escape(`${data.startDate} 00:00:00`)}
                AND created_at <= ${Db.escape(`${data.endDate} 23:59:59`)}
            `,
        },
    ]);

    return {
        whereBuilder,
        whereDate,
        // ... other WHERE fragments
    };
}

Returned Fragments

The constructFilters function returns an object where each property is a SQL WHERE fragment (or empty string if the filter is not active):

Property	Filter Source	Applied To
whereBuilder	follow, type, search, weightRange, minShipments, leadStatus	Main query WHERE clause
whereDate	startDate, endDate	Company and prospect subqueries
filterDate	Auto (MDR role)	30-day default date range
whereMkt	campaing	JOIN company_ads_tiers condition
whereOnBording	onboarding	JOIN about_yourself condition
whereAdminstrators	Role-based fields (kae, csr, etc.)	Company subquery
whereEcommerce	ecommerce	JOIN shops condition
whereNoEcommerce	ecommerce (without)	NOT EXISTS subquery
whereOrigin	salesman / auto	Prospect subquery

Applying Fragments in the Controller

Fragments are interpolated into the query's structure (JOIN/WHERE section):

const { whereBuilder, whereDate, whereMkt, whereNextContact } =
    await utils.prospects.constructFilters(request);

const configQuery = {
    select: `p.*, ...`,
    from: `(...) AS p`,
    structure: `
        JOIN catalog_follow_up_statuses AS cfs
            ON p.follow_up_id = cfs.id
        ${whereMkt ? `
            JOIN company_ads_tiers AS cat
                ON cat.company_id = p.company_id
                ${whereMkt}
        ` : ''}
        WHERE 1 = 1
            ${whereBuilder}
            ${whereNextContact}
            ${whereHasRecharge}
    `,
};

Joi Route Validation

Every query parameter must be declared in the Hapi route's Joi schema. Without this, the parameter is rejected with a "paramName" is not allowed error.

Location: backend/routes/prospects.routes.js

{
    method: 'GET',
    path: '/prospects',
    handler: controller.getList,
    options: {
        validate: {
            query: Joi.object({
                start: Joi.number().default(0),
                length: Joi.number().default(25),
                sortBy: Joi.string().optional(),
                sortType: Joi.string().valid('asc', 'desc').optional(),
                // Filters
                search: Joi.string().optional(),
                follow: Joi.string().optional(),
                type: Joi.string().optional(),
                campaing: Joi.string().optional(),
                ecommerce: Joi.string().optional(),
                weightRange: Joi.string().optional(),
                minShipments: Joi.number().integer().min(0).optional(),
                leadStatus: Joi.string().optional(),
                hasRecharge: Joi.string().valid('0', '1').optional(),
                // Date range filters use bracket notation
                'nextContact[0]': Joi.string().optional(),
                'nextContact[1]': Joi.string().optional(),
                source: Joi.string().valid('company', 'partner').default('company'),
            }),
            failAction: (_request, _response, err) => Boom.badData(err),
        },
    },
},

Common Joi Types for Filters

Filter Type	Joi Schema	Example
Text search	Joi.string().optional()	search, campaing
Numeric	Joi.number().integer().min(0).optional()	minShipments
Single select	Joi.string().optional()	type, weightRange
Multi-select (CSV)	Joi.string().optional()	follow (sent as '1,2,3')
Boolean-like	Joi.string().valid('0', '1').optional()	hasRecharge
Date range	Two params: Joi.string().optional()	'nextContact[0]', 'nextContact[1]'
Enum	Joi.string().valid('value1', 'value2').optional()	source

Filter Type Patterns

Boolean/Existence Filter

Frontend: select with Yes/No options. Backend: EXISTS / NOT EXISTS.

Frontend:

hasRecharge: {
    className: 'col-12 col-lg-2 mb-2',
    label: t('crm.columns.hasRecharge'),
    el: 'select',
    options: [
        { value: '1', text: t('labels.yes') },
        { value: '0', text: t('labels.no') },
    ],
},

Route validation:

hasRecharge: Joi.string().valid('0', '1').optional(),

Backend:

let whereHasRecharge = '';
if (data.hasRecharge === '1') {
    whereHasRecharge = `AND EXISTS (SELECT 1 FROM payment_history AS ph WHERE ph.company_id = p.company_id)`;
} else if (data.hasRecharge === '0') {
    whereHasRecharge = `AND NOT EXISTS (SELECT 1 FROM payment_history AS ph WHERE ph.company_id = p.company_id)`;
}

Multi-Select Filter (CSV)

Frontend: select2 with mode: 'tags'. Backend: IN (...) with Db.escapeMultiString.

Frontend:

follow: {
    className: 'col-12 col-lg-2 mb-2',
    label: t('datatable.column.follow'),
    el: 'select2',
    options: categories.value,
    placeholder: t('header.all'),
    mode: 'tags',
    groups: true,
},

Route validation:

follow: Joi.string().optional(), // Received as "1,2,3"

Backend (via whereBuilder):

{
    evaluation: data.follow,
    pass: `AND p.follow_up_id IN (${Db.escapeMultiString(data.follow)})`,
},

Date Range Filter

Frontend: datePicker. Backend: BETWEEN or >= / <= with escaped dates.

Frontend:

nextContact: {
    className: 'col-12 col-lg-2 mb-2',
    label: t('crm.columns.nextContact'),
    el: 'datePicker',
},

Route validation:

'nextContact[0]': Joi.string().optional(),
'nextContact[1]': Joi.string().optional(),

Backend:

let whereNextContact = '';
if (data['nextContact[0]'] && data['nextContact[1]']) {
    const ncStart = Db.escape(`${data['nextContact[0]']} 00:00:00`);
    const ncEnd = Db.escape(`${data['nextContact[1]']} 23:59:59`);
    whereNextContact = `AND (next_contact_subquery) BETWEEN ${ncStart} AND ${ncEnd}`;
}

Text Search Filter

Frontend: input with debounce. Backend: LIKE with wildcards.

Frontend:

search: {
    className: 'col-12 col-lg-2 mb-2',
    label: t('filters.search'),
    el: 'input',
    placeholder: t('filters.search.placeholder'),
    debounce: 2000,
    locked: true,
},

Route validation:

search: Joi.string().optional(),

Backend (via whereBuilder):

{
    evaluation: data.search,
    pass: `AND (
        p.name LIKE ${Db.escape(`%${data.search}%`)}
        OR p.email LIKE ${Db.escape(`%${data.search}%`)}
    )`,
},

Numeric Minimum Filter

Frontend: input with type: 'number'. Backend: numeric comparison.

Frontend:

minShipments: {
    className: 'col-12 col-lg-2 mb-2',
    label: t('crm.columns.estimatedShipments'),
    el: 'input',
    placeholder: t('filters.min'),
    type: 'number',
    debounce: 2000,
},

Route validation:

minShipments: Joi.number().integer().min(0).optional(),

Backend (via whereBuilder):

{
    evaluation: data.minShipments != null,
    pass: `AND CAST(p.fud_monthly_shipments AS UNSIGNED) >= ${Db.escape(data.minShipments)}`,
},

End-to-End Guide: Adding a New Filter

This walkthrough uses hasRecharge (boolean existence filter) as a real example.

Step 1: Frontend Filter Definition

Add the filter to allFilters in your view component:

// In ListView.vue (or equivalent)
const allFilters = computed(() => ({
    // ... existing filters
    hasRecharge: {
        className: 'col-12 col-lg-2 mb-2',
        label: t('crm.columns.hasRecharge'),
        el: 'select',
        options: [
            { value: '1', text: t('labels.yes') },
            { value: '0', text: t('labels.no') },
        ],
    },
}));

Step 2: Frontend Defaults Entry

Add the filter to the defaults file so the preferences system knows about it:

// In table.defaults.js
filters: [
    // ... existing filters
    { key: 'hasRecharge', visible: false },
],

Step 3: Backend Route Validation

Add the parameter to the Joi schema in the route file:

// In prospects.routes.js
validate: {
    query: Joi.object({
        // ... existing params
        hasRecharge: Joi.string().valid('0', '1').optional(),
    }),
},

WARNING

Forgetting this step causes a "hasRecharge" is not allowed validation error.

Step 4: Backend WHERE Clause

Build the SQL fragment in the controller (or utility):

// In prospects.controller.js
let whereHasRecharge = '';
if (data.hasRecharge === '1') {
    whereHasRecharge = `AND EXISTS (
        SELECT 1 FROM payment_history AS ph
        WHERE ph.company_id = p.company_id
    )`;
} else if (data.hasRecharge === '0') {
    whereHasRecharge = `AND NOT EXISTS (
        SELECT 1 FROM payment_history AS ph
        WHERE ph.company_id = p.company_id
    )`;
}

Step 5: Apply to Query

Interpolate the fragment into the query's WHERE clause:

structure: `
    JOIN catalog_follow_up_statuses AS cfs
        ON p.follow_up_id = cfs.id
    WHERE 1 = 1
        ${whereBuilder}
        ${whereNextContact}
        ${whereHasRecharge}
`,

Step 6: Add i18n Translation

Add the translation key to the language files (S3-hosted JSON):

{
    "crm": {
        "columns": {
            "hasRecharge": "Has recharge"
        }
    }
}

End-to-End Guide: Adding a New Column

This walkthrough uses last_recharge_date as a real example.

Step 1: Backend SQL Subquery

Add the computed column to the SELECT clause in the controller:

// In prospects.controller.js (within configQuery.select)
(SELECT MAX(ph.created_at)
 FROM payment_history AS ph
 WHERE ph.company_id = p.company_id
) AS last_recharge_date

Step 2: Frontend Column Definition

Add to allColumns in the view component:

{
    text: t('crm.columns.lastRechargeDate'),
    value: 'last_recharge_date',
    sortable: false,
},

Step 3: Frontend Column Defaults

Add to the defaults file:

{ value: 'last_recharge_date', visible: false, order: 20 },

Step 4: Frontend Template Slot

Add a custom render slot if needed:

<template #item-last_recharge_date="item">
    <span v-if="item.last_recharge_date">
        {{ utils.convertDate(item.last_recharge_date, 'short') }}
    </span>
    <span v-else class="text-muted">&mdash;</span>
</template>

Step 5: Add i18n Translation

{
    "crm": {
        "columns": {
            "lastRechargeDate": "Recharge date"
        }
    }
}

Step 6 (Optional): Make it Sortable

If the column should be sortable, add it to the allowedSorts map in the controller:

const allowedSorts = {
    // ... existing sorts
    last_recharge_date: 'last_recharge_date',
};

And set sortable: true in the column definition.

Where Filters Are Applied in the Query

Different filter fragments are applied at different levels of the query hierarchy:

Key Rule

• Filters on outer query columns (computed fields like last_contact, next_contact) go in the structure WHERE clause.
• Filters on source table columns (created_at, follow_up_id, role assignments) go in the respective subquery WHERE clause.
• Filters requiring JOINs (e-commerce, campaigns, onboarding) add both the JOIN and the condition in the subquery.

Related Documentation

• DataTable Component - Core DataTable props, filters, slots, and examples
• Table Preferences - User-customizable column and filter visibility