Lucas Borges Lucas Borges - 3 months ago 6
SQL Question

How to make an SQL query with codeigniter framework

How to make this query with codeigniter?

SELECT `nome_paciente`
FROM `cronograma_preventivo`
JOIN `paciente`
ON `cronograma_preventivo`.`id_paciente` = `paciente`.`id_paciente`
WHERE `mes` = '2016-09-01'
AND `kit_higiene` + `raspagem` + `atendimento` + "aplicacao_fluor" + "evidenciacao_placa" + "polimento_protese" != 0
GROUP BY `nome_paciente`


I tried this:

$this->db->join('paciente', 'cronograma_preventivo.id_paciente = paciente.id_paciente');
$this->db->select('nome_paciente');
$this->db->where('mes', date('Y-m-01',strtotime("NOW")));
$this->db->where("(kit_higiene+raspagem+atendimento+aplicacao_fluor+evidenciacao_placa+polimento_protese)>=0");
$this->db->group_by('nome_paciente');


But this doesn't work :(

Answer

Seems to me like a line like this is missing from the query builder code:

 $this->db->from('cronograma_preventivo')

And it seems to me like the

 $this->db->select()

should come first, before the db->from, which should be before the db->join.

I'm just guessing. I don't do codeigniter Query Builder. I'm not familiar with the requirements for ordering the methods. I'm much more comfortable crafting SQL that needs to be submitted to the database. And I much prefer to qualify all column references, for one, as a benefit future readers who may not be as familiar with the database schema, which columns come from which tables, and two, to prevent my SQL from breaking with an "ambiguous column" reference when a column with the same name is added to another table.

  SELECT p.nome_paciente
    FROM cronograma_preventivo r
    JOIN paciente p
      ON p.id_paciente = r.id_paciente
   WHERE r.mes = '2016-09-01'
     AND ( r.kit_higiene 
         + r.raspagem
         + r.atendimento
         + r.aplicacao_fluor
         + r.evidenciacao_placa
         + r.polimento_protese
         ) != 0
   GROUP BY p.nome_paciente

And if I had to convert that to CodeIgniter QueryBuilder, I would try something like this:

  $this->db->select('p.nome_paciente');
  $this->db->from('cronograma_preventivo r');
  $this->db->join('paciente p','p.id_paciente = r.id_paciente');
  $this->db->where('r.mes','2016-09-01');
  $this->db->where('( r.kit_higiene
         + r.raspagem
         + r.atendimento
         + r.aplicacao_fluor
         + r.evidenciacao_placa
         + r.polimento_protese
         ) != 0');
  $this->db->group_by('p.nome_paciente');

And if I observed that this did "not work", and I posted a question on StackOverflow, I would include some information about the actual behavior I was observing, especially any error messages that are returned.

If I was getting an unexpected resultset, then I would take a look at the SQL statement that CodeIgniter was generating, and compare it to the query that I want constructed, and go from there.