Welcome Guest, Not a member yet? Register   Sign In
Is it fine to have multiple rows in auth_identities table in SHIELD CI4?
#8

(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();
Reply


Messages In This Thread
RE: Is it fine to have multiple rows in auth_identities table in SHIELD CI4? - by pratibha.cts - 08-06-2024, 01:10 AM



Theme © iAndrew 2016 - Forum software by © MyBB