I have a table that is related by the id of the other. One contains the data of the document, the other contains the images related to the document. More precise than in this specific query, only the first row of the image table is returned.
Follow my query on Codeigniter:
function getConsulta($cidade,$classe,$busca,$perpage=0,$start=0,$one=false,$array='array'){
$this->db->select("{$classe}.*, cidades.nome as nome_cidade, categorias.nome as nome_categoria, {$classe}_imagens.imagem as imagem_imagem");
$this->db->from($classe);
$this->db->order_by('id','desc');
$this->db->join('cidades', "{$classe}.cidade_id = cidades.id", 'left');
$this->db->join('categorias', "{$classe}.categoria_id = categorias.id", 'left');
$this->db->join("{$classe}_imagens", "{$classe}.id = {$classe}_imagens.{$classe}_id", 'left');
$this->db->where('cidades.id', $cidade);
$this->db->limit($perpage,$start);
if($busca){
$this->db->like("{$classe}.nome",$busca)->or_like("categorias.nome",$busca);
}
$query = $this->db->get();
$result = !$one ? $query->result() : $query->row();
return $result;
}
Today the query works normally, but only when you have an image in the image table. The fact that occurs when you have more than one record in the relationship and that when returning multiple lines the foreach
interacts with each line repeating the result, but with a different image.