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 Laravel articles like this directly in your inbox.
