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