将核心php查询转换为laravel 7

Question

我是新来的。

我已经开始将我的核心PHP项目转换为laravel 7。我被一些查询问题卡住了。

核心问题是：

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

我也把它改装成了拉威尔·布伊德。

但我对select列有异议。

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

请让我知道我错在哪里。

我最后的疑问是

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

错误是：未找到列：1054字段列表中的未知列'计数（DISTINCT（unitid））'

Answer 1

您需要作为原始查询传递，而不是此行：

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

你能试试这个吗：

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