How to join two selects of different tables?

Question

I have the following tables:

+--------- +      +-------------+
| usuario  |      |usuario_grupo|     +----------+
+--------- +      +-------------+     | grupo    |
|usuario_id|----->| membro_id   |     +----------+
| nome     |      | grupo_id    |<----| grupo_id |
| ....     |      +-------------+     | nome     | 
| ...      |                          +----------+                          
+----------+

I would like to do a query in which I obtained using a foreach, for example the following:

All groups that a particular user is in, and all members that belong to the group in question.

At first I can get the groups (I’m using codeigniter).:

$q = $this->db->select('grupo_id')->from('usuario_grupo')->where('membro_id', $membro_id)->get()->result_array();
foreach ($q as $v) {
       $result[] = $this->db->select()->from('grupo')->where_in('grupo_id', $v)->get()->row_array();
} 

Result in Json, for example:

[
    {
      "grupo_id": "1",
      "nome": "São Paulo",
    }
    {
      "grupo_id": "9",
      "nome": "Rio de Janeiro",
    }
]

However, I would also like to obtain the members of each obtained group:

[
    {
      "grupo_id": "1",
      "nome": "São Paulo",
      "membros": 
                {
                  "membro_id": "21",
                  "membro_id": "39"
                }
    }
    {
      "grupo_id": "9",
      "nome": "Rio de Janeiro",
      "membros": 
               {
                 "membro_id": "4",
                 "membro_id": "9"
               }
    }
]

Answer 1

The most correct way in the codeigniter of you to do this, I think, is that it’s the way I’m going to expose it next, using the Join and mounting a subarray() for the expected results:

public function get_usuarios(){
    $consulta = $this->db->get('usuario')->result();
    foreach($consulta as &$valor){
        $this->db->select('usuario_grupo.*, grupo.nome as nome_grupo');
        $this->db->where('id_usuario', $valor->id_usuario); // neste caso é o id do usuário
        $this->db->join("grupo", "grupo.grupo_id=usuario_grupo.grupo_id"); // neste caso, montamos o join para buscar o nome do grupo
        $valor->grupos = $this->db->get('usuario_grupo')->result();
    }

    return $consulta;
}

In this we will get the complete listing of users and their respective groups in each of them, below them.

To start the search, you can do:

$retorno = $this->nome_model->get_usuarios();

foreach($retorno->grupos as $valor){    
    echo "Este usuário ".$valor->nome_usuario." pertence ao grupo ".$valor->nome_grupo;
}