multiple joins and group by in codeigniter

I’m using Codeigniter, what I want to do is a query that returns users by groups.

A group can have one or more users, here examples of the tables:

groups table        users table              users_by_groups table
    id                  id                    id_group   id_user
    1                   10                      1          10
    2                   11                      1          11
    3                   12                      1          12
                                                2          11
                                                3          12

the query I made:

public function getUsersByGroup(){
        $this->db->select('g.vgro_name,u.vusr_user');
        $this->db->from('vts_user_by_group b');
        $this->db->join('vts_groups g', 'b.vubg_vgro_id = g.vgro_id');
        $this->db->join('vts_users u', 'b.vubg_vusr_id = u.vusr_id');
        $this->db->group_by('g.vgro_name,vusr_user');
        // $this->db->order_by('u.vusr_user','asc');
        $query = $this->db->get();
        return $query->result();
    }

what this is returning:

      0 => 
object(stdClass)[28]
  public 'vgro_name' => string 'GROUP1' (length=4)
  public 'vusr_user' => string 'john' (length=9)
1 => 
object(stdClass)[29]
  public 'vgro_name' => string 'GROUP1' (length=4)
  public 'vusr_user' => string 'alice' (length=4)
2 => 
object(stdClass)[30]
  public 'vgro_name' => string 'GROUP2' (length=3)
  public 'vusr_user' => string 'mark' (length=3)

which is not what I want. What I pretend is for example:

 0 => 
object(stdClass)[31]
  public 'vgro_name' => string 'GROUP1' (length=7)
  public 'vusr_user' => string 'john' (length=7)
  public 'vusr_user' => string 'alice' (length=7)

    2 => 
object(stdClass)[30]
  public 'vgro_name' => string 'GROUP2' (length=3)
  public 'vusr_user' => string 'mark' (length=3)

What am I doing wrong?


Source: sql

1 Comment

  1. Digin Dominic

    You can do the ‘grouping’ in PHP:

    public function getUsersByGroup(){
       $this->db->select('g.vgro_name,u.vusr_user');
       $this->db->from('vts_user_by_group b');
       $this->db->join('vts_groups g', 'b.vubg_vgro_id = g.vgro_id');
       $this->db->join('vts_users u', 'b.vubg_vusr_id = u.vusr_id');
       $query = $this->db->get();

       $resultsByGroup = array();

       foreach($query->result_array() as $row) {
         $resultsByGroup[$row['vgro_name']][] = $row['vusr_user'];
       }

       return $resultsByGroup;
    }

    Reply

Leave a Reply