How to display count of total logins of each user within date range in cakephp 3.x

Question

I have a table logins with id, user_id(fk), date. How do I display users and number of logins of each user within date range (as another column).

This is my index function.

 public function index()
    {

        
        $this->paginate = [
            'contain' => 'Users',

        ];
        $query = $this->Logins->find('all')
              ->distinct('Logins.user_id')
                ->where([
                    'Logins.date >=' => '2021-07-25 00:00:00',
                    'Logins.date <=' => '2021-07-27 00:00:00',

              ]);
        $this->set('logins', $this->paginate($query));
    }

How do I implement the same by getting input for date range? and also how to display number of logins of each user within that date range?

Answer 1

You should add a column for login_count to the users table. Upon each successful login increase this by one. Alternatively have a log table that logs various events, among these can be a login event. That way you don't have to guess.

Then find the counts between two dates using (assuming Cake PHP 3.x or above). Assuming the events approach, you'd look at the login event and user id for a given date period:

$query = $events->find()->where(function (QueryExpression $exp, Query $query) {
        return $query->newExpr()->and([
          'startDate' => $startDate,
          'endDate' => $endDate,
          'user_id' => $userId
        ]);
    });