CODEWITHSUNDEEP
laravel
Георги Няголов
Георги Няголов

Reputation: 43

Syntax error or access violation with SUM() in Query Builder table

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) ```

Upvotes: 0

Views: 114

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

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')

Upvotes: 1

Related Questions