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