PHP code example of angel-source-labs / laravel-expressions

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

angel-source-labs / laravel-expressions example snippets

    public function testSelectRawUsingExpression()
    {
        $expression = new Expression("price as price_before_tax");
        $sql = DB::table('orders')->selectRaw($expression)->toSql();
        $this->assertEquals('select price as price_before_tax from `orders`', $sql);
    }

        $expression = new Expression("inet_aton(?)", ["192.168.0.1"]);
        DB::table('audits')->where('ip', $expression)->get();

public class InetAtoN extends Expression
{
    public function __construct($address)
    {
        parent::__construct("inet_aton(?)", $address);
    }
}

DB::table('audits')->where('ip', new InetAtoN("192.168.0.1"))->get();

public class Point extends Expression
{
    public function __construct($lat, $lng)
    {
        parent::__construct("ST_GeomFromText(?, ?)", [$lng, $lat]);
    }
}

$model->point = new Point(44.9561062,-93.1041534);
$model->save();

class ClassIsExpression implements IsExpression
{
    use ProvidesExpression;
}

function testSelectRawUsingExpression()
{
    $expression = new ClassIsExpression("price as price_before_tax");
    $sql = DB::table('orders')->selectRaw($expression)->toSql();
    $this->assertEquals('select price as price_before_tax from `orders`', $sql);
}

use Illuminate\Database\Query\Expression as BaseExpression;

class Expression extends BaseExpression implements IsExpression
{
    use ProvidesExpression;
}

$grammar = ExpressionGrammar::make()
        ->mySql("ST_GeomFromText(?, ?)")
        ->mySql("ST_GeomFromText(?, ?, 'axis-order=long-lat')", "8.0")
        ->postgres("ST_GeomFromText(?, ?)");
$expression = new Expression($grammar, [$lon, $lat]);

public class Point extends Expression
{
    public function __construct($lat, $lng)
    {
        parent::__construct(ExpressionGrammar::make()
            ->mySql("ST_GeomFromText(?, ?)")
            ->mySql("ST_GeomFromText(?, ?, 'axis-order=long-lat')", "8.0")
            ->postgres("ST_GeomFromText(?, ?)"), 
        [$lng, $lat]);
    }
}

$model->point = new Point(44.9561062,-93.1041534);
$model->save();

    $expression = new Expression("price * ? as price_with_tax", [1.0825]);
    DB::table('orders')->select($expression)->get();

    $expression = new Expression("price * ? as price_with_tax", [1.0825]);
    DB::table('orders')->selectRaw($expression)->get();

    $expression = new Expression("price * ? as price_with_tax, price * ? as profit", [1.0825]);
    DB::table('orders')->selectRaw($expression, [.20])->get();

    $expression = new Expression('price > IF(state = "TX", ?, 100)', [200]);
    DB::table('orders')->whereRaw($expression)->get();

    $expression = new Expression('price > IF(state = "TX", ?, ?)', [200]);
    DB::table('orders')->whereRaw($expression, [100])->get();

    $expression = new Expression('SUM(price) > ?', [2500]);
    $sql = DB::table('orders')
        ->select('department', DB::raw('SUM(price) as total_sales'))
        ->groupBy('department')
        ->havingRaw($expression)
        ->get();

        $expression = new Expression('SUM(price) > ? and AVG(price) > ?', [2500]);
        $sql = DB::table('orders')
            ->select('department', DB::raw('SUM(price) as total_sales'))
            ->groupBy('department')
            ->havingRaw($expression, [100])
            ->get();

    $ids = [12,23,34,45];
    $expression = new Expression('field(id, ?, ?, ?, ?)', $ids);
    DB::table('orders')
        ->whereIn('id', $ids)
        ->orderByRaw($expression)
        ->get();

    $ids = [12,23,34,45];
    $expression = new Expression('field(id, ?, ?, ?, ?)', [12,23]);
    DB::table('orders')
        ->whereIn('id', $ids)
        ->orderByRaw($expression,[34,45])
        ->get();

    $expression = new Expression('price > ?', [100]);
    DB::table('orders')
        ->select('department', 'price')
        ->groupByRaw($expression)
        ->get();

        $expression = new Expression('price > ?, department > ?', [100]);
        DB::table('orders')
            ->select('department', 'price')
            ->groupByRaw($expression, [1560])
            ->get();

    $expression = new Expression("inet_aton(?)", ["192.168.0.1"]);
    DB::table('audits')->where('ip', $expression)->get();

$users = DB::table('users')
                ->where('votes', '=', $expression)
                ->where('age', '>', $expression)
                ->get();

$users = DB::table('users')->where('votes', $expression)->get();

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

$users = DB::table('users')
           ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->get();

use App\Models\User;

$users = User::where(function ($query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

use App\Models\Income;

$incomes = Income::where('amount', '<', function ($query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

sql
    select price * ? as price_with_tax from `orders`; # bindings = [1 => 1.0825]

sql
    select price * ? as price_with_tax from `orders`; # bindings = [1 => 1.0825]

sql
    select price * ? as price_with_tax, price * ? as profit from `orders`; # bindings = [1 => 1.0825, 2 => 0.20]

sql
    select * from `orders` where price > IF(state = "TX", ?, ?); # bindings = [1 => 200, 2 => 100]

sql
    select `department`, SUM(price) as total_sales from `orders` group by `department` having SUM(price) > ?; # bindings = [1 => 2500]

sql
    select `department`, SUM(price) as total_sales from `orders` group by `department` having SUM(price) > ? and AVG(price) > ?; # bindings = [1 => 2500, 2 => 100]

sql
    select * from `orders` where `id` in (?, ?, ?, ?) order by field(id, ?, ?, ?, ?); # bindings = [1 => 12, 2 => 23, 3 => 34, 4 => 45, 5 => 12, 6 => 23, 7 => 34, 8 => 45]

sql
    select * from `orders` where `id` in (?, ?, ?, ?) order by field(id, ?, ?, ?, ?); # bindings = [1 => 12, 2 => 23, 3 => 34, 4 => 45, 5 => 12, 6 => 23, 7 => 34, 8 => 45]