Counting Rows Includes JSON by Id in Codeigniter

1. We have one groups table name as “groups” (There is mail groups)
2. We have one person table name as “person” (There is persons who has in mail groups)

We saved person in the “person” table with json_encode by group ID. Because a person can belong to more than one group.

The problem is we can not get count rows of “person” table by groups. We want to get some information like X Mail Group Has 10 People, Y Mail Group Has 5 People, etc. We should get this information with id by counting the json encoded sql rows. But how? We need help with these issues.

Our Code;

$jsonGroups = $this->db->get("person")->result();

foreach ($jsonGroups as $jsGro) {

    $jsGro->personEmailGroup = json_decode($jsGro->personEmailGroup);

    if(in_array($group->mailGroupId, $jsGro->personEmailGroup)) {

        $this->db->select('*');
        $query = $this->db->where('personId', $jsGro->personId)->where('personStatus', 1)->get('person');
        $num = $query->num_rows();
        if($num>0) {
            echo ' ' .$num;
        } else {

        }
    }
}

With the above code, the result is nothing.

Thanks in advance!

Answer

Using this kind of design with json for relations is not optimal but is your choice.

Assuming your sql server has support for JSON functions and you are using codeigniter 3, here is a sample way to get the number of members using the JSON_CONTAINS function

$sub_query = $this->db
                ->select('groups.groupsId,COUNT(*) AS NumberOfPerson')
                ->join('person',"JSON_CONTAINS(person.personEmailGroup, CONCAT('"',groups.groupsId,'"'), '$')")
                ->group_by('groupsId')
                ->from('groups')
                ->get_compiled_select();

$allGroups = $this->db
                    ->select('groups.*, personcounter.NumberOfPerson')
                    ->join("($sub_query) personcounter", "personcounter.groupsId= groups.groupsId","left")
                    ->from('groups')
                    ->get()
                    ->result();
echo '<pre>';
print_r($allGroups );
echo '</pre>';

I recommand to use pagination insead to get all groups.

Another option, without JSON_CONTAINS function is to check the group membership of each user and count them. For big person table use query with limit and offset till all table is scanned – please take a look on codeigniter documentation Limiting Results

// this are all users - not recomended due to memory leak
$users = $this->db->get("person")->result();
// array with group_id as key and group members as value
// will contains only groups with members
$groups_counter = array();
foreach ($users as $user) {
    $user_groups = json_decode($user->personEmailGroup);
    foreach($user_groups as $groupId){
        if(!isset($groups_counter [$groupId])){
            $groups_counter [$groupId] = 1;
        } else {
            $groups_counter[$groupId] += 1;
        }
    }
}

Best regards