How to Convert SQL to Laravel Query

Question

I want to build something with an aliases column like below, but I don't know how to make it a Laravel query. The following is my SQL.

SELECT
    d.*,
    d.damaged_building,
    d.total_victim 
FROM
    (
        SELECT
            delete_flg,
            damage_id,
            create_time,
            reg_user_name,
            municipality_id,
            report_time,
            district,
            village,
            disaster_type,
            cause_of_disaster,
            (
                bd_major_damage1 + bd_minor_damage1 + bd_major_damage2 + bd_minor_damage2 + bd_major_damage3 + bd_minor_damage3
            )
            as damaged_building,
            (
                hi_hd_deaths + hi_hd_serious_injuries + hi_hd_minor_injuries + hi_hd_missing_persons + hi_hd_sick_persons
            )
            as total_victim 
        FROM
            d_damage 
        WHERE
            delete_flg = 0 
        ORDER BY
            create_time DESC
    )
    d

I want to translate this to Eloquent of Laravel Query Builder for use in my controller.

why do u use sub-query without any conditions outside? – Ryan Nghiem May 06 '19 at 04:25 — Ryan Nghiem, May 06 '19 at 04:25

score 0 · Answer 1 · answered May 06 '19 at 04:25

If I were you just stick to learning the SQL Queries since you bring it anywhere you can learn alot of frameworks / tools, rather than investing your time in Laravel's. Anyway, you still can use sql queries in laravel using the raw expressions : https://laravel.com/docs/5.8/queries#raw-expressions

Mohammad Sohel · Accepted Answer · 2019-05-06T05:52:41.113

0

You may try with this

DB::table('d_damage')
->where('delete_flg',0)
->orderBy('create_time', 'desc')
->select('delete_flg', 'damage_id', 'create_time', 'reg_user_name', 'municipality_id', 'report_time', 'district', 'village', 'disaster_type', 'cause_of_disaster',DB::raw('(bd_major_damage1 + bd_minor_damage1 + bd_major_damage2 + bd_minor_damage2 + bd_major_damage3 + bd_minor_damage3) as damaged_building'),DB::raw('(hi_hd_deaths + hi_hd_serious_injuries + hi_hd_minor_injuries + hi_hd_missing_persons + hi_hd_sick_persons) as total_victim'))
->get();

this may be not exactly but almost

edited May 06 '19 at 05:52

answered May 06 '19 at 05:30

Mohammad Sohel

16
3

thanks, i already get it. i used view that based from tabel (d'damage'). thanks for your answer! – Faisal Tanjung May 17 '19 at 06:26

How to Convert SQL to Laravel Query

2 Answers2