How to convert raw query MYSQL to Laravel framework

Question

I don't know how i can use this code below in Laravel 5.8 I already tried but i doesn't work correctly.

$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);
    }
}

My code already tried.

$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();

Can anyone help me, Thank you.

Hint: You also can use a RAW query in Laraval without using the Query builder. — Raymond Nijland
– Raymond Nijland, Commented Aug 18, 2019 at 16:58
Yep, i use DB:: then put my code after DB:: and laravel give me error Syntax error or access violation: 1055 — Dake
– Dake, Commented Aug 18, 2019 at 17:00
I think we talk past one and other.. i meant DB::connection()->getPdo()->query(...) should be fine you don't have user input in your qeury.. Atleast not in the example at the top.. — Raymond Nijland
– Raymond Nijland, Commented Aug 18, 2019 at 17:04
... which makes me wonder why is there a WHERE in the laravel PHP code it wasn't in the native PHP/MySQL code.. — Raymond Nijland
– Raymond Nijland, Commented Aug 18, 2019 at 17:07
.. also SELECT * GROUP BY contact_phone is generally not how you should be writing SQL.. MySQL Handling of GROUP BY . — Raymond Nijland
– Raymond Nijland, Commented Aug 18, 2019 at 17:10

bjovanov · Accepted Answer · 2019-08-18 17:12:12Z

1

I think it could help you:

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()

edited Aug 18, 2019 at 17:12

answered Aug 18, 2019 at 17:07

bjovanov

4814 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raymond Nijland Over a year ago

SELECT * GROUP BY contact_phone is generally not how you should be writing SQL.. MySQL Handling of GROUP BY .. Running this query can mean invalid results..

bjovanov Over a year ago

@RaymondNijland You're absolutely right one more time. I updated my example :)

Dake Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.messages.id' isn't in GROUP BY (SQL: select id, SUM(amount) AS cost from messages where phone_number = 0973279939 group by contact_phone order by cost desc limit 3)

Dake Over a year ago

I adjust select column_name to select id then i got an error Syntax error or access violation: 1055

bjovanov Over a year ago

Yes because id column isn't in group by clause. Mysql doesn't know how to group all these differents IDs. Because you don't seems to need this ID in your loop so I wouldn't take it in your Mysql query. You should select only user column (I suppose for one phone number, you always have the same user) and SUM(amount) AS cost

Collectives™ on Stack Overflow

How to convert raw query MYSQL to Laravel framework

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related