如何将原始查询 MYSQL 转换为 Laravel 框架

Question

我不知道如何在 Laravel 5.8 中使用下面的代码 我已经尝试过，但我无法正常工作。

$sql = mysqli_query($db, "SELECT *, SUM(amount) AS SumBudget FROM messages GROUP BY contact_phone ORDER BY SUM(amount) DESC LIMIT 3");
$orderList = 0;
while ($data = mysqli_fetch_assoc($sql))
{
$orderList++;
$user = $data['user']; 
$cost = $data['SumBudget'];
if ($orderList == 1) {
printf("%d. %s %d‎฿<br>", $orderList, $user, $cost);
}
else if ($orderList == 2)
{
printf("%d. %s %d‎฿<br>", $orderList, $user, $cost);
}
else 
{
printf("%d. %s %d‎฿<br>", $orderList, $user, $cost);
}
}

我的代码已经尝试过了。

$data = DB::table('messages')
->where('phone_number', $request->phone_number)
->select(DB::raw('SUM(amount) as cost'))
->groupBy(DB::raw('contact_phone'))
->orderBy(DB::raw('SUM(amount)', 'DESC'))
->limit(3)
->get();

谁能帮我，谢谢。

Answer 1

我认为它可以帮助你：

DB::table('messages')
->select('column_name', /* ... */, DB::raw('SUM(amount) AS cost'))
->where('phone_number', $request->phone_number) // if you want to filter
->groupBy('contact_phone')
->orderBy('cost', 'DESC')
->take(3)
->get()