Convert core php query to laravel 7

Question

I am new in Laravel.

I have started to convert my core php project to laravel 7. I am stuck with some query issue.

Core Query is:

           if ($key == 'fuel_theft' || $key == 'soft_alert') {
                if ($user_id > 1 && $user_id != 35) {
                    $al_sql = "SELECT count(DISTINCT(unitid)) as counter, ts.site_id FROM `tickets` ts LEFT JOIN users_site us ON us.site_id = ts.site_id WHERE ts.alert_type = '" . $key . "' AND us.userid = '" . $user_id . "' AND ts.status = 1 ";
                } else if ($user_id > 1 && $user_id == 35) {
                    $al_sql = "SELECT count(DISTINCT(unitid)) as counter, ts.site_id FROM `tickets` ts LEFT JOIN users_site us ON us.site_id = ts.site_id WHERE ts.alert_type = 'low_drop' AND us.userid = '" . $user_id . "' AND ts.status = 1 ";
                } else {
                    $al_sql = "SELECT count(DISTINCT(unitid)) as counter, ts.site_id FROM `tickets` ts WHERE ts.alert_type = '" . $key . "' AND ts.status = 1 ";
                }
            } else {
                if ($user_id > 1) {
                    $al_sql = "SELECT count(DISTINCT(unitid)) as counter, ts.site_id FROM `tickets` ts LEFT JOIN users_site us ON us.site_id = ts.site_id WHERE ts.alert_type = '" . $key . "' AND us.userid = '" . $user_id . "' AND ts.status = 1";
                } else {
                    $al_sql = "SELECT count(DISTINCT(unitid)) as counter, ts.site_id FROM `tickets` ts LEFT JOIN users_site us ON us.site_id = ts.site_id WHERE ts.alert_type = '" . $key . "' AND ts.status = 1";
                }
            }

            if ($key == 'fuel_theft') {
                $al_sql .= " AND ts.event_date BETWEEN '" . $f_date . "' AND '" . date(Config::get('constants.DATE')) . "'";
            } else {
                $al_sql .= " AND ts.event_date = '" . date(Config::get('constants.DATE')) . "'";
            }

            $al_sql .= " AND ts.site_id > 0 GROUP BY ts.site_id";

            $al_res = DB::select($al_sql);

I have converted this into Laravel buider as well.

But I have issue with select columns.

$query->select('count(DISTINCT(unitid)) as counter', 'ts.site_id');

Please let me know where I am wrong.

my final query is

            $query = DB::table('tickets as ts');
            $query->select('count(DISTINCT(unitid)) as counter', 'ts.site_id');
            $query->leftJoin('users_site as us', 'us.site_id', '=', 'ts.site_id');

            if ($key == 'fuel_theft' || $key == 'soft_alert') {

                if ($user_id > 1 && $user_id != 35) {
                    $query->where('ts.alert_type', '=', $key);
                    $query->where('us.userid', '=', $user_id);
                } else if ($user_id > 1 && $user_id == 35) {
                    $query->where('ts.alert_type', '=', 'low_drop');
                    $query->where('us.userid', '=', $user_id);
                } else {
                    $query->where('ts.alert_type', '=', $key);
                }
            } else {

                if ($user_id > 1) {
                    $query->where('us.userid', '=', $user_id);
                }
            }

            if ($key == 'fuel_theft') {
                $query->whereBetween('ts.event_date', [$f_date, date(Config::get('constants.DATE'))]);
            } else {
                $query->where('ts.event_date', '=', date(Config::get('constants.DATE')));
            }

            $query->where('ts.site_id', '>', 0);
            $query->where('ts.status', '=', 1);
            $query->groupBy('ts.site_id');
            $al_res = $query->get();

error is : Column not found: 1054 Unknown column 'count(DISTINCT(unitid))' in 'field list'

Answer 1

You need to pass as raw query, instead of this line:

$query->select('count(DISTINCT(unitid)) as counter', 'ts.site_id');

Can you try this:

$query->select(DB::raw('count(DISTINCT(unitid)) as counter'), 'ts.site_id');