phpmysqlcodeigniteraggregate-functionsquery-builder

CodeIgniter query involving row filtering based on aggregate data after GROUP BY

I have a mysql table with the following columns:

id_entry    
id_player   
id_achievement
claimed

and need to find all players that have claimed specific id_achievement (in my example 46, 53 and 63) and have claimed it (claimed = 1).

I've tried several things, the closest one being:

$this->db->select('id_player');
$this->db->from('user_achievements'); 
$this->db->where_in('id_achievement', ['53','46','63']);
$this->db->where('claimed', 1);

But it will return the id_player if any on the three achievement is complete (I need all of them). What would be the best way to do this? I am using Codeigniter's query builder pattern.

Solution

  • looks like you're running multiple commands independently. try something like this:

    select id_player, count(*) from
(select id_player, id_achievement
from user_achievements
where id_achievement in (53, 46, 63)
and claimed = 1
group by id_player, id_achievement) sbqry
group by id_player
having count(*) = 3;