Codeigniter how to avoid duplicates showing relationship "one"
I am trying to show rows without duplicates, but the query is not working correctly. I think the problem is one to many relationships, because one "lahorario" has a lot of "cit". So, for example, I only want to show: "From 8:00 to 15:00 (this is the oHorario interval)" for today (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
Current database
Screenshot
Current list (unordered list, but duplicates are kept)
source to share
You can use $this->db->distinct()
and add a selection of the primary key to remove the 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();
}
source to share
Use a keyword DISTINCT
in your request
link: https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html
source to share