codeigniter：连接中第二个表的单行

Question

I have two tables : user and payment.

The user table have 3 records and each has 2 payment records in payment table. I want the last payment details of the users, but instead of getting 3 rows, I get only 1 row.

$this->db->select('user.*, max(payment.create_at) as last_payment_at');
$this->db->from('user');
$this->db->join('payment','payment.user_id=user.id', 'left');
$this->db->order_by("user.id", "desc"); 
return $this->db->get()->result_object();

Answer 1

$this->db->select('user.*, max(payment.create_at) as last_payment_at'); // you do not have to use max(payment.create_at) as last_payment_at if you are using desc and limit 1
$this->db->from('user');
$this->db->join('payment','payment.user_id=user.id', 'left');
$this->db->order_by("user.id", "desc"); // you can omit this line if you are getting max value

$this->db->limit(1);  // <======= add limit for 1

return $this->db->get()->result_object();