CODEWITHSUNDEEP
phpmysqlcodeignitergrocery-crud
Jose
Jose

Reputation: 87

Codeigniter how to avoid duplicates showing one to many relationship

I have been trying to show rows without duplicates but the query isn't working properly. I think the problem is one to many relationship, because one 'intervaloHorario' has many 'citas'. So, for example, i want to show only: 'From 8:00 to 15:00 (this is an intervaloHorario)' to date (cita) '27/08/1988'. What should i do?

Controller

$this->Fechacita_Model->delete_duplicaterow();

Model

 public function delete_duplicaterow() {      
  $this->db->select('
            c.intervaloHorario','ci.cita'

        );       

  $this->db->from('intervaloshorarios c');
  $this->db->join('citas ci', 'ci.idCitas = c.idIntervaloHorario','left');
  $this->db->group_by('c.idIntervaloHorario','ci.cita');
  $query =  $this->db->get();  
    return $query->num_rows();
}

Model(EDIT)

$this->db->select(array('c.intervaloHorario', 'ci.cita'));
        $this->db->distinct();
        $this->db->from('intervaloshorarios c');
        $this->db->join('citas ci', 'ci.idCitas = c.idIntervaloHorario', 'left');
        $this->db->group_by('c.idIntervaloHorario', 'ci.cita');

        $query = $this->db->get();
        $this->db->last_query();
        return $query->num_rows();

Database

Database

Current database

Current database

Screenshot

Duplicate

Current list (unordered list but duplicates persists)

Dup persists

Upvotes: 4

Views: 4893

Answers (2)

Mohammad Hamedani
Mohammad Hamedani

Reputation: 3354

You can use $this->db->distinct() and add selecting primary key to remove duplicate:

public function delete_duplicaterow() {      
  $this->db->select(array('c.intervaloHorario', 'ci.cita'));       
  $this->db->distinct();
  $this->db->from('intervaloshorarios c');
  $this->db->join('citas ci', 'ci.idCitas = c.idIntervaloHorario','left');
  $this->db->group_by('c.idIntervaloHorario','ci.cita');
  $query =  $this->db->get();  

  return $query->num_rows();
}

Upvotes: 3

Sampath Wijesinghe
Sampath Wijesinghe

Reputation: 690

Use the keyword DISTINCT in your query

reference : https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

Upvotes: 1

Related Questions