CodeIgniter Forums
How to get the rowCount with results returned by query while joining tables - 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: How to get the rowCount with results returned by query while joining tables (/showthread.php?tid=91570)

How to get the rowCount with results returned by query while joining tables - pratibha.cts - 08-31-2024

Hi

I am using CI4 with SHIELD.
I have created a function to get the list of users with multiple dynamic filters and also joined with many tables with pagination, It's working fine.
I want to get the count of total number of results returned by the query. I have tried many codes to achieve this, because of  using group by clause, I was getting issues. 
Finally I achieve what I needed, But I am not sure if it is correct way or not. currently it's only 50 records but it's taking much time to load. Please suggest if there is any better way.
Here is my code
PHP Code:
function getPlayers($whereClause = [], $metaQuery = [], $search = '', $orderBy = '', $order = '', $limit = 10, $offset = 0, $noLimit = false) {
    $db = \Config\Database::connect();

    $flagPath =  base_url() . 'uploads/logos/';

    // Start building the main query
    $builder = $db->table('users');
    $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,
                        packages.title as package_name,
                        pm.last4 as last4,
                        pm.brand as brand,
                        pm.exp_month as exp_month,
                        pm.exp_year as exp_year,
                        us.status as subscription_status,
                        us.plan_period_start as plan_period_start,
                        us.plan_period_end as plan_period_end,
                        us.plan_interval as plan_interval,
                        us.coupon_used as coupon_used,
                        CASE
                            WHEN TIMESTAMPDIFF(SECOND, `users`.`last_active`, NOW()) < 60 THEN 
                                CONCAT(TIMESTAMPDIFF(SECOND, `users`.`last_active`, NOW()), " secs ago")
                            WHEN TIMESTAMPDIFF(MINUTE, `users`.`last_active`, NOW()) < 60 THEN 
                                CONCAT(TIMESTAMPDIFF(MINUTE, `users`.`last_active`, NOW()), " mins ago")
                            WHEN TIMESTAMPDIFF(HOUR, `users`.`last_active`, NOW()) < 24 THEN 
                                CONCAT(TIMESTAMPDIFF(HOUR, `users`.`last_active`, NOW()), " hours ago")
                            WHEN TIMESTAMPDIFF(DAY, `users`.`last_active`, NOW()) < 30 THEN 
                                CONCAT(TIMESTAMPDIFF(DAY, `users`.`last_active`, NOW()), " days ago")
                            WHEN TIMESTAMPDIFF(MONTH, `users`.`last_active`, NOW()) < 12 THEN 
                                CONCAT(TIMESTAMPDIFF(MONTH, `users`.`last_active`, NOW()), " months ago")
                            ELSE 
                                CONCAT(TIMESTAMPDIFF(YEAR, `users`.`last_active`, NOW()), " years ago")
                        END AS last_activity_time,

                        CASE
                            WHEN TIMESTAMPDIFF(SECOND, `users`.`created_at`, NOW()) < 60 THEN 
                                CONCAT(TIMESTAMPDIFF(SECOND, `users`.`created_at`, NOW()), " secs ago")
                            WHEN TIMESTAMPDIFF(MINUTE, `users`.`created_at`, NOW()) < 60 THEN 
                                CONCAT(TIMESTAMPDIFF(MINUTE, `users`.`created_at`, NOW()), " mins ago")
                            WHEN TIMESTAMPDIFF(HOUR, `users`.`created_at`, NOW()) < 24 THEN 
                                CONCAT(TIMESTAMPDIFF(HOUR, `users`.`created_at`, NOW()), " hours ago")
                            WHEN TIMESTAMPDIFF(DAY, `users`.`created_at`, NOW()) < 30 THEN 
                                CONCAT(TIMESTAMPDIFF(DAY, `users`.`created_at`, NOW()), " days ago")
                            WHEN TIMESTAMPDIFF(MONTH, `users`.`created_at`, NOW()) < 12 THEN 
                                CONCAT(TIMESTAMPDIFF(MONTH, `users`.`created_at`, NOW()), " months ago")
                            ELSE 
                                CONCAT(TIMESTAMPDIFF(YEAR, `users`.`created_at`, NOW()), " years ago")
                        END AS registration_time,

                        JSON_ARRAYAGG(
                            JSON_OBJECT(
                                "country_name", c.country_name, 
                                "flag_path", CONCAT("'.$flagPath.'", c.country_flag)
                            )
                        ) AS user_nationalities
                    ');

    

    $builder->join('(SELECT user_id, secret FROM auth_identities WHERE type = "email_password") auth', 'auth.user_id = users.id', 'LEFT');
    $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('user_nationalities un', 'un.user_id = users.id', 'LEFT');
    $builder->join('countries c', 'c.id = un.country_id', 'LEFT');
    $builder->join('(SELECT user_id, brand, exp_month, exp_year, last4 FROM payment_methods WHERE is_default = 1) pm', 'pm.user_id = users.id', 'LEFT');

    // START: membership check
    if (isset($whereClause['membership'])){

        if($whereClause['membership'] == 'paid'){
            $builder->join('(SELECT id as max_id, user_id, status, package_id, plan_period_start, plan_period_end, plan_interval, coupon_used FROM user_subscriptions WHERE id IN (SELECT MAX(id) as max_id FROM user_subscriptions GROUP BY user_id) AND status = "active") us', 'us.user_id = users.id', 'INNER');
        } else {
            $builder->join('user_subscriptions us', 'us.user_id = users.id', 'LEFT');
        }

    } else {
        $builder->join('(SELECT id as max_id, user_id, status, package_id, plan_period_start, plan_period_end, plan_interval, coupon_used FROM user_subscriptions WHERE id IN (SELECT MAX(id) as max_id FROM user_subscriptions GROUP BY user_id)) us', 'us.user_id = users.id', 'LEFT');
    }
    // END: membership check


    $builder->join('packages', 'packages.id = us.package_id', 'LEFT');

    $builder->where('users.deleted_at', NULL);

    // Build the query with the same conditions
    if (isset($whereClause) && count($whereClause) > 0) {
        foreach ($whereClause as $key => $value) {
            if ($key == 'membership') {
                continue;
            } elseif ($key == 'email') {
                $builder->like("auth.secret", $value);
            } elseif ($key == 'location') {
                $builder->like("domains.id", $value);
            } /*elseif ($key == 'last_active') {
                $builder->where("users.last_active >= ", ( CURDATE() " - INTERVAL". $value ));
            } */ else {
                $builder->where("users.$key", $value);
            }
        }
    }

    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']);
            
            if ($meta['operator'] == "=") {
                $metaBuilder->where('meta_value', $metaValue);
            } elseif ($meta['operator'] == ">=") {
                $metaBuilder->where('meta_value >=', $metaValue);
            } elseif ($meta['operator'] == "<=") {
                $metaBuilder->where('meta_value <=', $metaValue);
            } elseif ($meta['operator'] == "IN") {
                $metaBuilder->whereIn('meta_value', $metaValue);
            } else {
                $metaBuilder->like('meta_value', $metaValue);
            }

            $metaSubquery = $metaBuilder->getCompiledSelect();
            $builder->where("users.id IN ($metaSubquery)");
        }
    }

    // START: membership check
    // check only users with free membership
    if (isset($whereClause['membership']) && $whereClause['membership'] == 'free'){
        $builder->where("us.user_id", NULL);
    }
    // END: membership check


    if (isset($search) && !empty($search)) {
        $builder->groupStart();
            $builder->orLike('users.username', $search);
            $builder->orLike('users.first_name', $search);
            $builder->orLike('users.last_name', $search);
            $builder->orLike('auth.secret', $search);
        $builder->groupEnd();
    }

    $builder->groupBy('users.id');
    
    // Count the total number of results
    $countBuilder  = $db->newQuery()->select('count(*) as total_count')->fromSubquery($builder, 'counter_table');
    $countQuery = $countBuilder->get();
    $totalCount = $countQuery->getRow()->total_count;


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

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

    // Execute the main query
    $query = $builder->get();
    // echo '>>>>>>>> getLastQuery >>>>> '. $db->getLastQuery(); exit;

    $result = $query->getResultArray();

    // Process the results to merge metadata into user objects
    $users = [];
    foreach ($result as $row) {
        $userId = $row['id'];
        if (!isset($users[$userId])) {
            $users[$userId] = [
                'id'                            => $row['id'],
                'username'                      => $row['username'],
                'email'                        => $row['email'],
                'created_at'                    => $row['created_at'],
                'updated_at'                    => $row['updated_at'],
                'first_name'                    => $row['first_name'],
                'last_name'                    => $row['last_name'],
                'role_name'                    => $row['role_name'],
                'language'                      => $row['language'],
                'newsletter'                    => $row['newsletter'],
                'user_domain'                  => $row['domain_name'],
                'user_location'                => $row['user_location'],
                'last_active'                  => $row['last_active'],
                'status'                        => $row['status'],
                'last4'                        => $row['last4'],
                'brand'                        => $row['brand'],
                'exp_month'                    => $row['exp_month'],
                'exp_year'                      => $row['exp_year'],
                'package_name'                  => $row['package_name'],
                'subscription_status'          => $row['subscription_status'],
                'plan_period_start'            => $row['plan_period_start'],
                'plan_period_end'              => $row['plan_period_end'],
                'plan_interval'                => $row['plan_interval'],
                'coupon_used'                  => $row['coupon_used'],
                'last_activity_time'            => $row['last_activity_time'],
                'user_nationalities'            => $row['user_nationalities'],
                'registration_time'            => $row['registration_time'],
                'meta' => []
            ];
        }
    }

    $imagePath = base_url() . 'uploads/';

    // Get all meta data for each user
    $userIds = array_keys($users);
    if (!empty($userIds)) {
        $metaBuilder = $db->table('user_meta');
        $metaBuilder->whereIn('user_id', $userIds);
        $metaQuery = $metaBuilder->get();
        $metaResult = $metaQuery->getResultArray();

        foreach ($metaResult as $metaRow) {
            //pr($metaRow);
            $userId = $metaRow['user_id'];
            $users[$userId]['meta'][$metaRow['meta_key']] = $metaRow['meta_value'];
            if($metaRow['meta_key'] == 'profile_image'){
                $users[$userId]['meta']['profile_image_path'] = $imagePath.$metaRow['meta_value'];
            }
            if($metaRow['meta_key'] == 'cover_image'){
                $users[$userId]['meta']['cover_image_path'] = $imagePath.$metaRow['meta_value'];
            }
        }
    }

    $userData = array_values($users);
    return [
        'totalCount' => $totalCount,
        'users' => $userData
    ];
} 

Earlier I was using this code to get the Count, it was working fine, but not working after I added "GROUP BY"
PHP Code:
// Count the total number of results
    $countBuilder = clone $builder;
    $countBuilder->select('COUNT(*) as total_count');
    $countQuery = $countBuilder->get();
    $totalCount = $countQuery->getRow()->total_count; 

Also tried below code
PHP Code:
//Count the total number of results
$countBuilder = clone $builder;
$countBuilder->getCompiledSelect(true);
$countBuilder->select('COUNT(DISTINCT users.id) as total_count', false); // Use COUNT DISTINCT to ensure accurate counting
$countQuery = $countBuilder->get();
$totalCount = $countQuery->getRow()->total_count;
// above returns "SELECT COUNT(DISTINCT users.id) as total_count FROM `users`;" query. skipped all the filters and I got total number of rows in table instead of total number of rows retuned by query 


// also tried this
$countBuilder->resetSelect(); // Reset the SELECT clause
// got CRITICAL Error: Call to protected method CodeIgniter\Database\BaseBuilder::resetSelect() from global scope 

Thanks

RE: How to get the rowCount with results returned by query while joining tables - InsiteFX - 08-31-2024

CodeIgniter 4 User Guide - Working with Databases - Generating Query Results - getNumRows()

You can run the query in phpMyAdmin using the Explain command to see the impact of the query.

RE: How to get the rowCount with results returned by query while joining tables - pratibha.cts - 08-31-2024

Thanks for the response. It worked for me. Smile

But It's taking "935 ms" time to load 57 records, it is fine?
https://prnt.sc/tspivOjvOVIG

RE: How to get the rowCount with results returned by query while joining tables - InsiteFX - 08-31-2024

I would run it through phpMyAdmin Explain and see what it says.

ms = milliseconds (one thousandth of a second).