Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have this query in 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 listing everything from games, additionally getting average rate from rates when available. Now it only shows those rows which are in both tables. How can I solve this?

like image 995
Tomek Buszewski Avatar asked Jun 28 '12 13:06

Tomek Buszewski


1 Answers

Use this instruction

$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();

Left join will bring multiple results. using avg and group by will restrict to fetch only one row against each record.

like image 99
Muhammad Raheel Avatar answered Sep 22 '22 14:09

Muhammad Raheel