Статті за тегом «SQL»

// Створити міграцію для view
public function up()
{
    DB::statement("
        CREATE VIEW user_stats AS
        SELECT
            users.id,
            users.name,
            users.email,
            COUNT(DISTINCT orders.id) as total_orders,
            SUM(orders.total) as total_spent,
            MAX(orders.created_at) as last_order_date
        FROM users
        LEFT JOIN orders ON users.id = orders.user_id
        GROUP BY users.id, users.name, users.email
    ");
}

public function down()
{
    DB::statement("DROP VIEW IF EXISTS user_stats");
}

// Створити модель для view
namespace App\Models;

class UserStats extends Model
{
    protected $table = 'user_stats';
    public $timestamps = false;
    public $incrementing = false;
}

// Використовувати view як звичайну модель
$topCustomers = UserStats::where('total_orders', '>', 10)
    ->orderBy('total_spent', 'desc')
    ->limit(100)
    ->get();

// В контролері
public function dashboard()
{
    $stats = UserStats::where('last_order_date', '>=', now()->subDays(30))
        ->orderBy('total_spent', 'desc')
        ->paginate(50);

    return view('admin.customers', compact('stats'));
}

// Складний view звітності
public function up()
{
    DB::statement("
        CREATE VIEW monthly_revenue AS
        SELECT
            DATE_FORMAT(created_at, '%Y-%m') as month,
            COUNT(*) as order_count,
            SUM(total) as revenue,
            AVG(total) as avg_order_value,
            COUNT(DISTINCT user_id) as unique_customers
        FROM orders
        WHERE status = 'completed'
        GROUP BY DATE_FORMAT(created_at, '%Y-%m')
    ");
}

// Обчислення на стороні бази даних
$products = Product::selectRaw('
    id,
    name,
    price,
    price * 1.2 as price_with_tax,
    price * quantity as total
')->get();

// Обчислення віку
$users = User::selectRaw('
    id,
    name,
    YEAR(CURDATE()) - YEAR(birthdate) as age
')->get();

// Конкатенація
$users = User::selectRaw("
    CONCAT(first_name, ' ', last_name) as full_name,
    email
")->get();

// Безпечний parameter binding
$taxRate = 1.15;
$products = Product::selectRaw(
    'price * ? as price_with_tax',
    [$taxRate]
)->get();

// Обчислення відстані
$lat = 40.7128;
$lng = -74.0060;

$stores = Store::selectRaw("
    *,
    (6371 * acos(cos(radians(?))
    * cos(radians(lat))
    * cos(radians(lng) - radians(?))
    + sin(radians(?))
    * sin(radians(lat)))) AS distance
", [$lat, $lng, $lat])
    ->having('distance', '<', 10)
    ->orderBy('distance')
    ->get();

// Складні обчислення з декількома параметрами
$discount = 0.1;
$minPrice = 100;

$items = Product::selectRaw('
    id,
    name,
    price,
    price * (1 - ?) as discounted_price,
    CASE WHEN price > ? THEN "premium" ELSE "standard" END as tier
', [$discount, $minPrice])->get();