Innerjoin no Codeigniter

Question

I’m working on a code and I need to make an association, where I have to count the amount of voting in a video. The problem is that there are 2 tables a videos and voting . In videos is the registered videos, and there is the id video and link. In votacoes has the id_video and who voted. I am using Codeigniter to query and list the videos:

Controller:

class Promocao extends CI_Controller {
    public function __construct() {
        parent::__construct();
        $this->load->model('miss_model', 'miss');
    }


    public function votacao($id = NULL) {

/* Pega os vídeos */
        $data['videos'] = $this->miss->find_all('id', 'desc');
        $data['titulo'] = 'Votação - TOP CLASS VIP';
        $this->load->view('votacao', $data);

    }
}

View:

<?php if (count($videos)):foreach ($videos as $row):?>
    <div class="col-md-4">    
        <video width="240" height="240" controls>
            <source src="<?php echo base_url('assets/videos/miss/' . $row['video'])?>" type="video/mp4">
            <source src="<?php echo base_url('assets/videos/miss/' . $row['video'])?>" type="video/ogg">
            Your browser does not support the video tag.
        </video>

        <?php echo anchor('promocao/votacao/' . $row['id'], 'VOTAR', 'class="btn btn-success"');?>
    </div>
<?php endforeach;?>
<?php else:?>
    <h1 class="text-center">SEM VÍDEOS</h1>
<?php endif;?>

In the view I need to put like this 30 votes for example, i.e., display the amount of votes obtained from each video. The problem is that I don’t know how to relate the two tables by id of the video and id_video from the other table.

Answer 1

Well, to do Join in the IC is like this: model that inherits the IC model

public function listaVideos()
{
   return $this->db->select("videos.*, votacoes.voto")
               ->join("votacoes", "votacoes.id_video=videos.id")
               ->get("videos")->result();
}

is more or less that
a good reference is also the documentation itself: https://ellislab.com/codeigniter/user-guide/database/active_record.html#select