CODEWITHSUNDEEP
phpmysqllaravelsubquery
SecretCoder
SecretCoder

Reputation: 45

How to run this subquery in Laravel 5.4 and MySQL

I need to run this subquery in Laravel 5.4 and the answers that I have found on StackOverflow are for 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%'

Thank you very in advanced for your help.

Best regards,

Upvotes: 1

Views: 232

Answers (2)

Manjunath Muniraju
Manjunath Muniraju

Reputation: 44

//Building Inner Query

$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', '!=', '');

//Creating Outer Query

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

Note : This works on Laravel 5.2, which means it will work on Laravel 5.4.

Upvotes: 0

kopaty4
kopaty4

Reputation: 2296

Simplest way to do this is:

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

Upvotes: 2

Related Questions