Free version laravel-lumen-crud-wizard - Url query language lib for RESTful cruFd (micro) services using lumen/laravel 8-9-10-11

[macropay-solutions]

Folowing #49228, we gladly present
https://github.com/macropay-solutions/laravel-lumen-crud-wizard

This is not just another CRUD lib!
It has built in filtering capabilities that can be used for listing but also for mass deleting, so it could be called a CRUFD (create, read, update, filter and delete) lib instead.

If you have a sql DB that needs API exposure ASAP then this is a fast or maybe the fastest solution.

On request we can issue a test token to be used on a laravel 9 DEMO project that has a dummy db.

You get:

• crud-wizard-builder: request builder that is used to encode the request in the request initiator project + documentation (min PHP 7.4 or 8.0)
• laravel-crud-wizard: request parser or translator into sql for the request target project + documentation (min PHP 8.0)
• laravel 9 demo project that uses laravel-crud-wizard

Originally posted by @ghost in #49228

Changes required for the first resource:

With green are the changes needed for a new resource (except ResourceController).

Long story short: so you don't have to code the filters by hand.

Consider this solution only if you have or plan to have over 10 tables exposed via api.

[macropay-solutions]

So if someone is interested in testing it here is the bearer token: 6TGvWZzsQMop4kiATYfgEjA3oQBmBkjkpE2H9R2q
Here is the endpoint: http://89.40.19.34/laravel-9/api/{resource} or /laravel-10/
ex http://89.40.19.34/laravel-9/api/operations?mins[]=created_at
Here are exposed the resources with their relations: http://89.40.19.34/laravel-9/laravel-lumen-crud-wizard

PS. HEADER: Accept application/xls generates a binary xls file for download (with relations in different sheets).
PS. updated for mariadb driver

[dmitrychurkin]

Amazing job! This will really facilitate of reducing a boilerplate while building APIs

[macropay-solutions]

Added also min and max aggregations today.
ex http://89.40.19.34/laravel-9/api/operations?mins[]=value&limit=1&relationsFilters[products][value][from]=21&doesntHaveRelations[]=products

{
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": "10.00"
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "DEMO_ONLY_sql_debugger": [
        "select count(*) as aggregate from `operations` where `operations`.`id` not in (select `x`.`operation_id` from `operations_products_pivot` as `x` inner join `products` on `x`.`product_id` = `products`.`id` where `products`.`value` >= '21')",
        "select * from `operations` where `operations`.`id` not in (select `x`.`operation_id` from `operations_products_pivot` as `x` inner join `products` on `x`.`product_id` = `products`.`id` where `products`.`value` >= '21') order by `created_at` desc limit 1 offset 0",
        "select distinct `products`.*, `operations_products_pivot`.`operation_id` as `laravel_through_key` from `products` inner join `operations_products_pivot` on `operations_products_pivot`.`product_id` = `products`.`id` where `operations_products_pivot`.`operation_id` in (3748918) and `products`.`value` >= '21'",
        "select MIN(`operations`.`value`) as value_min from `operations` where `operations`.`id` not in (select `x`.`operation_id` from `operations_products_pivot` as `x` inner join `products` on `x`.`product_id` = `products`.`id` where `products`.`value` >= '21')"
    ],
    "current_page": 1,
    "data": [
        {
            "id": 3748918,
            "parent_id": null,
            "client_id": 68378,
            "currency": "EUR",
            "value": "27.00",
            "created_at": "2024-01-17 11:04:57",
            "updated_at": null,
            "primary_key_identifier": "3748918",
            "products": []
        }
    ],
    "from": 1,
    "last_page": 3286782,
    "per_page": 1,
    "to": 1,
    "total": 3286782
}

another example:
http://89.40.19.34/laravel-9/api/operations/1/products?aggregates[groupBys][]=id&mins[]=value

[macropay-solutions]

Good news. New feature implemented to allow filter on nullable columns with in (null,1,2,3,4,5) that will generate nested where with or:
(column in (...) OR column IS NULL)

http://89.40.19.34/laravel-9/api/operations?limit=3&parent_id[o]=isNull&parent_id[v][]=1&parent_id[v][]=2&sort[0][by]=parent_id

{
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null,
        "created_at_min": null
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "DEMO_ONLY_sql_debugger": [
        "select count(*) as aggregate from `operations` where (`operations`.`parent_id` in ('1', '2') or `operations`.`parent_id` is null)",
        "select * from `operations` where (`operations`.`parent_id` in ('1', '2') or `operations`.`parent_id` is null) order by `parent_id` desc limit 3 offset 0"
    ],
    "current_page": 1,
    "data": [
        {
            "id": 3,
            "parent_id": 2,
            "client_id": 2,
            "currency": "EUR",
            "value": "10.05",
            "created_at": "2024-01-12 19:31:53",
            "updated_at": null,
            "primary_key_identifier": "3"
        },
        {
            "id": 2,
            "parent_id": 1,
            "client_id": 2,
            "currency": "EUR",
            "value": "10.01",
            "created_at": "2024-01-12 19:25:51",
            "updated_at": null,
            "primary_key_identifier": "2"
        },
        {
            "id": 1,
            "parent_id": null,
            "client_id": 1,
            "currency": "EUR",
            "value": "10.00",
            "created_at": "2024-01-03 19:39:02",
            "updated_at": null,
            "primary_key_identifier": "1"
        }
    ],
    "from": 1,
    "last_page": 1249640,
    "per_page": 3,
    "to": 3,
    "total": 3748918
}

http://89.40.19.34/laravel-9/api/operations?limit=3&parent_id[o]=isNotNull

{
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null,
        "created_at_min": null
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "DEMO_ONLY_sql_debugger": [
        "select count(*) as aggregate from `operations` where `operations`.`parent_id` is not null",
        "select * from `operations` where `operations`.`parent_id` is not null order by `created_at` desc limit 3 offset 0"
    ],
    "current_page": 1,
    "data": [
        {
            "id": 3,
            "parent_id": 2,
            "client_id": 2,
            "currency": "EUR",
            "value": "10.05",
            "created_at": "2024-01-12 19:31:53",
            "updated_at": null,
            "primary_key_identifier": "3"
        },
        {
            "id": 2,
            "parent_id": 1,
            "client_id": 2,
            "currency": "EUR",
            "value": "10.01",
            "created_at": "2024-01-12 19:25:51",
            "updated_at": null,
            "primary_key_identifier": "2"
        }
    ],
    "from": 1,
    "last_page": 1,
    "per_page": 3,
    "to": 2,
    "total": 2
}

[macropay-solutions]

Free version https://github.com/macropay-solutions/laravel-crud-wizard-free

[macropay-solutions]

Now also on packagist https://packagist.org/packages/macropay-solutions/laravel-crud-wizard-free

[macropay-solutions]

Test laravel 9:
http://89.40.19.34/laravel-9/api/{resource}
ex http://89.40.19.34/laravel-9/api/operations?mins[]=created_at
Here are exposed the resources with their relations: http://89.40.19.34/laravel-9/laravel-lumen-crud-wizard

Test laravel 10:
http://89.40.19.34/laravel-10/api/{resource}
ex http://89.40.19.34/laravel-10/api/operations?mins[]=created_at
Here are exposed the resources with their relations: http://89.40.19.34/laravel-10/laravel-lumen-crud-wizard

[macropay-solutions]

Postman collection:
laravel-10-crud-test.postman_collection.json

[macropay-solutions]

Added simplePaginate on request to not count the results (also for the free version)
http://89.40.19.34/laravel-10/api/products?simplePaginate=1&page=104859&limit=5

{
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null
    },
    "maxs": {
        "value_max": null
    },
    "index_required_on_filtering": [
        "id",
        "created_at",
        "value",
        "currency",
        "code",
        "ean",
        "name"
    ],
    "DEMO_ONLY_sql_debugger": [
        "select * from `products` order by `created_at` desc limit 6 offset 524290"
    ],
    "current_page": 104859,
    "data": [
        {
            "id": 2,
            "ean": "produc21",
            "name": "product2",
            "code": "product2",
            "currency": "EUR",
            "value": "10.05",
            "created_at": "2024-01-12 19:30:58",
            "updated_at": null,
            "primary_key_identifier": "2"
        },
        {
            "id": 1,
            "ean": "product1",
            "name": "new name",
            "code": "product1",
            "currency": "EUR",
            "value": "10.00",
            "created_at": "2024-01-03 19:38:37",
            "updated_at": "2024-03-18 07:54:15",
            "primary_key_identifier": "1"
        }
    ],
    "from": 524291,
    "per_page": 5,
    "to": 524292
}

vs

http://89.40.19.34/laravel-10/api/products?page=104859&limit=5

{
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null
    },
    "maxs": {
        "value_max": null
    },
    "index_required_on_filtering": [
        "id",
        "created_at",
        "value",
        "currency",
        "code",
        "ean",
        "name"
    ],
    "DEMO_ONLY_sql_debugger": [
        "select count(*) as aggregate from `products`",
        "select * from `products` order by `created_at` desc limit 5 offset 524290"
    ],
    "current_page": 104859,
    "data": [
        {
            "id": 2,
            "ean": "produc21",
            "name": "product2",
            "code": "product2",
            "currency": "EUR",
            "value": "10.05",
            "created_at": "2024-01-12 19:30:58",
            "updated_at": null,
            "primary_key_identifier": "2"
        },
        {
            "id": 1,
            "ean": "product1",
            "name": "new name",
            "code": "product1",
            "currency": "EUR",
            "value": "10.00",
            "created_at": "2024-01-03 19:38:37",
            "updated_at": "2024-03-18 07:54:15",
            "primary_key_identifier": "1"
        }
    ],
    "from": 524291,
    "last_page": 104859,
    "per_page": 5,
    "to": 524292,
    "total": 524292
}

[macropay-solutions]

Added cursor paginate also to the free version:
http://89.40.19.34/laravel-10/api/products?limit=2&cursor=

{
    "cursor": "eyJpZCI6MiwiX3BvaW50c1RvTmV4dEl0ZW1zIjp0cnVlfQ",
    "has_more_pages": true,
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null
    },
    "maxs": {
        "value_max": null
    },
    "index_required_on_filtering": [
        "id",
        "created_at",
        "value",
        "currency",
        "code",
        "ean",
        "name"
    ],
    "DEMO_ONLY_sql_debugger": [
        "0.88 ms, sql: select * from `products` order by `id` asc limit 3"
    ],
    "data": [
        {
            "id": 1,
            "ean": "product1",
            "name": "new name",
            "code": "product1",
            "currency": "EUR",
            "value": "10.00",
            "created_at": "2024-01-03 19:38:37",
            "updated_at": "2024-03-18 07:54:15",
            "primary_key_identifier": "1"
        },
        {
            "id": 2,
            "ean": "produc21",
            "name": "product2",
            "code": "product2",
            "currency": "EUR",
            "value": "10.05",
            "created_at": "2024-01-12 19:30:58",
            "updated_at": null,
            "primary_key_identifier": "2"
        }
    ],
    "per_page": 2
}

[macropay-solutions]

New feature. limit=0 generates only count query.

http://89.40.19.34/laravel-10/api/operations?limit=0

{
    "has_more_pages": false,
    "sums": {
        "value": null
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null,
        "created_at_min": null
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "DEMO_ONLY_sql_debugger": [
        "0.89 ms, sql: select count(*) as aggregate from `operations` where `id` >= -9223372036854775808"
    ],
    "current_page": 0,
    "data": [],
    "from": null,
    "last_page": 0,
    "per_page": 0,
    "to": null,
    "total": 3748918
}

http://89.40.19.34/laravel-9/api/operations?limit=0&sums[]=value

{
    "has_more_pages": false,
    "sums": {
        "value": "204351066.06"
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null,
        "created_at_min": null
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "DEMO_ONLY_sql_debugger": [
        "0.74 ms, sql: select count(*) as aggregate from `operations` where `id` >= -9223372036854775808",
        "0.53 ms, sql: select SUM(`operations`.`value`) as value from `operations` where `id` >= -9223372036854775808 limit 1"
    ],
    "current_page": 0,
    "data": [],
    "from": null,
    "last_page": 0,
    "per_page": 0,
    "to": null,
    "total": 3748918
}

http://89.40.19.34/laravel-9/api/operations?limit=0&sums[]=value&aggregates[groupBys][]=currency

{
    "has_more_pages": false,
    "sums": {
        "value": "204351066.06"
    },
    "avgs": {
        "value_avg": null
    },
    "mins": {
        "value_min": null,
        "created_at_min": null
    },
    "maxs": {
        "value_max": null,
        "created_at_max": null
    },
    "index_required_on_filtering": [
        "id",
        "parent_id",
        "client_id",
        "created_at",
        "currency",
        "value"
    ],
    "aggregated_total_count": 3748918,
    "DEMO_ONLY_sql_debugger": [
        "18732.83 ms, sql: select count(*) as aggregate from (select SUM(`operations`.`value`) as value, AVG(`operations`.`value`) as value_avg, MIN(`operations`.`value`) as value_min, MIN(`operations`.`created_at`) as created_at_min, MAX(`operations`.`value`) as value_max, MAX(`operations`.`created_at`) as created_at_max, COUNT(DISTINCT `operations`.`id`) as group_count, `operations`.`currency` as currency from `operations` group by `currency`) as `aggregate_table`",
        "0.32 ms, sql: select count(*) as aggregate from `operations` where `id` >= -9223372036854775808",
        "0.47 ms, sql: select SUM(`operations`.`value`) as value from `operations` where `id` >= -9223372036854775808 limit 1"
    ],
    "current_page": 0,
    "data": [],
    "from": null,
    "last_page": 0,
    "per_page": 0,
    "to": null,
    "total": 1
}

[macropay-solutions]

We invite you all to try our URL QUERY LANGUAGE (without postman), directly into the lib presentation page.

For start, you can run the queries from previous replies.

http://89.40.19.34/laravel-9/laravel-lumen-crud-wizard

http://89.40.19.34/laravel-10/laravel-lumen-crud-wizard

The tables have millions rows so, some queries might timeout and File not found will be displayed in the browser.

[dmitrychurkin]

It would be great if the free version could have basic filtering and multi sorting capabilities.

[macropay-solutions]

@dmitrychurkin Thank you for your reply. We will discuss this internally and get back to you with an answer ASAP.

[macropay-solutions]

@dmitrychurkin It is our pleasure to announce the release o the free version that now contains also basic filters and sort: https://packagist.org/packages/macropay-solutions/laravel-crud-wizard-free

Bear in mind that for the free version, GET /resource?column=value will translate to sql: column = 'value' as opposed to the paid version: column like 'value%'.
To get column = 'value' in sql in the paid version the query should be GET /resource?column[in][]=value

@taylorotwell This basic filter and sort could be incorporated into the apiResource in the index action.

[macropay-solutions]

Added List relation as resource: to the test page.
INFO:
Multiple queries to fetch hydrated model are done in order to have the ability to check the state in the relation function definition. This is an inverse(or reverse) search that does not use Eloquent relation->where to improve speed and ease the index usage for mysql as it can be seen in the last query.
This is equivalent to

/operations?relationsFilters[parent][id][in][]=2&currency[in][]=EUR

but it enforces the id of the parent in route.

[macropay-solutions]

Released big improvement for update/upsert in the free version: fix-upsert-validation