How to convert this MySQL query to Laravel?

Question

Here my MySQL query (work in phpMyAdmin) :

SELECT workcenter, (SUM(w1+w2 +w3 +w4)/ (COUNT(DISTINCT(teknisi))*40*4) )*100 AS total FROM `team` GROUP by workcenter ORDER BY total

then, i try in Laravel Sintax like this below (not work) :

$sql = Team::groupBy('workcenter')->select('workcenter', \DB::raw('(SUM(w1+w2+w3+w4)/ (COUNT(DISTINCT(teknisi))*30*4) )*100 AS total'))
            ->OrderBy('total', 'Desc')
            ->get();

When i run the laravel sintax, its doesn't show any errors, but the output is nothing..

Please anyone help me to convert the MySQL query to Laravel Sintax. Thank you!

I have tried your code. It works fine. Did your model connect the right database or define the right table name? — TsaiKoga
– TsaiKoga, Commented Nov 4, 2019 at 3:33

Victor Lava · Accepted Answer · 2019-11-04 03:31:04Z

1

I think you are close enough, however, this doesn't look like a correct way to group by with Eloquent ORM. Try using raw expressions, something like this might work:

$sql = DB::table('team')
                     ->select(DB::raw('workcenter, (SUM(w1+w2 +w3 +w4)/ (COUNT(DISTINCT(teknisi))*40*4) )*100 as total'))
                     ->orderBy('total', 'desc')
                     ->groupBy('workcenter')
                     ->get();

More about raw expressions here - https://laravel.com/docs/6.x/queries#raw-expressions

answered Nov 4, 2019 at 3:31

Victor Lava

1214 bronze badges

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

1 Comment

mrobbizulfikar Over a year ago

thank for your answer sir, i have copy+paste the sintax, but still not working,, hmm

idirsun · Accepted Answer · 2019-11-04 05:34:50Z

0

Whenever I want to convert SQL query to Laravel I always change one column name, the laravel error report will show your current query and u can compare it to the SQL query

answered Nov 4, 2019 at 5:34

idirsun

6147 silver badges8 bronze badges

2 Comments

mrobbizulfikar Over a year ago

my sintax isn't resulting any error, but only show 'nothing' idk whyy :(

idirsun Over a year ago

if you change the column name workcenter to workcente2r for example that should have result to a column not found error and will output your query with the wrong column.

Collectives™ on Stack Overflow

How to convert this MySQL query to Laravel?

Please anyone help me to convert the MySQL query to Laravel Sintax. Thank you!

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Please anyone help me to convert the MySQL query to Laravel Sintax. Thank you!

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related