Download the PHP package ayup-creative/laravel-dsql without Composer

Laravel Advanced Search DSL

A production-ready domain-specific language (DSL) for Laravel applications. This package allows you to expose complex, user-defined search expressions while abstracting and protecting your underlying database schema.

Designed for reporting tools, analytics dashboards, and advanced filtering APIs, it converts a safe string-based syntax into composable Eloquent queries.

✨ Features

• Standardised DSL Syntax: Clear and unambiguous syntax for filtering and reporting.
• Column Selection: Specify which fields to return with SELECT [col1], [col2] AS "alias".
• Alias Reuse: Reuse calculated aliases within the same SELECT and WHERE clauses.
• Boolean Logic: Full support for AND, OR, and grouped expressions with parentheses.
• Virtual Columns: Map safe, user-facing field names to internal database logic using attributes (#[VirtualColumn]).
• Custom Operator Registry: Easily extend the language with new operators (e.g., equals, in, gt, between, contains).
• Column-to-Column Comparisons: Compare fields safely (e.g., [processed_at]:gt[created_at]).
• Arithmetic Expressions: Perform calculations directly in the search string (e.g., [price] / 1.2 :gt 100).
• Calculated Virtual Columns: Define aliases for complex expressions using attributes.
• Dynamic Values: Express real-time dates and values like now(), today(), or now()->subDays(7).
• Custom Selection Casting: Apply PHP-side formatting to selected values using CAST(expr, 'type').
• Default Selections: Define fallback fields to return when no SELECT clause is specified.
• Column Metadata: Attach custom metadata (e.g., for UI formatting/casts) to search columns.
• Searchable Trait: Simplified model integration with convenient methods for report generation.
• Relationship Traversal: Query across Eloquent relationships using dot notation (e.g., [customer.ref]:equals"ABC123").
• Built-in Sorting & Limits: Include reporting instructions directly in the query string.
• Relationship Aggregates: Perform existence and count checks on relationships (e.g., EXISTS([orders]), COUNT([items]):gt 5).
• Security-First: Uses a lexer and Pratt parser to build a safe abstract syntax tree (AST) before compilation.
• Autocomplete Ready: Built-in methods to retrieve searchable columns and blank syntax for UI integration.

🚀 Quick Start

Installation

Basic Usage

🔍 Autocomplete Support

The DSL provides built-in support for generating metadata for your UI via the AdvancedSearch facade.

📖 Syntax Guide

Column Selection

Specify which fields to return with SELECT:

• SELECT [name], [price] WHERE [status]:equals"active"
• SELECT [name] AS "product_name", [price] * 1.2 AS "vat_price"
• SELECT CAST([price], "money") AS "formatted_price" WHERE [status]:equals"active"
• SELECT CAST([price], "money") WHERE [status]:equals"active" (Alias defaults to price)

Alias Reuse

Aliases defined in the SELECT clause can be reused in subsequent SELECT expressions and in the WHERE clause:

• SELECT [price] * 0.3 AS "a", [a] * 2 AS "b" WHERE [a]:gt 10
• SELECT CAST([price] * 0.3, "money") AS "a", [a] AS "a_copy"

If no SELECT is specified, the system defaults to selecting all columns (*) in the database query.

Default Selections for UI/Reports

You can define a default set of fields to be returned by getSelections() when no SELECT clause is provided. This is useful for building dynamic report tables.

Or by defining a static method:

If neither is defined, it fallbacks to the model's getFillable() list.

Virtual Columns

Virtual columns allow you to expose internal database logic or complex calculations as safe, user-friendly field names. You can define them using the #[VirtualColumn] attribute on your model classes or static methods.

A virtual column can specify:

• How it's handled for filtering (WHERE clause) via a custom resolver method.
• How it's handled for selection (SELECT clause) via a SQL expression.

If only an expression is provided (on the class or a method), it will be used for both selection and filtering. If a method is decorated, it acts as the primary resolver for filtering.

Fields (Columns)

Fields are always wrapped in square brackets: [status], [customer.name].

Literals (Values)

• Strings: Quoted or unquoted if they don't contain special characters: "active", 'pending', ABC123.
• Numbers: Integers or decimals: 100, 99.99.
• Lists: Used for in or between operators: (active, pending), (10, 20).
• Dynamic Functions: Express real-time values: now(), today(), yesterday(), tomorrow().
• Method Chaining: Call methods on dynamic values: now()->subDays(7), now()->startOfMonth().

Arithmetic

Full support for +, -, *, / on the left-hand side of conditions:

• [price] / 1.2 :gt 100
• ([amount] + [shipping]) * 1.1 :lt 500

Relationship Aggregates

• Existence: EXISTS([relationship]) (returns boolean)
• Count: COUNT([relationship]) (returns integer)
• Example: SELECT [name], COUNT([orders]) AS "order_count" WHERE EXISTS([orders])

Operators

• equals: [status]:equals"active"
• in: [status]:in(processed, pending)
• gt / lt: [price]:gt 100, [price]:lt[discount_price]
• between: [created_at]:between("2024-01-01", "2024-12-31")
• contains: [name]:contains"John"

Relationship Traversal

The DSL supports querying across Eloquent relationships using dot notation:

• [customer.name]:equals"John" (Automatically uses whereHas('customer', ...))
• [registration.model.standard_warranty_years]:lt 10

For selection, dotted paths are supported directly in the SELECT clause:

• SELECT [customer.name], [customer.address.city]

The system automatically detects relationships in the SELECT clause, eager-loads them using $query->with(), and ensures that necessary foreign keys (for BelongsTo) or primary keys (for HasOne/HasMany) are included in the SQL selection. Plural relationships (e.g., HasMany) are automatically resolved into arrays when accessed via getSelectionValue().

Boolean Logic

([status]:equals"active" OR [priority]:gt 5) AND [category]:in(books, electronics)

Sorting & Limits

• sort(column, direction): sort(created_at, desc)
• limit(number): limit(10)

Developer Guide

For detailed information on how to extend the system with custom operators or advanced resolvers, please see the DEVELOPER_GUIDE.md.

🛠️ Development

This project uses pre-commit to maintain code quality and ensure consistent commit messages.

Pre-commit Hooks

We use pre-commit to automatically run:

• Pint: Ensures PHP code style follows Laravel standards.
• PHPUnit: Runs the test suite to prevent regressions.
• Conventional Commits: Validates commit messages.

To set up pre-commit locally:

1. Install pre-commit on your machine.
2. Run pre-commit install in the project root to set up the git hook scripts.
3. Run pre-commit install --hook-type commit-msg to enable commit message linting.

Conventional Commits

We follow the Conventional Commits specification for all commit messages. This allows us to automate our release process and generate consistent changelogs.

Common types:

• feat: A new feature
• fix: A bug fix
• docs: Documentation only changes
• style: Changes that do not affect the meaning of the code (white-space, formatting, etc)
• refactor: A code change that neither fixes a bug nor adds a feature
• perf: A code change that improves performance
• test: Adding missing tests or correcting existing tests
• chore: Changes to the build process or auxiliary tools and libraries

CI/CD & Automated Releases

Our GitHub Actions workflow handles the following sequentially:

1. PHPUnit tests: Runs tests across multiple PHP versions (8.2, 8.3, 8.4, 8.5).
2. Commit/PR lint: Validates that pull request titles follow conventional commit standards.
3. Release Please: Automatically creates GitHub releases, tags, and updates the changelog when a PR is merged into the main branch.

Testing

Run the test suite with PHPUnit:

You can also run tests using Composer:

For coverage reports (requires Xdebug):

Pint is also available for code formatting. Run pint to fix any issues.

License

MIT License.