用where子句在其他表中的列的总和排序

Question

I am using codeigniter and I want to list campaigns order_by how many donations each has received. So we are dealing with two tables, "campaigns" and "donations".

I used this

$this->db->select('campaigns.id as id, campaigns.userId as userId, name, amount');
$this->db->join('donations','donations.campaignId = campaigns.id', 'left');
$this->db->order_by("SUM(amount)", "DESC");

I want it to get all campaigns whether they have any donations or not and order by sum of the donations they have received. This query is getting just one campaign. Ideas?

Answer 1

You need a group by campaigns.id and to change the select to have sum(amount) as amount.

This is a SQL problem. I'm not intimately familiar with code-igniter so I won't attempt to write the code.