Transform query from MySQL to Codeigniter

Question

Transform query from MySQL to Codeigniter

Navigation

#1 by (3 votes)

4

I have this query in MySQL:

select p.id_empleado as id,(select 'empleado') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
        e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado
from empleados p inner join usuarios u on p.id_usuario = u.id_usuario 
                 inner join empresas e on u.id_empresa = e.id_empresa
union
select p.id_alumno as id,(select 'estudiante') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
        e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado
from estudiantes p inner join usuarios u on p.id_alumno = u.id_usuario 
                 inner join empresas e on u.id_empresa = e.id_empresa

and transform it to a query with codeigniter style, for example:

$this->db->select('title, content, date');
$query = $this->db->get('mytable');

is equal to:

SELECT title, content, date FROM mytable

What I can think of is doing it with $this->db->query() , but in my case it would not work for me because I want to get the results in another function, for example:

funcion 1{
            $this->db->from("ejemplo");

}
funcion 2{
        $this->funcion1();        
        $query = $this->db->get();
        return $query->result();
}

The truth is that I do not know where to start, if helping me in something would be very helpful.

php codeigniter

asked by U.C 30.08.2018 в 18:50

source

1 answer

When starting Navigation Drawer enter with a fragment directly Locale returns to default

score 3 · Accepted Answer

ActiveRecord does not support UNION, so you should only write your query and use the method of ActiveRecord query.

It would stay like this:

$this->db->query("SELECT p.id_empleado as id,(select 'empleado') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
                    e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado
            from empleados p inner join usuarios u on p.id_usuario = u.id_usuario 
                             inner join empresas e on u.id_empresa = e.id_empresa
            UNION
            select p.id_alumno as id,(select 'estudiante') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
                    e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado
            from estudiantes p inner join usuarios u on p.id_alumno = u.id_usuario 
                             inner join empresas e on u.id_empresa = e.id_empresa");

Another alternative, in case you want to maintain the standard with ActiveRecord:

// #1 SubQueries no.1 -------------------------------------------
$this->db->select("p.id_empleado as id,(select 'empleado') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
                    e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado");
$this->db->from('empleados p');
$this->db->join('usuarios u', 'p.id_usuario = u.id_usuario', 'inner');
$this->db->join('empresas e', 'u.id_empresa = e.id_empresa', 'inner');
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();
$this->db->_reset_select();
// #2 SubQueries no.2 -------------------------------------------
$this->db->select("p.id_alumno as id,(select 'estudiante') as tipo, p.url_imagen, concat_ws(' ',p.nombre,p.paterno,p.materno) as nombre,
                e.nombre as empresa,concat_ws(' ',u.nombre,u.apellidos) as solicitante, p.fecha_alta, p.estado");
$this->db->from('estudiantes p');
$this->db->join('usuarios u', 'p.id_usuario = u.id_usuario', 'inner');
$this->db->join('empresas e', 'u.id_empresa = e.id_empresa', 'inner');
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();
$this->db->_reset_select();

// #3 Union manual --------------------------
$this->db->query("select * from ($subQuery1 UNION $subQuery2) as unionTable");

// #3 Alternativa con ActiveRecord ------------
$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();