Codeigniter：分组并从连接表中获取最新记录

Question

$this -> db -> select('users.*');
$this -> db -> select('timerecord.*');
$this -> db -> from('users');
$this -> db -> join('timerecord', 'users.id = timerecord.id');
//$this -> db -> where(array('users.id'=>$user_id), NULL, FALSE);
$this -> db -> where('users.status', 'Verified');
//$this -> db -> where('users.id', 'timerecord.id');
$this -> db -> order_by('timerecord.timeId', 'DESC');
$this -> db -> group_by('timerecord.id, users.id');

i want to get records from both users and timerecord table but timerecord shows multiple rows because of duplicated id, and now i used group_by to limit the row in timerecord however the record is not showing the latest record i also used orderby but no luck, please someone help me, thanks!

Answer 1

can you try this?

$this -> db -> select('users.*');
$this -> db -> select('timerecord.*');
$this -> db -> from('users');
$this -> db -> join('timerecord', 'users.id = timerecord.timerecord.user_id');
//USE THE NAME OF THE ROW WHERE USER'S IDs ARE INSERTED ON YOUR TIMERECORD TABLE,
// THAT'S HOW YOU JOIN TABLE, RIGHT? 
// I ASSUME THAT YOU ARE JOINING USER'S ID AND TIMERECORD'S ID ON YOUR PREVIOUS POST //
//$this -> db -> where(array('users.id'=>$user_id), NULL, FALSE);
$this -> db -> where('users.status', 'Verified');
//$this -> db -> where('users.id', 'timerecord.id');
$this -> db -> order_by('timerecord.timeId', 'DESC');
$this -> db -> group_by('timerecord.id, users.id');