通过 Codeigniter 中的三个不同表获取数据的子查询是什么?

我正在传递User_name，因为我必须获取第三个表中的visit_id，因为我的方法有点混乱，任何人都可以告诉如何使用 Codeigniter 子查询来实现这一点？

  public function fetch_patient_bill($user_name) {
    $this->db->select('id');
    $this->db->from('user');
    $query = $this->db->where('user_name', $user_name);
    $result = $query->get();
    foreach ($result->result() as $user) {}
    $user_id = $user->id;
    $this->db->select('id');
    $this->db->from('patient_registration');
    $query = $this->db->where('user_id', $user_id);
    $result = $query->get();
    foreach ($result->result() as $registration) {}
    $registration_id = $registration->id;
    $this->db->select('id');
    $this->db->from('patient_visit');
    $query = $this->db->where('patient_id', $registration_id);
    $result = $query->get();
    foreach ($result->result() as $visit) {}
    $visit_id = $visit->id;   //i want to fetch this id
    }