Codeigniter how to avoid duplicates showing one to many relationship

Question

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

Current database

Screenshot

Current list (unordered list but duplicates persists)

try `$this->db->distinct($columnName);` where `$columnName` is from which column you want to distinct. — Touqeer Shafi, May 29 '17 at 09:08

Mohammad Hamedani · Answer 1 · 2017-05-29T19:50:55.207

3

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();
}

edited May 29 '17 at 19:50

answered May 29 '17 at 08:24

Mohammad Hamedani

3,304
3
10
22

I tried that and it didn't work. I've been trying changing distinct line (above and below) too ... – Jose May 29 '17 at 08:28
No erros, but the list keeps like screenshot above – Jose May 29 '17 at 09:37
Try adding primary key to select columns: `$this->db->select('your primary key', 'c.intervaloHorario','ci.cita');` – Mohammad Hamedani May 29 '17 at 09:43
it doesn't work (nothing has changed), i think distinct() function is not working fine or something else – Jose May 29 '17 at 09:49
I have changed database relationship from 1:n to 1:1 still not working – Jose May 29 '17 at 12:31
I simulate on my machine and it works! First please try `$this->db->select(array( 'c.intervaloHorario','ci.cita'));` and tell me if it worked. Then try to get raw SQL query by `$this->db->last_query()` after `$this->db->get()` function and share query. – Mohammad Hamedani May 29 '17 at 19:50
Sorry for being late! with your new query it's changing the order of results but duplicates persists. `$this->db->select(array( 'c.intervaloHorario','ci.cita'));` didn't work for me. But when i add `$this->db->last_query()` it's changing the order of results . I'll post it above. – Jose May 30 '17 at 17:39
`$this->db->last_query()` return last query running, so you should echo it. that means how codeigniter build sql query and `last_query` return pure sql query. Use `echo $this->db->last_query()` and share sql query. – Mohammad Hamedani May 30 '17 at 19:23
Hey! i got `echo $this->db->last_query()` works! it's throwing next query: `SELECT * FROM intervaloshorarios INNER JOIN citas ON intervaloshorarios.idIntervaloHorario = citas.idIntervaloHorario GROUP BY citas.cita, intervaloshorarios.intervaloHorario HAVING COUNT(*) >= 2` – Jose May 31 '17 at 10:52

score 1 · Answer 2 · answered May 29 '17 at 08:27

1

Use the keyword DISTINCT in your query

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

answered May 29 '17 at 08:27

Sampath Wijesinghe

789
1
11
33

Codeigniter how to avoid duplicates showing one to many relationship

2 Answers2