Laravel Packages

Easily create complex database queries with the Query Enrich Package

Published
Easily create complex database queries with the Query Enrich Package image

Laravel Query Enrich is designed to easily create complex database queries in Laravel without having to write complicated SQL code. Here are some examples taken from the readme:

Example of fetching orders placed in the last 7 days

With Laravel Query Enrich

$recentOrders = DB::table('orders')
    ->where(c('created_at'), '>=', QE::subDate(QE::now(), 7, Unit::DAY))
    ->get();

Without Laravel Query Enrich

$recentOrders = DB::table('orders')
    ->whereRaw('created_at >= NOW() - INTERVAL ? DAY', 7)
    ->get();

Raw Query

SELECT *
FROM `orders`
WHERE `created_at` >= NOW() - INTERVAL 7 DAY;

Using the avg function for grabbing the average monthly price for oil and gas

With Laravel Query Enrich

$monthlyPrices = DB::table('prices')
    ->select(
        QE::avg(c('oil'))->as('oil'),
        QE::avg(c('gas'))->as('gas'),
        'month'
    )
    ->groupBy('month')
    ->get();

Without Laravel Query Enrich

$monthlyPrices = DB::table('prices')
    ->select(DB::raw('avg(`oil`) as `oil`, avg(`gas`) as `gas`, `month`'))
    ->groupBy('month')
    ->get();

Raw Query

select avg(`oil`) as `oil`, avg(`gas`) as `gas`, `month`
from `prices`
group by `month`

Using an exists query

With Laravel Query Enrich

$authors = DB::table('authors')->select(
    'id',
    'first_name',
    'last_name',
    QE::exists(
        Db::table('books')->where('books.author_id', c('authors.id'))
    )->as('has_book')
)->orderBy(
    'authors.id'
)->get();

Without Laravel Query Enrich

$authors = DB::table('authors')
->select(
    'id',
    'first_name',
    'last_name',
    DB::raw('exists(select * from `books` where `books`.`author_id` = `authors`.`id`) as `has_book`'))
->orderBy(
    'authors.id',
)
->get();

Raw Query

select `id`,
       `first_name`,
       `last_name`,
       exists(select * from `books` where `books`.`author_id` = `authors`.`id`) as `result`
from `authors`
order by `authors`.`id` asc

Getting a full name using concatws

With Laravel Query Enrich

$authors = Author::select(
    'first_name',
    'last_name',
    QE::concatWS(' ', c('first_name'), c('last_name'))->as('result')
)->get();

Without Laravel Query Enrich

$author = Author::select(
    'first_name',
    'last_name',
    DB::raw("concat_ws(' ', `first_name`, `last_name`) as `result`")
)->first();

Raw Query

select `first_name`, `last_name`, concat_ws(' ', `first_name`, `last_name`) as `result`
from `authors`

Check out the documentation for complete details and view the package on Github.

Eric L. Barnes photo

Eric is the creator of Laravel News and has been covering Laravel since 2012.

Sponsored

laravelcloud logo
Laravel Cloud

Easily create and manage your servers and deploy your Laravel applications in seconds.

Visit Laravel Cloud

The latest

View all →
pgjumbo: A PostgreSQL Client for macOS, Windows, and Linux image

pgjumbo: A PostgreSQL Client for macOS, Windows, and Linux

Read article
Laranon: Reversible PII Anonymization for Laravel and LLMs image

Laranon: Reversible PII Anonymization for Laravel and LLMs

Read article
PHP 8.6 is released!  image

PHP 8.6 is released!

Read article
Laravel Warrant: Row-Level Permissions With Eloquent image

Laravel Warrant: Row-Level Permissions With Eloquent

Read article
JSON Schema Validation Rules and a RetryFor Attribute in Laravel 13.43 image

JSON Schema Validation Rules and a RetryFor Attribute in Laravel 13.43

Read article
Durable Workflow: Long-Running Workflows for Laravel image

Durable Workflow: Long-Running Workflows for Laravel

Read article