How to make this query with codeigniter?
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`
$this->db->join('paciente', 'cronograma_preventivo.id_paciente = paciente.id_paciente');
Seems to me like a line like this is missing from the query builder code:
And it seems to me like the
should come first, before the
db->from, which should be before the
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.