+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Get Last Record In Each MySQL Group (/showthread.php?tid=78575)
Get Last Record In Each MySQL Group - viracoders - 02-09-2021
I need to this SQL query to get last record in each group:
PHP Code:
SELECT id, category_id, post_title
FROM posts
WHERE id IN (
SELECT MAX(id)
FROM posts
GROUP BY category_id
);
how do can I work with query builder to get last record in each group in codeigniter 4?
RE: Get Last Record In Each MySQL Group - alakian - 02-09-2021
Use Subqueries method. ie:
PHP Code:
$builder = $this->db->table('posts');
$builder->select('id, category_id, post_title');
$builder->whereIn('id', function(BaseBuilder $builder) {
return $builder->select('MAX(id)', false)->from('posts')->groupBy('category_id');
});
$query = $builder->get()->getResult();
return $query;
remember, you need to add in top of your model:
Code:
use CodeIgniter\Database\BaseBuilder;