Ad

Syntax Error Or Access Violation With SUM() In Query Builder Table

- 1 answer

I am trying to exchange db::select() with db::table() so I can use pagination but since I have in my query sum() and am getting an error.

My Quest: How to use sum() and order results with Query Builder::table?

My old but working attempt

$query = 'SELECT SUM(votes.votes) AS c, sites.id, sites.user_id, sites.url, sites.type_id, sites.img_src, sites.details, sites.created_at, sites.updated_at
          FROM sites 
          JOIN votes 
          WHERE sites.id = votes.site_id
          GROUP BY sites.id, sites.user_id, sites.url ,sites.type_id, sites.img_src, sites.details, sites.created_at, sites.updated_at 
          ORDER BY c DESC LIMIT 10');

My new attempt

$test = DB::table('sites')
        ->join('votes', 'site.id', '=', 'votes.site_id')
        ->select(\DB::raw('SUM(votes.votes) AS c'), 'sites.id', 'sites.user_id', 'sites.url', 'sites.type_id', 'sites.img_src', 'sites.details', 'sites.created_at', 'sites.updated_at')
        ->where('sites.id ', '=', 'votes.site_id')
        ->groupBy('sites.id, sites.user_id, sites.url ,sites.type_id, sites.img_src, sites.details, sites.created_at, sites.updated_at')
        ->orderBy('c', 'DESC')
        ->get();

Error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`url ,sites`.`type_id, sites`.`img_src, sites`.`details, sites`.`created_at, si' at line 1 (SQL: select SUM(votes.votes) AS c, `sites`.`id`, `sites`.`user_id`, `sites`.`url`, `sites`.`type_id`, `sites`.`img_src`, `sites`.`details`, `sites`.`created_at`, `sites`.`updated_at` from `sites` inner join `votes` on `site`.`id` = `votes`.`site_id` group by `sites`.`id, sites`.`user_id, sites`.`url ,sites`.`type_id, sites`.`img_src, sites`.`details, sites`.`created_at, sites`.`updated_at` order by `c` desc) ```
Ad

Answer

Group by multiple fields

You need to split the string in groupBy method, or Laravel will take it as one field:

->groupBy('sites.id', 'sites.user_id', 'sites.url' ,'sites.type_id', 'sites.img_src', 'sites.details', 'sites.created_at', 'sites.updated_at')

Compare two columns by whereColumn

->where('sites.id ', '=', 'votes.site_id')

will compare the column sites.id with string "votes.site_id", you need to use whereColumn to compare two columns:

->whereColumn('sites.id ', '=', 'votes.site_id')
Ad
source: stackoverflow.com
Ad