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