Easily create complex database queries with the Query Enrich Package

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.

---

The post Easily create complex database queries with the Query Enrich Package appeared first on Laravel News.

Join the Laravel Newsletter to get all the latest Laravel articles like this directly in your inbox.