[SOLVED] Customize users auto update activity query

[kairiex2]

Hi..
first of all sorry for my bad english.
Every time we access usermodel / user entity it goes auto save to database the last_active and updated_at columns.
This is the default query performed :
UPDATE usersSETlast_active= '2022-08-17 21:04:42',updated_at = '2022-08-17 21:04:42' WHERE users.id IN (1)

So my question, is there a way to customized that automatic save by shield to our own query?
i've read and also check that using WHERE id = 1 perform much faster then WHERE users.id` IN (1)``.

[datamweb]

i've read and also check that using WHERE id = 1 perform much faster then WHERE users.id` IN (1)``.

Hello @iRedds, I would be happy to know your opinion on this matter.
Do you confirm the truth of this issue?

[jozefrebjak]

@datamweb I have another issue with last_active. I would like to cache some options lists, to not query all the time database. Here is for example my function to get users options in provided group.

$item could be for example customer and group = user

So this function gave me a array to use for select in form to choose from users which has an access to provided item.

public function getUsersFromGroupOptions(string $item, string $group): array
    {
        $options = [];

        $cacheName = "{$item}_options_user_group_{$group}";

        if (! ($options = cache($cacheName))) {
            $options = array_reduce(
                $this->select('users.id, users.first_name, users.last_name, users.username')
                    ->join('auth_groups_users as agu', 'agu.user_id = users.id', 'left')
                    ->where('agu.group', $group)
                    ->findAll(),
                static function ($result, $user) {
                    $name = implode(' ', [$user->first_name, $user->last_name]);

                    $result[$user->id] = (ctype_space($name) ? '' : $name) ?: $user->username;

                    return $result;
                },
                [],
            );
            cache()
                ->save($cacheName, $options, DECADE);
        }

        return $options;
    }

but I would like to delete cache if there is some update of the users groups or deletion.

So I added

    /**
     *
     * @var string[]
     */
    protected $beforeUpdate = ['clearCache'];

    /**
     * @var string[]
     */
    protected $beforeDelete = ['clearCache'];

and clearCache function

    /**
     * @param mixed[] $data
     *
     * @return mixed[]
     */
    public function clearCache(array $data): array
    {
        $user = $this->getUserById(is_array($data['id']) ? $data['id'][0] : $data['id']);

        // delete cache for customer options
        cache()->deleteMatching('*options_user_group*');

        if ($user !== null) {
            // delete model requests cache, name etc.
            cache()->deleteMatching("user#{$user->id}*");
        }

        return $data;
    }

But because of the last_active is there so much automatic updates and my cache is deleted permanently, so is useless.

So now I'm thinking how to delete cache only if it's needed and not all the time when last_active is updated. Maybe we need to update last_active column without touchin updated_at. @kenjis @MGatner @datamweb what are you thinking ?

[datamweb]

@jozefrebjak please see #392 .

[lonnieezell]

There's no way to not do updated if we use the model's functions. We might be able to do a raw query to handle that. Unsure if that's a good idea or not.

You can turn off the last active date recording, though, if you don't need that for your site. If Config\Auth:

public bool $recordActiveDate = false;

[jozefrebjak]

@lonnieezell thank you for your message.

Yes, I know about that option. I like the last_active column, so I don't want to turn it off at all.

I understand that updated_at is updated automatically, but is it a good idea to update it with last_active?

What if we need to track who updated the user and when? Then it is impossible to find a timestamp when it was done.

Also there is much more problems with caching at all, if we want to store some things in memory and not touch a database for all the time.

[pjsde]

What if we need to track who updated the user and when? Then it is impossible to find a timestamp when it was done.

I agree with @jozefrebjak, the last_active column shouldn't update the updated_at column as it's actually not changing the registry data but a status date.

[kenjis]

I agree.

[lonnieezell]

After thinking a bit more I agree. Anyone want to submit a PR for that?

[pjsde]

I can do it

[pjsde]

I send the PR #392

[jozefrebjak]

@pjsde Perfect, I'll test it in my project.

[kenjis]

This issue was fixed by #392

[kenjis]

So my question, is there a way to customized that automatic save by shield to our own query?

Create your own UserModel and override the updateActiveDate() method:
https://github.com/codeigniter4/shield/pull/392/files#diff-459f49e48ae4f20464cf08fd3256652536435610f4f7db89305fe7269e539ac9
and set your UserModel to $userProvider:

shield/src/Config/Auth.php

Line 341 in a3ea677

public string $userProvider = 'CodeIgniter\Shield\Models\UserModel';

[kairiex2]

Thanks.. I got a lot of understanding now on how to use shield.