README

Universal JSON query DSL parser for Laravel Eloquent. Accepts a structured JSON payload and converts it into Eloquent queries with filtering, sorting, pagination, full-text search, relation filtering, and JSON field support.

Requirements
Installation
Quick Start
How It Works
Model Configuration
JSON Payload Format
API Reference
Aggregation
Operator Auto-Resolution
Custom Filters
Configuration
Validation & Error Handling
Security Considerations

Requirements

PHP 8.2, 8.3, or 8.4
Laravel 11, 12, or 13 (illuminate/database + illuminate/support)

Installation

Add the package to your project via Composer:

composer require dartvadius/eloquent-search

Laravel auto-discovers the service provider. To publish the config file:

php artisan vendor:publish --tag=eloquent-search-config

This creates config/eloquent-search.php with default settings.

Quick Start

1. Prepare your model

Add the Searchable trait and define searchableConfig():

use DartVadius\EloquentSearch\Searchable;
use DartVadius\EloquentSearch\SearchableConfig;

class Task extends Model
{
    use Searchable;

    public function searchableConfig(): SearchableConfig
    {
        return SearchableConfig::make()
            ->fields(['id', 'title', 'employee_id', 'scheduled_time', 'created_at'])
            ->nullable(['employee_id'])
            ->sortable(['id', 'scheduled_time', 'created_at'])
            ->defaultSort('scheduled_time', 'asc');
    }
}

2. Use in a controller

use DartVadius\EloquentSearch\SearchQuery;

public function search(Request $request)
{
    $query = Task::where('company_id', $request->user()->company_id);

    $result = SearchQuery::apply($query, $request->json()->all());

    return response()->json($result);
}

3. Send a JSON request

POST /api/tasks/search
Content-Type: application/json

{
    "where": {
        "eq": { "employee_id": 42 },
        "between": { "scheduled_time": ["2026-04-01 00:00:00", "2026-04-30 23:59:59"] }
    },
    "sort": [{ "field": "scheduled_time", "dir": "desc" }],
    "page": 1,
    "per_page": 25
}

Response:

{
    "data": [{ "id": 1, "title": "...", "..." }],
    "total": 42,
    "page": 1,
    "per_page": 25,
    "last_page": 2
}

How It Works

JSON payload
    |
    v
PayloadValidator        -- validates structure, types, limits
    |
    v
OperatorResolver        -- reads model casts/schema, resolves allowed operators per field
    |
    v
QueryParser             -- converts JSON operators into Eloquent where/whereIn/etc. calls
    |
    v
SortApplier             -- applies sorting or default sort
    |
    v
SearchPaginator         -- paginates or returns count_only
    |
    v
Eloquent Builder result

The package never touches your base query. You set up any authorization scopes, joins, or conditions you need before passing the Builder to SearchQuery. The DSL is applied on top.

Model Configuration

The searchableConfig() method returns a SearchableConfig instance that defines what's allowed and how.

Fields

The field whitelist controls which columns can be filtered. Fields not in this list are silently ignored (or throw an exception, depending on config).

SearchableConfig::make()
    ->fields([
        'id',                        // auto-resolves operators from column type
        'title',                     // string -> eq, not_eq, like, in, not_in
        'employee_id',               // integer -> eq, not_eq, in, not_in, gt, lt, gte, lte, between
        'scheduled_time',            // datetime -> eq, between, gt, lt, gte, lte
        'is_recurring',              // boolean -> eq, not_eq
        'status' => ['eq', 'in'],    // explicit override: only these operators allowed
    ]);

Operators are auto-resolved from the model's $casts or the database schema. See Operator Auto-Resolution for the full mapping.

Nullable columns detected from the database schema automatically get the is_null operator added. For columns where schema detection is unreliable (or the column is NOT NULL in the schema but you still need is_null filtering), use the explicit nullable() method below.

Nullable Fields

Explicitly mark fields that should support the is_null operator, regardless of their database schema:

->nullable(['employee_id', 'client_id', 'parent_id'])

This guarantees is_null is available for these fields without relying on database schema introspection. Useful when:

The column is NOT NULL in the schema but you need to filter by null values
Schema detection fails due to caching or connection issues
You want explicit control over which fields support null filtering

Fields listed in nullable() get is_null added to their auto-resolved operators. Schema-based nullable detection still works as a fallback for all other fields.

JSON Fields

Mark columns that store JSON arrays (e.g., tags, marks, skills):

->jsonFields(['marks', 'skills'])

This enables the json_contains and json_contains_all operators for these fields, regardless of their $casts type.

Sorting

Define which fields can appear in the sort payload:

->sortable(['id', 'title', 'scheduled_time', 'created_at'])
->defaultSort('scheduled_time', 'asc')

If the client doesn't send sort or sends only non-whitelisted fields, the defaultSort is applied. If no defaultSort is configured, no sorting is applied.

Search Fields

Define fields for full-text search (the search operator). Supports dot notation for related model fields:

->searchFields(['title', 'employee.first_name', 'employee.last_name'])

When the client sends "search": "John", the query becomes:

WHERE (title LIKE '%John%' OR EXISTS (
    SELECT * FROM employees WHERE employees.id = tasks.employee_id
    AND (first_name LIKE '%John%' OR last_name LIKE '%John%')
))

If searchFields is not configured, the search operator is silently ignored.

Extending Search (searchUsing)

For searching data that cannot be expressed via searchFields (pivot tables, computed fields, custom fields with business logic), use searchUsing():

->searchUsing(function (\Illuminate\Database\Eloquent\Builder $query, string $term) {
    // Called inside the OR group alongside searchFields.
    // Use $query->orWhere / orWhereIn / orWhereHas to add conditions.
    // $term is the original search term (not escaped).
})

The callback is invoked inside the shared WHERE (...) search group, so all conditions are OR-combined with the rest of the search fields.

Example: searching custom fields on tasks

// In the controller — company_id is available
$config = (new Task)->searchableConfig()
    ->searchUsing(function (Builder $query, string $term) use ($company) {
        $searchableFieldIds = TaskCustomField::where('company_id', $company->id)
            ->where('searchable', true)
            ->pluck('id');

        if ($searchableFieldIds->isEmpty()) {
            return;
        }

        $taskIds = TaskCustomFieldValue::whereIn('task_custom_field_id', $searchableFieldIds)
            ->where('value', 'LIKE', "%{$term}%")
            ->pluck('task_id')
            ->toArray();

        if (!empty($taskIds)) {
            $query->orWhereIn('id', $taskIds);
        }
    });

// Pass the augmented config to build()
$builder = SearchQuery::build($query, $payload, [], $config);

You can register multiple callbacks — all will be invoked within the same OR group:

$config = (new Task)->searchableConfig()
    ->searchUsing($this->customFieldSearchCallback($company))
    ->searchUsing($this->anotherSearchCallback());

When to use searchUsing instead of searchFields:

Searching pivot tables (custom fields, tags via intermediate table)
Searching with business logic (e.g., only fields with the searchable flag)
Searching computed values or subqueries
When controller context is needed ($company, $user)

Relations

Define which model relations can be filtered via the has operator:

->relations([
    'latestLog' => ['status_id'],
    'client' => ['name', 'email', 'phone'],
    'employee' => ['id', 'first_name', 'last_name'],
])

Each relation maps to an array of allowed fields within that relation. Fields not in this list are ignored.

Custom Filters

->filter('task_status', new TaskStatusFilter())

See Custom Filters section for details.

JSON Payload Format

The payload is a JSON object with these top-level keys:

Key	Type	Required	Description
`where`	object	No	AND filter conditions
`or`	object	No	OR filter conditions (combined with `where` as `WHERE (where) OR (or)`)
`sort`	array	No	Sorting rules
`search`	string	No	Full-text search term (can also be inside `where`)
`page`	integer	No	Page number (enables pagination)
`per_page`	integer	No	Results per page (default: 25, max: 1000)
`count_only`	boolean	No	If true, returns only `{"total": N}`
`aggregate`	object	No	Aggregation spec (GROUP BY) — see Aggregation

⚠️ A valid, well-formed payload is required. The structure above is strict: wrong types or malformed blocks — where that isn't an object, a between without exactly two values, a non-integer page, etc. — are rejected with InvalidPayloadException → HTTP 422. Unknown field names and operators are tolerated (silently skipped), but the shape of each block is enforced. See Validation & Error Handling.

Operators

All operators are keys inside the where (or or) object. Each operator maps field names to values:

{
    "where": {
        "OPERATOR": {
            "FIELD": "VALUE"
        }
    }
}

Comparison operators

Operator	SQL	Value type	Example
`eq`	`= value`	scalar	`{"eq": {"status": "active"}}`
`not_eq`	`!= value`	scalar	`{"not_eq": {"status": "cancelled"}}`
`gt`	`> value`	scalar	`{"gt": {"id": 100}}`
`gte`	`>= value`	scalar	`{"gte": {"price": 50.00}}`
`lt`	`< value`	scalar	`{"lt": {"id": 1000}}`
`lte`	`<= value`	scalar	`{"lte": {"price": 200.00}}`

Set operators

Operator	SQL	Value type	Example
`in`	`IN (...)`	non-empty array	`{"in": {"employee_id": [1, 2, 3]}}`
`not_in`	`NOT IN (...)`	non-empty array	`{"not_in": {"status": [5, 6]}}`

Range operator

Operator	SQL	Value type	Example
`between`	`BETWEEN a AND b`	array of 2 elements	`{"between": {"scheduled_time": ["2026-04-01", "2026-04-30"]}}`

String operator

Operator	SQL	Value type	Example
`like`	`LIKE '%value%'`	string	`{"like": {"title": "repair"}}`

The % wrapping is automatic. Special characters (%, _, !) are auto-escaped.

Null operators

Operator	SQL	Value type	Example
`is_null`	`IS NULL` / `IS NOT NULL`	boolean or array	`{"is_null": {"cancelled_at": true}}`

Object format: {"is_null": {"field": true}} — true = IS NULL, false = IS NOT NULL.

Array shorthand: {"is_null": ["field1", "field2"]} — all listed fields must be NULL. Equivalent to {"is_null": {"field1": true, "field2": true}}.

JSON operators

Operator	SQL	Logic	Example
`json_contains`	`JSON_CONTAINS`	ANY (OR)	`{"json_contains": {"marks": [1, 2]}}` — has mark 1 OR 2
`json_contains_all`	`JSON_CONTAINS`	ALL (AND)	`{"json_contains_all": {"skills": [5, 10]}}` — has skill 5 AND 10

Both accept a scalar (single value) or an array (multiple values).

Combining operators

All operators within a where block are combined with AND:

{
    "where": {
        "eq": { "employee_id": 42 },
        "between": { "scheduled_time": ["2026-04-01", "2026-04-30"] },
        "is_null": { "cancelled_at": true }
    }
}

Generates:

WHERE employee_id = 42
  AND scheduled_time BETWEEN '2026-04-01' AND '2026-04-30'
  AND cancelled_at IS NULL

Sorting

{
    "sort": [
        { "field": "scheduled_time", "dir": "desc" },
        { "field": "id", "dir": "asc" }
    ]
}

field (required) must be in the model's sortable() whitelist
dir (optional) defaults to "asc". Valid values: "asc", "desc"
Non-whitelisted fields are silently skipped
If no valid sort provided, defaultSort() is used as fallback

Pagination

{
    "page": 1,
    "per_page": 50
}

When page is present, the response includes pagination metadata:

{
    "data": [...],
    "total": 150,
    "page": 1,
    "per_page": 50,
    "last_page": 3
}

When page is absent, SearchQuery::apply() returns the same paginated format with page: 1. Use SearchQuery::build() + ->get() for unpaginated results.

Count Only

{
    "count_only": true,
    "where": { "eq": { "employee_id": 42 } }
}

Response:

{
    "total": 15
}

No data is returned. Useful for badge counters, tab counts, etc.

Full-Text Search

Search can be a top-level key or inside where:

{
    "where": {
        "search": "John repair",
        "between": { "scheduled_time": ["2026-04-01", "2026-04-30"] }
    }
}

The search term is matched against all fields defined in searchFields(). For related fields (dot notation like employee.first_name), the library uses whereHas automatically. To extend search beyond standard fields, use searchUsing().

OR Conditions

Use the or block alongside where for (where) OR (or) logic:

{
    "where": {
        "eq": { "employee_id": 42 }
    },
    "or": {
        "eq": { "created_by": 42 }
    }
}

Generates:

WHERE (employee_id = 42) OR (created_by = 42)

The or block supports all the same operators as where. Nesting or inside or is not allowed.

AND-OR Groups

For complex conditions like "employee is 42 OR client is in [10, 20]":

{
    "where": {
        "and_or": [
            { "eq": { "employee_id": 42 } },
            { "in": { "client_id": [10, 20] } }
        ]
    }
}

Each array element is an OR group. Groups are combined with AND:

WHERE (employee_id = 42)
  AND (client_id IN (10, 20))

Within a single group with multiple conditions, the first is AND and the rest are OR:

{
    "and_or": [
        { "eq": { "employee_id": 42, "created_by": 42 } }
    ]
}

WHERE (employee_id = 42 OR created_by = 42)

Relation Filtering (has)

Filter records based on related model data using has:

{
    "where": {
        "has": {
            "latestLog": {
                "in": { "status_id": [1, 2, 3] }
            },
            "client": {
                "like": { "name": "Acme" },
                "load": false
            }
        }
    }
}

Generates:

WHERE EXISTS (SELECT * FROM task_logs WHERE task_logs.task_id = tasks.id AND status_id IN (1, 2, 3))
  AND EXISTS (SELECT * FROM clients WHERE clients.id = tasks.client_id AND name LIKE '%Acme%')

Each relation key maps to an object of operators that apply to the related table's fields.

Eager loading: By default, filtered relations are added to with() for eager loading. Set "load": false to filter without loading the relation data.

Whitelisting: Relations must be defined in both:

The model's ->relations([...]) config (with allowed fields)
The controller's $allowedRelations array (if passed to SearchQuery)

API Reference

SearchQuery::apply()

All-in-one: validates, filters, sorts, and paginates.

$result = SearchQuery::apply(
    $query,                // Eloquent Builder
    $payload,              // JSON payload (array)
    $allowedRelations      // optional: ['relation1', 'relation2']
);

// $result = ['data' => [...], 'total' => 42, 'page' => 1, 'per_page' => 25, 'last_page' => 2]

SearchQuery::build()

Step-by-step: validates, filters, and sorts, but does NOT paginate. Returns a SearchBuilder for manual control.

$builder = SearchQuery::build($query, $payload, $allowedRelations);

The optional fourth parameter $config overrides the model's config — useful for adding searchUsing in the controller:

$config = (new Task)->searchableConfig()
    ->searchUsing($this->customFieldSearchCallback($company));

$builder = SearchQuery::build($query, $payload, [], $config);

SearchQuery::aggregate()

Applies the payload's filters and runs the aggregate block (GROUP BY) — returns the aggregate rows. Mirror of apply(), but the terminal is aggregation instead of pagination. See Aggregation.

$rows = SearchQuery::aggregate($query, $payload, $allowedRelations, $config);
// [['group' => 1, 'value' => 142], ...]  (or [['value' => 327]] without a group-by)

SearchBuilder

Returned by SearchQuery::build(). Provides access to the modified query:

// Get the raw Eloquent Builder (for further modifications)
$eloquentQuery = $builder->getQuery();

// Execute and get all results (no pagination)
$collection = $builder->get();

// Get paginated results (same format as apply())
$result = $builder->paginate();

// Get count only
$count = $builder->count();

// Aggregate (GROUP BY) — reads the payload's `aggregate` block; see the Aggregation section
$rows = $builder->aggregate();

Typical pattern for unpaginated results:

$builder = SearchQuery::build($query, $payload);
$tasks = $builder->get();

return response()->json($tasks);

Typical pattern for conditional pagination:

$builder = SearchQuery::build($query, $payload);

if (isset($payload['page'])) {
    return response()->json($builder->paginate());
} else {
    return response()->json($builder->get());
}

Aggregation

Aggregation is a terminal on SearchBuilder — a sibling to paginate() / get() / count(), not a step in the filter pipeline. It reuses the same filtered query (your where, full-text search, has, etc. all apply) and runs a GROUP BY + aggregate SELECT, returning grouped rows instead of records.

What may be aggregated is declared in searchableConfig() as a closed set — metric fields and group-by dimensions are validated against the model, so no client-supplied column or SQL ever reaches the database.

Declaring aggregatable fields

use Illuminate\Support\Facades\DB;

SearchableConfig::make()
    ->fields([/* ... */])
    // Metrics. Two shapes, mixed freely:
    //  - Column metric: numeric field → which functions it allows (`fn(field)`).
    //  - Expression metric: a name → a model-authored aggregate expression, referenced
    //    from the payload by `{name}`. The SQL lives in YOUR model (trusted), never in
    //    the request, so the closed-set guarantee holds.
    // `count` (of records) is always available and needs no field.
    ->metrics([
        'duration'        => ['sum', 'avg', 'min', 'max'],
        'amount'          => ['sum', 'avg'],
        'shift_count'     => ['expr' => DB::raw('COUNT(*)')],
        'confirmed_count' => ['expr' => DB::raw('SUM(confirmed_by_employee)')],
    ])
    // Dimensions. Column dimensions are string list entries; an expression dimension is a
    // `name => DB::raw(...)` pair — the portable escape hatch for period/weekday/etc.
    // (the driver-specific SQL is your model's code, not the request).
    ->dimensions([
        'project_id', 'status', 'employee_id',
        'day' => DB::raw("DATE_FORMAT(scheduled_time, '%Y-%m-%d')"), // MySQL example
    ])
    // Derived metrics: computed in PHP after aggregation from the values row (keyed by
    // metric name). Portable — it's plain PHP, no SQL.
    ->derived([
        'confirmation_rate' => fn ($v) => ($v['shift_count'] ?? 0) > 0
            ? round(100 * ($v['confirmed_count'] ?? 0) / $v['shift_count'], 2) : 0,
    ]);

The library itself stays database-agnostic — it only emits standard aggregate functions and GROUP BY. Anything driver-specific (date bucketing, JSON_TABLE, CONVERT_TZ, …) belongs in an expression metric/dimension that your model declares: the library treats it as opaque SQL it was handed, so portability is the model author's call, not the request's. When a derived metric is requested, every declared expression metric is computed so the closure can read its inputs by name. A derived metric should therefore depend on expression metrics (or count declared as one) — a plain column metric (fn+field) is only present in the closure's row when it is itself explicitly requested.

Running an aggregation

The aggregate spec lives inside the payload under the aggregate key — the whole JSON goes into the builder, exactly like the rest of the DSL. where/or/has filter the data, then the aggregate block runs the GROUP BY:

$payload = [
    'where'     => ['eq' => ['status' => 'done']],
    'aggregate' => [
        'metric'  => ['fn' => 'sum', 'field' => 'duration'],
        'groupBy' => ['field' => 'project_id'],
    ],
];

$rows = SearchQuery::aggregate(Shift::query()->where('company_id', $companyId), $payload);

// [
//   ['group' => 1, 'value' => 142],
//   ['group' => 2, 'value' => 96],
//   ['group' => 3, 'value' => 58],
// ]

Equivalent via the builder (e.g. to add constraints before running):

$rows = SearchQuery::build(Shift::query(), $payload)->aggregate();

Example JSON request

The request is a single, well-formed payload — filters and the aggregate block together — sent as-is to the builder:

// POST /shifts/aggregate
{
  "where": {
    "eq": { "status": "done" },
    "between": { "scheduled_time": ["2026-05-01 00:00:00", "2026-05-31 23:59:59"] }
  },
  "aggregate": {
    "metric":    { "fn": "sum", "field": "duration" },
    "groupBy":   { "field": "project_id" },
    "orderBy":   "value",
    "direction": "desc",
    "limit":     5
  }
}

public function aggregate(Request $request)
{
    $rows = SearchQuery::aggregate(
        Shift::query()->where('company_id', $request->user()->company_id),
        $request->all()
    );

    return response()->json($rows);
}

[
  { "group": 1, "value": 142 },
  { "group": 2, "value": 96 },
  { "group": 3, "value": 58 }
]

Rich request (JSON) — multiple metrics (column + expression + derived), multi-level grouping, having (incl. between), top-N ordering and a grand total, all in one payload:

// POST /shifts/aggregate
{
  "where": {
    "between": { "scheduled_time": ["2026-05-01 00:00:00", "2026-05-31 23:59:59"] }
  },
  "aggregate": {
    "metrics": [
      { "fn": "count", "as": "shift_count" },
      { "name": "confirmed_count", "as": "confirmed_count" },
      { "name": "confirmation_rate", "as": "rate" }
    ],
    "groupBy": [
      { "field": "project_id" },
      { "field": "status" }
    ],
    "having": [
      { "metric": "shift_count", "op": "between", "value": [10, 500] }
    ],
    "orderBy": "rate",
    "direction": "desc",
    "limit": 20,
    "total": true
  }
}

Response — values keyed by as, group is an object (multi-level), and a trailing grand-total row with group: null:

[
  { "group": { "project_id": 1, "status": "done" }, "values": { "shift_count": 42, "confirmed_count": 40, "rate": 95.24 } },
  { "group": { "project_id": 2, "status": "done" }, "values": { "shift_count": 31, "confirmed_count": 25, "rate": 80.65 } },
  { "group": null, "values": { "shift_count": 327, "confirmed_count": 300, "rate": 91.74 } }
]

Period trend (JSON) — group by the day expression dimension declared on the model:

// POST /shifts/aggregate
{
  "where": {
    "between": { "scheduled_time": ["2026-05-01 00:00:00", "2026-05-31 23:59:59"] }
  },
  "aggregate": {
    "metric":    { "fn": "count" },
    "groupBy":   { "field": "day" },
    "orderBy":   "group",
    "direction": "asc"
  }
}

[
  { "group": "2026-05-01", "value": 12 },
  { "group": "2026-05-02", "value": 9 },
  { "group": "2026-05-03", "value": 15 }
]

Spec & result shape

Key	Type	Description
`metric`	object	one metric (BC). `{fn:'count'}`, `{fn:'sum'\|'avg'\|'min'\|'max', field}`, or `{name}` (declared expression metric)
`metrics`	array	many metrics: a list of metric objects, each with an optional `as` (output key). Use instead of `metric`
`groupBy`	object \| array \| null	omit → scalar aggregate; `{field}` → one group-by; `[{field}, …]` → multi-level group-by (≤ `max_group_by_depth`)
`having`	array	post-aggregation conditions on metric values: `[{metric:'<as>', op, value}]`. `op ∈ eq/not_eq/gt/gte/lt/lte/between` (derived-aware)
`orderBy`	string	`'group'` or a metric `as`. (`'value'` is accepted as a legacy alias when using singular `metric`)
`direction`	string	`'asc'` or `'desc'` (default: `desc` for a metric, `asc` for group)
`limit`	int	top-N rows
`total`	bool	with `groupBy`, append a grand-total row (`group: null`) re-aggregated over the full set

Result — the shape mirrors the request:

Request	Each row
`metric` (singular)	`{group?, value}`
`metrics` (plural)	`{group?, values: {as: number}}`
no `groupBy`	no `group` key (single scalar / KPI row)
one `groupBy`	`group` = scalar
many `groupBy`	`group` = `{dimension: value, …}`

Values come back as numbers — int for whole results, float for fractional ones (e.g. avg).

// Single value (KPI) — payload: ['aggregate' => ['metric' => ['fn' => 'count']]]
SearchQuery::aggregate(Shift::query(), $payload);
// [['value' => 327]]

Top-N

Order by a metric and limit the rows (in the payload's aggregate block):

$payload = [
    'aggregate' => [
        'metric'    => ['fn' => 'sum', 'field' => 'duration'],
        'groupBy'   => ['field' => 'project_id'],
        'orderBy'   => 'value',
        'direction' => 'desc',
        'limit'     => 5,
    ],
];

$rows = SearchQuery::aggregate(Shift::query(), $payload);

Multiple metrics, derived metrics, HAVING & totals

Request several metrics at once (output keyed by as), filter grouped rows with having, and append a grand total. Derived metrics and totals are computed correctly for non-additive metrics (avg/min/max/distinct) because the total is re-aggregated over the whole set, not summed from the group rows.

$payload = [
    'aggregate' => [
        'metrics' => [
            ['name' => 'shift_count',       'as' => 'shift_count'],     // expression metric
            ['name' => 'confirmed_count',   'as' => 'confirmed_count'], // expression metric
            ['name' => 'confirmation_rate', 'as' => 'rate'],            // derived (PHP)
        ],
        'groupBy' => [['field' => 'project_id'], ['field' => 'status']],         // multi-level
        'having'  => [['metric' => 'shift_count', 'op' => 'gte', 'value' => 10]],
        'orderBy' => 'rate',
        'direction' => 'desc',
        'total'   => true,
    ],
];

$rows = SearchQuery::aggregate(Shift::query()->where('company_id', $id), $payload);

// [
//   ['group' => ['project_id' => 1, 'status' => 'done'], 'values' => ['shift_count' => 42, 'confirmed_count' => 40, 'rate' => 95.24]],
//   ...
//   ['group' => null, 'values' => ['shift_count' => 327, 'confirmed_count' => 300, 'rate' => 91.74]], // grand total
// ]

having is applied in PHP after aggregation, so it works on derived metrics too. The grand-total row ignores having/limit (it always covers the full filtered set).

having also accepts between:

// projects whose shift_count is in [10, 100]
'having' => [['metric' => 'shift_count', 'op' => 'between', 'value' => [10, 100]]],

Expression dimensions (period buckets, weekday, …)

Date bucketing has no portable SQL form, so the library doesn't ship it — but a model can declare an expression dimension (the day => DB::raw(...) entry shown in Declaring aggregatable fields) and group by it like any other dimension. The driver-specific SQL stays in your model; the request just names it.

// Trend: shifts per day in May. `day` is the expression dimension declared on the model.
$payload = [
    'where'     => ['between' => ['scheduled_time' => ['2026-05-01 00:00:00', '2026-05-31 23:59:59']]],
    'aggregate' => [
        'metric'    => ['fn' => 'count'],
        'groupBy'   => ['field' => 'day'],
        'orderBy'   => 'group',
        'direction' => 'asc',
    ],
];

$rows = SearchQuery::aggregate(Shift::query()->where('company_id', $id), $payload);

// [
//   ['group' => '2026-05-01', 'value' => 12],
//   ['group' => '2026-05-02', 'value' => 9],
//   ['group' => '2026-05-03', 'value' => 15],
//   ...
// ]

The same mechanism covers weekday, start_hour, or any other computed grouping — declare the expression once, then it's a first-class dimension (usable in groupBy, single or multi-level).

Validation

The spec is validated against the config; an invalid request throws InvalidPayloadException (HTTP 422):

an unknown function (not one of count / sum / avg / min / max);
a metric field not declared in metrics(), or not allowing that function;
a metric {name} not declared as an expression metric or a derived metric;
a groupBy field not declared in dimensions() (column or expression);
more groupBy levels than limits.max_group_by_depth (default 3);
a having condition referencing an unselected metric, or using an operator outside eq/not_eq/gt/gte/lt/lte/between;
an orderBy that is neither 'group' nor a selected metric.

Since every metric, dimension and HAVING target is checked against the closed config set, no client-supplied identifier is interpolated into raw SQL — only the SQL you declared in expression metrics/dimensions runs.

Operator Auto-Resolution

The library automatically determines which operators are valid for each field based on the model's $casts and database column types:

Type	Auto-resolved operators
`integer`, `bigint`, `smallint`, etc.	`eq`, `not_eq`, `in`, `not_in`, `gt`, `lt`, `gte`, `lte`, `between`
`float`, `double`, `decimal`	`eq`, `not_eq`, `in`, `not_in`, `gt`, `lt`, `gte`, `lte`, `between`
`string`	`eq`, `not_eq`, `like`, `in`, `not_in`
`boolean`	`eq`, `not_eq`
`datetime`, `timestamp`	`eq`, `between`, `gt`, `lt`, `gte`, `lte`
`date`	`eq`, `between`, `gt`, `lt`, `gte`, `lte`
`array`, `collection`, `json`	`json_contains`, `json_contains_all`

Additionally:

Nullable columns detected from the database schema get is_null added automatically
nullable() explicitly adds is_null for listed fields, regardless of schema
jsonFields() forces json_contains + json_contains_all regardless of cast type
Explicit overrides ('status' => ['eq', 'in']) bypass auto-resolution entirely

Custom Filters

For queries that cannot be expressed through the standard operators (subqueries, computed fields, cross-table aggregation), implement the CustomFilter interface:

use Illuminate\Database\Eloquent\Builder;
use DartVadius\EloquentSearch\Contracts\CustomFilter;

class TaskStatusFilter implements CustomFilter
{
    public function apply(Builder $query, string $operator, mixed $value): void
    {
        // Example: filter by latest log's status via subquery
        $subquery = '(SELECT status_id FROM task_logs
                      WHERE task_id = tasks.id
                      ORDER BY id DESC LIMIT 1)';

        match ($operator) {
            'eq'     => $query->where(\DB::raw($subquery), $value),
            'in'     => $query->whereIn(\DB::raw($subquery), (array) $value),
            'not_in' => $query->whereNotIn(\DB::raw($subquery), (array) $value),
        };
    }

    public function allowedOperators(): array
    {
        return ['eq', 'in', 'not_in'];
    }
}

public function searchableConfig(): SearchableConfig
{
    return SearchableConfig::make()
        ->fields(['id', 'title', 'scheduled_time'])
        ->filter('task_status', new TaskStatusFilter());
}

Use in JSON payload:

{
    "where": {
        "in": { "task_status": [1, 2, 3] }
    }
}

Important: Custom filters are registered by field name (not operator). When a field matches a custom filter, all operator handling is delegated to that filter. The allowedOperators() method controls which operators the filter accepts.

Configuration

After publishing (php artisan vendor:publish --tag=eloquent-search-config), edit config/eloquent-search.php:

return [
    // Pagination defaults
    'pagination' => [
        'default_per_page' => 25,    // Default page size
        'max_per_page' => 1000,      // Maximum allowed page size (silently capped)
    ],

    // Safety limits to prevent abuse
    'limits' => [
        'max_conditions' => 50,        // Max total conditions across where + or + and_or + has
        'max_or_conditions' => 10,     // Max groups in and_or
        'max_in_values' => 500,        // Max array items in in/not_in/json_contains
        'max_group_by_depth' => 3,     // Max GROUP BY levels in an aggregation
        'max_groups' => 5000,          // Hard ceiling on grouped rows fetched before PHP post-processing
    ],

    // What to do with fields not in the whitelist
    'on_unknown_field' => 'skip',    // 'skip' = silently ignore, 'throw' = throw InvalidPayloadException
];

Validation & Error Handling

The library validates the payload structure before executing any queries. Invalid payloads throw DartVadius\EloquentSearch\Exceptions\InvalidPayloadException (extends Symfony\Component\HttpKernel\Exception\UnprocessableEntityHttpException).

In a Laravel application, this means invalid payloads automatically return a 422 Unprocessable Entity HTTP response — no manual try/catch required.

What is validated:

Check	Error
`where` / `or` is not an object	`"where" must be an object.`
Nested `or` inside `or`	`Nested "or" inside "or" is not supported.`
Nested `has` inside `has`	`Nested "has" is not supported.`
Nested `and_or` inside `and_or`	`Nested "and_or" inside and_or is not supported.`
`eq` value is array	`eq.field in where: expected scalar, got array.`
`in` value is empty	`in.field in where: expected non-empty array.`
`between` value has != 2 elements	`between.field in where: expected array with exactly 2 elements.`
`like` value is not string	`like.field in where: expected string, got integer.`
`is_null` value is not boolean/array	`is_null.field in where: expected boolean, got string.`
`is_null` array shorthand has non-string	`is_null[1] in where: expected string field name, got integer.`
Total conditions > max	`Too many conditions: 55 (max: 50).`
`in` values > max	`in.field in where: too many values 600 (max: 500).`
`and_or` groups > max	`Too many "and_or" groups in where: 15 (max: 10).`
Invalid `page`	`"page" must be a positive integer.`
Invalid `sort[].dir`	`"sort[0].dir" must be "asc" or "desc".`

What is NOT validated (silently ignored):

Unknown field names (when on_unknown_field = skip)
Operators not allowed for a field type (e.g., like on an integer)
Unknown relation names in has
Non-whitelisted sort fields
search when no searchFields configured
search with empty string or null value (treated as no search)

Since InvalidPayloadException extends UnprocessableEntityHttpException, Laravel handles it automatically — returning 422 with the error message. No try/catch needed in controllers.

If you need custom error formatting, you can still catch it explicitly:

use DartVadius\EloquentSearch\Exceptions\InvalidPayloadException;

try {
    $result = SearchQuery::apply($query, $payload);
} catch (InvalidPayloadException $e) {
    return response()->json(['error' => $e->getMessage()], 422);
}

Security Considerations

Field whitelisting is mandatory. Only fields declared in fields() can be queried. There is no "allow all" mode.
Relation whitelisting is double-gated. Both the model config and the controller's $allowedRelations must allow a relation.
Input limits prevent abuse. max_conditions, max_in_values, and max_or_conditions protect against denial-of-service via complex queries.
Always apply authorization scopes before passing the query. The library does not handle permissions. Set up your WHERE company_id = ? or role-based scopes on the Builder before calling SearchQuery.

// Good: authorization first, then DSL
$query = Task::where('company_id', $user->company_id);
$result = SearchQuery::apply($query, $payload);

// Bad: no authorization scope
$result = SearchQuery::apply(Task::query(), $payload);

dartvadius / eloquent-search

Maintainers

Package info

Statistics

Security

README

Table of Contents

Requirements

Installation

Quick Start

1. Prepare your model

2. Use in a controller

3. Send a JSON request

How It Works

Model Configuration

Fields

Nullable Fields

JSON Fields

Sorting

Search Fields

Extending Search (searchUsing)

Relations

Custom Filters

JSON Payload Format

Operators

Comparison operators

Set operators

Range operator

String operator

Null operators

JSON operators

Combining operators

Sorting

Pagination

Count Only

Full-Text Search

OR Conditions

AND-OR Groups

Relation Filtering (has)

API Reference

SearchQuery::apply()

SearchQuery::build()

SearchQuery::aggregate()

SearchBuilder

Aggregation

Declaring aggregatable fields

Running an aggregation

Example JSON request

Spec & result shape

Top-N

Multiple metrics, derived metrics, HAVING & totals

Expression dimensions (period buckets, weekday, …)

Validation

Operator Auto-Resolution

Custom Filters

Configuration

Validation & Error Handling

Security Considerations