CodeIgniter Forums
Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? (/showthread.php?tid=91398)



Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - pratibha.cts - 08-04-2024

Hi

I have used SHIELD Auth for first time in CI4, and I created APIs to register or login the users.
It's working fine.

But I checked the "auth_identities" table and I got multiple rows for one user. I just want to know is it fine have multiple rows in auth_identities table for one user or is it any issue. Please check screenshot.

https://prnt.sc/k1G9W0bFgS7S


This is creating issue while fetching users list, I am getting multiple records for one user. I have joined users table with auth_identities table to get the user email.
https://prnt.sc/QgEfitRqlmfR


If it's any issue, please help me how can fir this?
Thanks


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - kenjis - 08-05-2024

One user can have multiple auth identities. It is expected and no problem.
See https://shield.codeigniter.com/getting_started/concepts/#user-identities


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - pratibha.cts - 08-05-2024

(08-05-2024, 12:46 AM)kenjis Wrote: One user can have multiple auth identities. It is expected and no problem.
See https://shield.codeigniter.com/getting_started/concepts/#user-identities

Thanks for your response.. but it there any to resolve the duplicate rows, it will reduce the speed while fetching large  number of records.


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - kenjis - 08-05-2024

If you get the duplicate rows, it shows your SQL is something wrong.

If you just want to get user email, you can use UserModel.
Quote:While this has the potential to make the system more complex, the email and password fields are automatically looked up for you when attempting to access them from the User entity.



RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - pratibha.cts - 08-05-2024

(08-05-2024, 02:52 AM)kenjis Wrote: If you get the duplicate rows, it shows your SQL is something wrong.

If you just want to get user email, you can use UserModel.
Quote:While this has the potential to make the system more complex, the email and password fields are automatically looked up for you when attempting to access them from the User entity.


I need join user table with so many tables, I was not able to get all requuired information with "UserModel". Thats why I build below query.  Now I am concerned about duplicate rows. 

Code:
// Start building the main query
    $builder = $db->table('users');
    if (!$countOnly) {
        $builder->select('users.*,
                          user_roles.role_name as role_name,
                          languages.language as language,
                          domains.domain_name as domain_name,
                          domains.location as user_location,
                          auth_identities.secret as email,
                          us.status as subscription_status,
                          us.plan_period_start as plan_period_start,
                          us.plan_period_end as plan_period_end,
                          packages.title as package_name');
    } else {
        $builder->select('COUNT(*) as totalRecords');
    }

    $builder->join('auth_identities', 'auth_identities.user_id = users.id', 'INNER');
    $builder->join('user_roles', 'user_roles.id = users.role', 'INNER');
    $builder->join('languages', 'languages.id = users.lang', 'INNER');
    $builder->join('domains', 'domains.id = users.user_domain', 'INNER');
    $builder->join('(SELECT MAX(id) as max_id, user_id, status, package_id, plan_period_start, plan_period_end
                    FROM user_subscriptions
                    GROUP BY user_id) us', 'us.user_id = users.id', 'LEFT');
    $builder->join('packages', 'packages.id = us.package_id', 'LEFT');

    if (isset($whereClause) && count($whereClause) > 0) {
        foreach ($whereClause as $key => $value) {
            if ($key == 'email') {
                $builder->like("auth_identities.secret", $value);
            } elseif ($key == 'location') {
                $builder->where("domains.id", $value);
            } else {
                $builder->where("users.$key", $value);
            }
        }
    }

    // Handle meta query with multiple key-value pairs
    if (isset($metaQuery) && count($metaQuery) > 0) {
        foreach ($metaQuery as $meta) {
            $metaBuilder = $db->table('user_meta');
            $metaBuilder->select('user_id')
                        ->where('meta_key', $meta['meta_key']);

            $metaValue = is_array($meta['meta_value']) ? $meta['meta_value'] : (is_numeric($meta['meta_value']) ? (int)$meta['meta_value'] : $meta['meta_value']);

            switch ($meta['operator']) {
                case "=":
                    $metaBuilder->where('meta_value', $metaValue);
                    break;
                case ">=":
                    $metaBuilder->where('meta_value >=', $metaValue);
                    break;
                case "<=":
                    $metaBuilder->where('meta_value <=', $metaValue);
                    break;
                case "IN":
                    $metaBuilder->whereIn('meta_value', $metaValue);
                    break;
                default:
                    $metaBuilder->like('meta_value', $metaValue);
                    break;
            }
            $metaSubquery = $metaBuilder->getCompiledSelect();
            $builder->where("users.id IN ($metaSubquery)");
        }
    }

    // Add custom condition for user_domain
    //pr($whereClause);

    // Add custom condition for user_domain
    if (isset($whereClause['location'])) {
        $user_domain = $whereClause['location'];

        $builder->groupStart();
        $builder->where('users.user_domain', $user_domain);
        $builder->orGroupStart()
                ->whereIn('users.id', function($subQuery) use ($user_domain) {
                    return $subQuery->select('user_id')
                                    ->from('user_subscriptions')
                                    ->whereIn('package_id', function($subSubQuery) use ($user_domain) {
                                        return $subSubQuery->select('id')
                                                          ->from('packages')
                                                          ->where('domain_id', $user_domain);
                                    })
                                    ->where('CURRENT_DATE() <= plan_period_end');
                })
                ->groupEnd();
        $builder->groupEnd();
    }

    // Add sorting
    if (!empty($orderBy)) {
        $builder->orderBy($orderBy, $order);
    } else {
        $builder->orderBy('users.id', 'DESC');
    }

    // Add pagination
    if (!$noLimit) {
        $builder->limit($limit, $offset);
    }

    $query = $builder->get();
  


Please help me resolve this issue.
Thanks


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - kenjis - 08-05-2024

If you want to get auth identities with only email,  you need to add the condition for the join.

This is code in Shield:
https://github.com/codeigniter4/shield/blob/223fbd9954732fd9ff8599348b3296936508e843/src/Models/UserModel.php#L215


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - kenjis - 08-05-2024

By the way, when I see your screenshot, there are more than 70 identities in one user.
It is too many.


RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - pratibha.cts - 08-06-2024

(08-05-2024, 05:11 PM)kenjis Wrote: By the way, when I see your screenshot, there are more than 70 identities in one user.
It is too many.

Thanks for your suggestion, I fixed the issue by changing the join with "auth_identites" table.

Code:
$builder = $db->table('users');
    if(!$countOnly){
        $builder->select( 'users.*,
                            user_roles.role_name as role_name,
                            languages.language as language,
                            domains.domain_name as domain_name,
                            domains.location as user_location,
                            auth.secret as email,
                            us.status as subscription_status,
                            us.plan_period_start as plan_period_start,,
                            us.plan_period_end as plan_period_end,
                            packages.title as package_name,
                        ',);
    }  else {
        $builder->select( 'COUNT(*) as totalRecords' );
    }   

    $builder->join('(SELECT user_id, secret FROM auth_identities WHERE type = "email_password") auth ', 'auth.user_id = users.id', 'LEFT');  // updated this
    $builder->join('user_roles', 'user_roles.id = users.role', 'INNER');
    $builder->join('languages', 'languages.id = users.lang', 'INNER');
    $builder->join('domains', 'domains.id = users.user_domain', 'INNER');
    $builder->join('(SELECT MAX(id), user_id, status, package_id, plan_period_start, plan_period_end FROM user_subscriptions  GROUP BY user_id) us', 'us.user_id = users.id', 'LEFT');
    $builder->join('packages', 'packages.id = us.package_id', 'LEFT');

   
    if (isset($whereClause) && count($whereClause) > 0) {
        foreach ($whereClause as $key => $value) {
            if($key == 'email'){
                $builder->like("auth_identities.secret", $value);
            } if($key == 'location'){
                $builder->like("domains.id", $value);
            } else {
                $builder->where("users.$key", $value);
            }
        }
    }

    // Handle meta query with multiple key-value pairs
    if (isset($metaQuery) && count($metaQuery) > 0) {
        foreach ($metaQuery as $meta) {
            $metaBuilder = $db->table('user_meta');
            $metaBuilder->select('user_id')
                        ->where('meta_key', $meta['meta_key']);
           
            // check if meta value is array or simple data           
            if(is_array($meta['meta_value'])){
                $metaValue = $meta['meta_value'];
            } else {
                $metaValue = is_numeric($meta['meta_value']) ? (int)$meta['meta_value'] : $meta['meta_value'];
            }

            if ($meta['operator'] == "=") {
                $metaBuilder->where('meta_value', $metaValue);
            } else if($meta['operator'] == ">="){
                $metaBuilder->where('meta_value >=', $metaValue);
            } else if($meta['operator'] == "<="){
                $metaBuilder->where('meta_value <=', $metaValue);
            } else if($meta['operator'] == "IN"){
                $metaBuilder->whereIN('meta_value', $metaValue);
            } else {
                $metaBuilder->like('meta_value', $metaValue);
            }
            $metaSubquery = $metaBuilder->getCompiledSelect();
            $builder->where("users.id IN ($metaSubquery)");
        }
    }

    // Add Sorting

    if(!empty($orderBy)){
        $builder->orderBy($orderBy, $order);
    } else {
        $builder->orderBy('id', 'DESC');
    }

    // Add pagination
    if(!$noLimit){
        $builder->limit($limit, $offset);
    }

    $query = $builder->get();