如何在Laravel5.4和MySQL中运行该子查询

Question

我需要在Laravel5.4中运行这个子查询，我在StackOverflow上找到的答案是Laravel <5。

SELECT DISTINCT *
                    FROM (
                           SELECT DISTINCT
                             testing_results.pluginName,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '#', -1), '/', 1) AS access_vector,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 2), '/', -1) AS access_complexity,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 3), '/', -1) AS authentication,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 4), '/', -1) AS confidentiality,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 5), '/', -1) AS integrity,
                             SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 6), '/', -1) AS availability
                           FROM
                             testing_results
                           WHERE
                             testing_results.cvss_vector != ''
                         ) sub
                         WHERE sub.access_vector LIKE '%AV:L%'

非常感谢您的帮助。

诚挚的问候,

Answer 1

最简单的方法是：

$query = <<<SQL
SELECT DISTINCT *
                FROM (
                       SELECT DISTINCT
                         testing_results.pluginName,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '#', -1), '/', 1) AS access_vector,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 2), '/', -1) AS access_complexity,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 3), '/', -1) AS authentication,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 4), '/', -1) AS confidentiality,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 5), '/', -1) AS integrity,
                         SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 6), '/', -1) AS availability
                       FROM
                         testing_results
                       WHERE
                         testing_results.cvss_vector != ''
                     ) sub
                     WHERE sub.access_vector LIKE '%AV:L%'
SQL;

dd(DB::select($query));

Answer 2

//构建内部查询

$sub_query = DB::table('testing_results')
->select([DB::raw('DISTINCT testing_results.pluginName'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '#', -1), '/', 1) AS access_vector'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 2), '/', -1) AS access_complexity'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 3), '/', -1) AS authentication'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 4), '/', -1) AS confidentiality'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 5), '/', -1) AS integrity'),
DB::raw('SUBSTRING_INDEX(SUBSTRING_INDEX(cvss_vector, '/', 6), '/', -1) AS availability')
])
->where('testing_results.cvss_vector', '!=', '');

//创建外部查询

$query = DB::raw("({$sub_query->toSql()}) as sub_query");
$data = DB::table($query)
         ->mergeBindings($sub_query)
         ->where('access_vector', 'NOT LIKE', '%AV:L%')->get();

注:这适用于Laravel5.2，这意味着它将在Laravel5.4上工作。