How "NOT EXISTS" on Active Record?

[eluser]reset[/eluser]
Hello, I have to make this query on Active Record:

Code:

SELECT gl.idgrupolaboratorio, l.laboratorio, e.idenunciado, e.titulo
  FROM (`grupo_alumnos` ga)
       JOIN
       `grupo_laboratorio` gl ON ga.idgrupolaboratorio = gl.idgrupolaboratorio
       JOIN
       `enunciados` e ON gl.idgrupolaboratorio = e.idgrupolaboratorio
       JOIN
       `laboratorios` l ON gl.idlaboratorio = l.idlaboratorio
WHERE ga.idusuario = '10'
   AND gl.idcurso = '1'
   AND e.fechatope > now()
   AND NOT EXISTS
          (SELECT 1
             FROM practicas p
            WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado)
ORDER BY l.laboratorio, e.idenunciado

I do that, but it doesn't work by the NOT EXISTS clausule:

Code:

public function getEnunciadosGruposByUsuarioCurso($idUsuario,$idCurso){
    $this->db->select('gl.idGrupoLaboratorio, l.laboratorio, e.idEnunciado, e.titulo');
    $this->db->from($this->tablas['grupo_alumnos'].' ga');
    $this->db->join($this->tablas['grupo_laboratorio'].' gl','ga.idgrupolaboratorio = gl.idgrupolaboratorio');
    $this->db->join($this->tablas['enunciados'].' e','gl.idgrupolaboratorio = e.idgrupolaboratorio');
    $this->db->join($this->tablas['laboratorios'].' l','gl.idlaboratorio = l.idlaboratorio');
    $this->db->where('ga.idusuario',$idUsuario);
    $this->db->where('gl.idcurso',$idCurso);
    $this->db->where('e.fechatope > now()');
    $this->db->where('NOT EXISTS (SELECT 1 FROM '.$this->tablas['practicas'].' p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado)');
    $this->db->order_by('l.laboratorio, e.idenunciado');
    $result = $this->db->get();
    return $result->result_array();    
}

My browser show this error

Code:

An Error Was Encountered
Error Number: 1064

Algo est� equivocado en su sintax cerca 'EXISTS (SELECT 1 FROM practicas p WHERE p.idusuario = ga.idusuar' en la linea 9

SELECT gl.idGrupoLaboratorio, l.laboratorio, e.idEnunciado, e.titulo FROM (`grupo_alumnos` ga) JOIN `grupo_laboratorio` gl ON ga.idgrupolaboratorio = gl.idgrupolaboratorio JOIN `enunciados` e ON gl.idgrupolaboratorio = e.idgrupolaboratorio JOIN `laboratorios` l ON gl.idlaboratorio = l.idlaboratorio WHERE ga.idusuario = '10' AND gl.idcurso = '1' AND e.fechatope > now() AND `NOT` EXISTS (SELECT 1 FROM practicas p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado) ORDER BY l.laboratorio, e.idenunciado

How I can make this query?

Thanks all for reading

[eluser]reset[/eluser]
Hello,

I have solved this problem doing this:

Code:

$this->db->where('NOT EXISTS (SELECT 1 FROM '.$this->tablas['practicas'].' p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado) AND 0 =', '0');

I would like to read our opinions about this solution.

Thanks,

[eluser]Derek Allard[/eluser]
Interesting work around, very clever.

I think this is what you are looking for.

Code:

$this->db->where('NOT EXISTS (SELECT 1 FROM '.$this->tablas['practicas'].' p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado)', '', FALSE);

[eluser]reset[/eluser]
[quote author="Derek Allard" date="1211750783"]Interesting work around, very clever.

I think this is what you are looking for.

Code:

$this->db->where('NOT EXISTS (SELECT 1 FROM '.$this->tablas['practicas'].' p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado)', '', FALSE);

[/quote]

Thanks, but It doesn't work, I've the same error in other line:

Code:

Error Number: 1064

Algo est� equivocado en su sintax cerca ''' ORDER BY l.laboratorio, e.idenunciado' en la linea 9


SELECT gl.idGrupoLaboratorio, l.laboratorio, e.idEnunciado, e.titulo FROM (`grupo_alumnos` ga) JOIN `grupo_laboratorio` gl ON ga.idgrupolaboratorio = gl.idgrupolaboratorio JOIN `enunciados` e ON gl.idgrupolaboratorio = e.idgrupolaboratorio JOIN `laboratorios` l ON gl.idlaboratorio = l.idlaboratorio WHERE ga.idusuario = '10' AND gl.idcurso = '1' AND e.fechatope > now()

AND NOT EXISTS (SELECT 1 FROM practicas p WHERE p.idusuario = ga.idusuario AND p.idenunciado = e.idenunciado) '' ORDER BY l.laboratorio, e.idenunciado

How do you view the solution that I give up? Do you think it is correct?

[eluser]Derek Allard[/eluser]
Does it? Apologies, it was off the top of my head - should have tested first. If your way works, go with it. There are somethings that AR simply cannot handle as they're just too complex. We should though consider adding in a statement to allow authors to pass their own code in. Anyhow thanks for sharing your solution.

[eluser]reset[/eluser]
Thank you for responding Derek.

I think this happens because the second parameter of "where" ($this->db->where(first, second)) expected to be a constant, but I'm not sure I would have to revise the Active Record code, but now I have no time.

Thanks again

[eluser]Seppo[/eluser]
I tried with Derek's code and it worked as expected... which CI version are you running, reset?

[eluser]reset[/eluser]
Hi Seppo, I use 1.6.1.