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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With