-
pratibha.cts
Junior Member
-
Posts: 12
Threads: 4
Joined: Oct 2022
Reputation:
0
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
-
kenjis
Administrator
-
Posts: 3,671
Threads: 96
Joined: Oct 2014
Reputation:
230
-
pratibha.cts
Junior Member
-
Posts: 12
Threads: 4
Joined: Oct 2022
Reputation:
0
(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
-
kenjis
Administrator
-
Posts: 3,671
Threads: 96
Joined: Oct 2014
Reputation:
230
-
pratibha.cts
Junior Member
-
Posts: 12
Threads: 4
Joined: Oct 2022
Reputation:
0
(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();
|
Is it fine to have multiple rows in auth_identities table in SHIELD CI4?
