Codeigniter, active record, join

0
votes

I am currently in the process of building a little game to learn myself some active record (codeigniter) and php. So I am fairly new to all of this.

And of course I've encountered the following challenge on the road.

The challenge is, I have two tables, User_Heroes and Heroes. The table Heroes contains all the heroes a user can choose during his time playing a game (that belong to his faction) and User_Heroes is the table of heroes that the user has already recruited into his service.

The thing I want to do is as follows, I want to make a query that returns a list of heroes that the current user doesn't have in his employ.

To simply get them all I would do this

public function get_faction_heroes($sUser, $hero_faction){
        $oQuery = $this -> db
                        -> select(' id,
                                    hero_name,
                                    hero_faction,
                                    hero_type,
                                    hero_cost,
                                    hero_maintenance,
                                    hero_allowed')
                        -> like('hero_faction',$hero_faction)
                        -> get('heroes')
                        -> result();

        return $oQuery;
    }

Currently I have the following Query build up, but obviously it is wrong as it does not return the list as I expect it to return.

 public function get_faction_heroes($sUser, $hero_faction){
    $oQuery = $this -> db
                    -> select(' h.id,
                                h.hero_name,
                                h.hero_faction,
                                h.hero_type,
                                h.hero_cost,
                                h.hero_maintenance,
                                h.hero_allowed')
                    -> like('h.hero_faction',$hero_faction)
                    -> where_not_in('u.hero_id', $sUser)
                    -> where('h.hero_allowed >', 1)
                    -> join('user_heroes u', 'u.hero_id = h.id', 'left')
                    -> get('heroes h')
                    -> result();

    return $oQuery;
}

As you might notice, some heroes are allowed to be recruited multiple times (hero_allowed > 1).

Perhaps to further clear up the table I've added two images with the current tables

enter image description here

enter image description here

1
phpcodeigniterjoinactiverecordleft-join
i think you should use right join (not left) - Mahdi Youseftabar
Already tried that, sadly it's not the sollution :( - Geert Kamps
use $this->db->last_query(); to getting last query fired after that you have to play with query in phpmyadmin or similar mysql application to get desired problem. Actually your problem is not releted to codeignter it's related to query. - siddhesh
For getting better solutions You can make sql fiddle which simulates the situation. - siddhesh
I know it's not codeigniter, I've just hit a wall in how to properly construct this query (be it in Active Record or just plain MYSQL) - Geert Kamps

1 Answers

1
votes
$this->db->select('*')->from('Heroes');
$this->db->where('`id` NOT IN (SELECT `hero_id` FROM `User_Heroes`)', NULL, FALSE);
$query=$this->db->get();
return $query->result();