Posted on: March 30, 2023 10:16 AM

Posted by: Renato

Categories: Laravel laravelphp developer Dicas query mysql postgresql

Views: 282

Substitua chamadas de consulta brutas por expressões de consulta do Laravel

..

Replace Raw Query Calls With Laravel Query Expressions
 

O pacote Query Expressions para Laravel substitui qualquer chamada de consulta bruta por expressões. Em vez de escrever o seguinte exemplo do leia-me :

// Instead of:

User::query()

->when(isPostgreSQL(), fn ($query) => $query->selectRaw('coalesce("user", "admin") AS "value"'))

->when(isMySQL(), fn ($query) => $query->selectRaw('coalesce(`user`, `admin`) AS `value`'))

.

Você poderia escrever algo como o seguinte usando este pacote:

.

Além do código terser, por que você deseja usar algo como este pacote? O leia-me descreve como este pacote se encaixa em seus aplicativos Laravel:

A implementação do banco de dados do Laravel fornece uma boa maneira de trabalhar com vários bancos de dados enquanto abstrai seu funcionamento interno...

No entanto, quando queremos usar mais funcionalidade de banco de dados do que o Laravel fornece, devemos recorrer a expressões SQL brutas e escrever código específico do banco de dados. O pacote Query Expressions se baseia em novos recursos introduzidos no Laravel 10 para resolver esse problema.

No momento da redação, este pacote oferece suporte a várias expressões, como:

• valores
• Apelido
• Operadores aritméticos
• Operadores bit a bit
• Comparação e operadores lógicos
• funções agregadas
• Condicional

----->>>>>

• Values
• Aliases
• Arithmetic operators
• Bitwise operators
• Comparison and Logical operators
• Aggregate functions
• Conditional

Se você quiser saber mais sobre este pacote, confira laravel-query-expressions no GitHub. Você pode instalar este pacote em seu projeto Laravel 10 via composer:

composer require tpetry/laravel-query-expressions

.

Informações do Github

Laravel Query Expressions to replace DB::raw() calls

Laravel's database implementation provides a good way of working with multiple databases while abstracting away their inner workings. You don't have to consider minor syntax differences when using a query builder or how each database handles specific operations slightly differently.

However, when we want to use more database functionality than Laravel provides, we must fall back to raw SQL expressions and write database-specific code. The Query Expressions package builds on new features introduced in Laravel 10 to solve that problem. All provided implementations abstract some SQL functionality that is automatically transformed to the correct syntax with the same behaviour for your used database engine. And if your version is still supported by Laravel but is missing a feature, it is emulated by the implementations. So you can even do things that were not possible before.

You can make your queries database independent:

// Instead of:
User::query()
    ->when(isPostgreSQL(), fn ($query) => $query->selectRaw('coalesce("user", "admin") AS "value"'))
    ->when(isMySQL(), fn ($query) => $query->selectRaw('coalesce(`user`, `admin`) AS `value`'))

// You can use:
User::select(new Alias(new Coalesce(['user', 'admin']), 'count'));

And you can also create new powerful queries:

// Aggregate multiple statistics with one query for dashboards:
Movie::select([
    new CountFilter(new Equal('released', new Number(2021))),
    new CountFilter(new Equal('released', new Number(2022))),
    new CountFilter(new Equal('genre_id', new Number(12))),
    new CountFilter(new Equal('genre_id', new Number(35))),
])->where('streamingservice', 'netflix');

Installation

You can install the package via composer:

composer require tpetry/laravel-query-expressions

Usage

This package implements a lot of expressions you can use for selecting data, do better filtering or ordering of rows. Every expression can be used exactly as stated by the documentation, but you can also combine them as shared in the example before. Whenever an expression class needs a string|Expression parameter, you can pass a column name or another (deeply nested) expression object.

Note A string passed for a string|Expression parameter is always used as a column name that will be automatically quoted.

Warning The generated SQL statements of the examples are only for explanatory purposes. The real ones will be automatically tailored to your database using proper quoting and its specific syntax.

Language

Values

As stated before, an expression is always a column name. But if you want to e.g. do an equality check, you may want to compare something to a specific value. That's where you should use the Number class.

use Tpetry\QueryExpressions\Value\Number;

new Number(44);
new Number(3.1415);

Note The Number class in isolation is not that usefull. But it will be used more in the next examples.

Alias

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Language\Alias;
use Tpetry\QueryExpressions\Value\Number;

new Alias(string|Expression $expression, string $name)

User::select([
    new Alias('last_modified_at', 'modification_date'),
    new Alias(new Value(21), 'min_age_threshold'),
])->get();

Note The Alias class in isolation is not that usefull because Eloquent can already do this. But it will be used more in the next examples.

Operators

Arithmetic Operators

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Arithmetic\{
    Add, Divide, Modulo, Multiply, Power, Subtract,
};
use Tpetry\QueryExpressions\Operator\Value\Number;

new Add(string|Expression $value1, string|Expression $value2);
new Divide(string|Expression $value1, string|Expression $value2);
new Modulo(string|Expression $value1, string|Expression $value2);
new Multiply(string|Expression $value1, string|Expression $value2);
new Power(string|Expression $value1, string|Expression $value2);
new Subtract(string|Expression $value1, string|Expression $value2);

// UPDATE user_quotas SET credits = credits - 15 WHERE id = 1985
$quota->update([
    'credits' => new Subtract('credits', new Number(15)),
]);

// SELECT id, name, (price - discount) * 0.2 AS vat FROM products
Product::select([
    'id',
    'name',
    new Alias(new Multiply(new Subtract('price', 'discount'), Number(0.2)), 'vat')
])->get();

Bitwise Operators

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Bitwise\{
    BitAnd, BitNot, BitOr, BitXor, ShiftLeft, ShiftRight,
};
use Tpetry\QueryExpressions\Operator\Value\Number;

new BitAnd(string|Expression $value1, string|Expression $value2);
new BitNot(string|Expression $value);
new BitOr(string|Expression $value1, string|Expression $value2);
new BitXor(string|Expression $value1, string|Expression $value2);
new ShiftLeft(string|Expression $value, string|Expression $times);
new ShiftRight(string|Expression $value, string|Expression $times);

// SELECT * FROM users WHERE (acl & 0x8000) = 0x8000
User::where(new BitAnd('acl', 0x8000), 0x8000)
    ->get();

Comparison & Logical Operators

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Comparison\{
    Between, DistinctFrom, Equal, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, NotDistinctFrom, NotEqual
};
use Tpetry\QueryExpressions\Operator\Logical\{
    CondAnd, CondNot, CondOr, CondXor
};
use Tpetry\QueryExpressions\Operator\Value\Number;

new Between(string|Expression $value, string|Expression $min, string|Expression $max);
new DistinctFrom(string|Expression $value1, string|Expression $value2);
new Equal(string|Expression $value1, string|Expression $value2);
new GreaterThan(string|Expression $value1, string|Expression $value2);
new GreaterThanOrEqual(string|Expression $value1, string|Expression $value2);
new LessThan(string|Expression $value1, string|Expression $value2);
new LessThanOrEqual(string|Expression $value1, string|Expression $value2);
new NotDistinctFrom(string|Expression $value1, string|Expression $value2);
new NotEqual(string|Expression $value1, string|Expression $value2);

new CondAnd(string|Expression $value1, string|Expression $value2);
new CondNot(string|Expression $value);
new CondOr(string|Expression $value1, string|Expression $value2);
new CondXor(string|Expression $value1, string|Expression $value2);

// Examples in Aggregates::countFilter()

Warning These objects can currently not be used with Laravel's where(). They are only for logical operations within aggregates or as selected columns. I am working on fixing that ;)

Functions

Aggregates

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Function\Aggregate\{
    Avg, Count, CountFilter, Max, Min, Sum, SumFilter,
};
use Tpetry\QueryExpressions\Operator\Value\Number;

new Avg(string|Expression $value);
new Count(string|Expression $value);
new CountFilter(string|Expression $filter);
new Max(string|Expression $value);
new Min(string|Expression $value);
new Sum(string|Expression $value);
new SumFilter(string|Expression $value, string|Expression $filter);

// SELECT COUNT(*) AS visits, AVG(duration) AS duration FROM blog_visits WHERE ...
BlogVisit::select([
    new Alias(new Count('*'), 'visits'),
    new Alias(new Avg('duration'), 'duration'),
])
->whereDay('created_at', now())
->get();

// SELECT
//   COUNT(*) FILTER (WHERE (released = 2021)) AS released_2021,
//   COUNT(*) FILTER (WHERE (released = 2022)) AS released_20212,
//   COUNT(*) FILTER (WHERE (genre_id = 12)) AS genre_12,
//   COUNT(*) FILTER (WHERE (genre_id = 35)) AS genre_35
// FROM movies
// WHERE streamingservice = 'netflix'
Movie::select([
    new Alias(new CountFilter(new Equal('released', new Number(2021))), 'released_2021'),
    new Alias(new CountFilter(new Equal('released', new Number(2022))), 'released_2022'),
    new Alias(new CountFilter(new Equal('genre_id', new Number(12))), 'genre_12'),
    new Alias(new CountFilter(new Equal('genre_id', new Number(35))), 'genre_35'),
])
    ->where('streamingservice', 'netflix')
    ->get();

Conditional

use Tpetry\QueryExpressions\Function\Conditional\{
    Coalesce, Greatest, Least
};
use Tpetry\QueryExpressions\Language\Alias;

new Coalesce(array $expressions);
new Greatest(array $expressions);
new Least(array $expressions);

// SELECT GREATEST(published_at, updated_at, created_at) AS last_modification FROM blog_articles
BlogArticle::select([
    new Alias(new Greatest('published_at', 'updated_at', 'created_at'), 'last_modification')
])
->get();

Fontes:

- https://laravel-news.com/query-expressions-for-laravel

- https://github.com/tpetry/laravel-query-expressions

Renato

Developer

No comments yet! Be the first to comment