CODEWITHSUNDEEP
mysqllaravel-5
Ben Koo
Ben Koo

Reputation: 89

Convert mySQL queries to Laravel

I'm new to laravel new and needed solution for database query. I have below raw query:

SELECT SUM(un.uni_duration) AS course_duration FROM xxcourse_components xx
INNER JOIN courses co ON co.cou_id = xx.cco_version
INNER JOIN modules mo ON mo.mod_id = xx.cco_component
INNER JOIN xxmodule_units xu ON xu.modu_module = mo.mod_id
INNER JOIN units un ON un.uni_id = xu.modu_unit
WHERE co.cou_id = "6"
AND un.deleted_at IS NULL;

I try to convert to laravel as query builder:

return DB::table('xxcourse_components as xx')
            ->select('SUM(un.uni_duration) as course_duration')
            ->join('courses as co', 'co.cou_id', '=', 'xx.cco_version')
            ->join('modules as mo', 'mo.mod_id', '=', 'xx.cco_component')
            ->join('xxmodule_units as xu', 'xu.modu_module', '=', 'mo.mod_id')
            ->join('units as un', 'un.uni_id', '=', 'xu.modu_unit')
            ->where('co.cou_id ', '=', $cou_id)
            ->whereNull('un.deleted_at')
            ->get();

However the result get me the error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SUM(un.uni_duration)' in 'field list' (SQL: select SUM(un.uni_duration) as course_duration from xxcourse_components as xx inner join courses as co on co.cou_id = xx.cco_version inner join modules as mo on mo.mod_id = xx.cco_component inner join xxmodule_units as xu on xu.modu_module = mo.mod_id inner join units as un on un.uni_id = xu.modu_unit where co.cou_id = 6 and un.deleted_at is null)

any clues to solve the error?

Upvotes: 0

Views: 74

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Use selectRaw:

return DB::table('xxcourse_components as xx')
    ->selectRaw('SUM(un.uni_duration) AS course_duration')
    ->join('courses as co', 'co.cou_id', '=', 'xx.cco_version')
    ->join('modules as mo', 'mo.mod_id', '=', 'xx.cco_component')
    ->join('xxmodule_units as xu', 'xu.modu_module', '=', 'mo.mod_id')
    ->join('units as un', 'un.uni_id', '=', 'xu.modu_unit')
    ->whereRaw('co.cou_id = ?', [$cou_id])
    ->whereNull('un.deleted_at')
    ->get();

Note: I also refactored your WHERE clause to use whereRaw with a statement parameter. Ideally you should be using a prepared statement to avoid SQL injection. Though, if you can verify that $cou_id only contains numbers, then the injection risk should be minimal.

Upvotes: 1

Ahmad Karlam
Ahmad Karlam

Reputation: 18

Use \DB::raw on your select, like this:

...
->select(\DB::raw('SUM(un.uni_duration) as course_duration'))
...

You can find the documentation here Laravel Raw Expressions

Upvotes: 0

Related Questions