mercredi 2 octobre 2019

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?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire