Welcome Guest, Not a member yet? Register   Sign In
join of users and users_groups as 1 row
#1

Hello,
Using CodeIgniter 3.0.3 I make join of users and users_groups tables with command
PHP Code:
    $this->db->join($this->ion_auth_config_tables['users_groups'], $this->ion_auth_config_tables['users_groups'] . '.user_id = ' $this->ion_auth_config_tables['users'] . '.id''left'); 

and I get sql like

Code:
SELECT bp_users.*
FROM `bp_users`
LEFT JOIN `bp_users_groups` ON `bp_users_groups`.`user_id` = `bp_users`.`id`



as any user can be member of several groups in result set for any such user I get several rows.
I would like for any user to get 1 row with subarray of his groups.
Sure, I can make such grouping manually. But can I to make such using CodeIgniter db/sql methods ?

  Thanks!
Reply
#2

You will need to do a GROUP_CONCAT but Codeigniter active record has no GROUP_CONCAT method.

The data returned should be a string. You will need to do explode() so that it becomes an array.

GROUP CONCAT http://stackoverflow.com/questions/27692...-one-field
Manually write SQL query in active recordhttp://stackoverflow.com/questions/17167573/concat-in-codeigniter-active-record
Reply
#3

There is a function in Ion Auth called get_users_groups(). It uses the following query:

PHP Code:
$this->db->select($this->tables['users_groups'].'.'.$this->join['groups'].' as id, '.$this->tables['groups'].'.name, '.$this->tables['groups'].'.description')
 
               ->where($this->tables['users_groups'].'.'.$this->join['users'], $id)
 
               ->join($this->tables['groups'], $this->tables['users_groups'].'.'.$this->join['groups'].'='.$this->tables['groups'].'.id')
 
               ->get($this->tables['users_groups']); 

You could tweak this query to suite your needs or you could just use the build in function itself. When your just interested in the groups of a single user I would go with this option. If you need to know this for alot of users at the same time, than please take your time to adjust the query because using the get_users_groups function an extra query needs to run for every row in your first query.

PHP Code:
$user_groups $this->ion_auth->get_users_groups($user->id)->result(); 
Reply
#4

Recently had the same need and got it done this way

Code:
$sql = "id, username, first_name, last_name, From_UnixTime(last_login, '%Y %d-%b  %r') last_login, active, Group_Concat(groups.name ORDER BY groups.name SEPARATOR ', ') as belongs_to";

$query = $this->db->select($sql)
    ->from('members')
   ->join('users_groups', 'users_groups.user_id = members.id', 'inner')
   ->join('groups', 'users_groups.group_id = groups.id', 'inner')
   ->get();
Reply
#5

Thanks!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB