Geek asks and answers

CodeIgniter - MySQL join when there are no rows in the secound table

I have this query in the model:

  public function Games() {
    $q = $this->db->select('games.id, games.title, games.slug, games.dev_id, games.dev, games.plat_id, games.plat');
    $q = $this->db->from('games');
    $q = $this->db->join('rates', 'games.id = rates.game_id');
    $q = $this->db->select_avg('rates.rate');
    $q = $this->db->get();

    return $q->result();
}

My goal is to list everything from games, additionally getting the average ratefrom rateswhen it is available. Now it shows only those rows that are in both tables. How can i solve this?

+5
source share
1 answer

Use this command

$this->db->select('games.id, games.title, games.slug, games.dev_id, games.dev, games.plat_id, games.plat');
$this->db->select_avg('rates.rate');
$this->db->from('games');
$this->db->join('rates', 'games.id = rates.game_id','left');
$this->db->group_by('rates.game_id');
$q = $this->db->get();

A left join will bring several results. using avg and group by will restrict the selection of only one row for each record.

+5
source

More articles:


All Articles