Tips & Tricks: статті за тегом «Database Views»

// Створити міграцію для 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')
    ");
}