Download the PHP package yassinedabbous/laravel-dynamic-query without Composer

Laravel Dynamic Query

A rich set of features designed to make querying a database more dynamic, flexible, and secure.

Table of Content

• Features
• Installation
• Usage
  • Add trait to model, use controller (index, show) and test with url
  • Dynamic Select
    • Dynamic Columns
    • Dynamic Relations
    • Dynamic Appends
    • Dynamic Aggregates
    • use dynamicSelect + dynamicAppends
    • deep relations
  • Dynamic Filter
    • DynamicFilters
      • not
      • like Left & Right
    • operators list
    • clauses
    • logic
    • Scopes
    • computes
  • Groups
  • Sorts
• :gear: Configuration

:sparkles: Features

• Flexibility & Customization: Dynamically customize queries and select only necessary fields.
• Modular Use: Use specific traits to reduce redundant code.
• Easy Integration: Integrates smoothly with Laravel’s core features.
• Data Efficiency: Fetch only needed data with automatic eager loading.
• Secure Queries: Control allowed fields to prevent query manipulation.
• Advanced Queries: Simplify complex queries with deep relations and aggregates.
• Clean Results: Return optimized and clean API responses.
• Ease of Use: Extend functionality easily with query parameters.

:small_red_triangle_down: Installation:

composer require yassinedabbous/laravel-dynamic-query

:technologist: Quick Setup

This packages is a set of 4 traits, you can add one of them or just use the general HasDynamicQuery trait:

In your controller, call the needed scopes or just use the dynamicQuery() method:

For more details on usage, see the sections below.

:technologist: Usage:

Select:

Since the API client (FrontEnd developer) doesn't need to know which fields are columns, appends, or model relationships, DynamicFields simplifies the process by automatically handling:

• selecting columns
• appending attributes
• eager loading relationships (with deep fields)
• applying aggregates All through a single URL parameter: _fields.

Note: to support deep fields, all requested relations should use DynamicFields.

• API call:

GET /users?_fields=id,name,avatar,followers_count,posts:id|title|time_ago

• Resulting Database Queries:

SELECT `id`, `name`, `avatar`, (SELECT COUNT("id") FROM "followers" where ...) AS `followers_count`  FROM `users`;

SELECT `id`, `title`, `created_at` FROM `posts` WHERE `users_id` = ?;

• Response: ("time_ago", "followers_count" .. are automatically appended)

[
    {
        "id": 1,
        "name": "Someone",
        "avatar": "...",
        "followers_count": 1048,
        "posts": [
            {"id":1, "title":"post 1", "time_ago": "2 weeks ago"},
            ...
        ]
    },
    {
        "id": 1,
        ...
    },
    ...
]

To utilize the dynamic selection feature, you need to define a list of selectable columns, relations, aggregates, and appends within your model.

Dynamic Columns:

Dynamic Relations:

Model relations can be inferred automatically from methods if they are defined with a typed return:

Alternatively, you can manually define the list of allowed relations using the dynamicRelations() method:

Dynamic Appends:

To function properly, appends must be defined along with their dependent columns or relations:

To work correctly, appends must be defined with their dependent columns or relations:

Dynamic Aggregates:

Aggregates can be defined as named scopes or closures:

Filter:

DynamicFilters Allows API consumers to filter queries based on URL parameters, it support multiple clause types and logics:

API Example:

api/endpoint?status_id=20&members_count=3
            &_operators[members_count]=>
            &_clauses[members_count]=having

Dynamic Filters:

To implement dynamic filters, you need to define a list of acceptable filters, optionally with their allowed operators:

Operators:

DynamicFilters uses the equality = operator by default, but this can be changed for each filter using the _operators parameter.

• Example:

api/products?price=1000&_operators[price]=>

Results in the following SQL query:

SELECT * FROM products WHERE price > 1000

• Example

api/products?name=Iphone&_operators[name]=LIKE%

Resulted DB query: SELECT * FROM products WHERE name LIKE "Iphone%"

• Using NOT Logic

To apply the NOT logic, use the exclamation mark (!) before operators:

• Left and Right LIKE

Use % to specify left or right LIKE conditions:

Supported operators:

    [
        '=', '<', '>', '<=', '>=', '<>', '!=', '<=>',
        '&', '|', '^', '<<', '>>', '&~', 'is', 'is not',
        'like', '!like', '%like', 'like%',
        'like binary', 'not like', 'ilike',
        'rlike', 'not rlike', 'regexp', 'not regexp',
    ];

Clauses:

DynamicFilters uses WHERE as the default clause, but you can change it with the _clause parameter:

By default, DynamicFilters uses the WHERE clause, but this can be changed using the _clause parameter:

use WHERE clause for all filters:

api/endpoint?_clause=where

use HAVING clause for all filters:

api/endpoint?_clause=having

To apply a clause to a specific filter, use _clauses.

Example:

api/endpoint?price=1000&reviews_count=3&_clauses[reviews_count]=having

Resulting SQL query:

SELECT * FROM products WHERE price = 1000 HAVING reviews_count = 3

Logic operator:

DynamicFilters uses AND by default, but this can be changed using the _logic parameter:

api/endpoint?_logic=and      # use AND clause for all filters
api/endpoint?_logic=or       # use OR clause for all filters

Supported logic clauses are AND and OR.

Scopes:

DynamicFilters support named scopes and closures as filter:

Sorting:

The _sort query parameter specifies the property by which the results will be ordered. By default, sorting is in ascending order, but it can be reversed by prefixing the property name with a hyphen (-).

You can also sort by multiple properties:

/endpoint?_sort=id,-price

or

/endpoint?_sort[]=id&_sort[]=-price

To enable sorting, define a list of allowed sortable fields in your model:

Groups:

The _group query parameter specifies the properties by which the results will be grouped. Grouping is often used with computed values or subqueries (see DynamicFields section).

You can group by one or more properties:

/endpoint?_group=month

/endpoint?_group=month,status

/endpoint?_group[]=month&_group[]=status

To enable grouping, define a list of allowed grouping fields in your model:

Paginate:

Dynamic pagination works similarly to Laravel's paginator but offers more flexibility.

API call example:

/endpoint?page=3&per_page=40

To fetch all records at once, use the _get_all query parameter, optionally limiting the number of results with _limit.

/endpoint?_get_all=true&_limit=50

:gear: Configuration:

You can optionally publish the config file with:

php artisan vendor:publish --tag=dynamic-query-config

These are the contents of the default config file that will be published:

Final result