get result filtered by count grouped by date in cakephp 3

Question

I am working on CakePHP 3.4

I am building some application which uses API key. One key is limited to 100 calls per day, so I'm using multiple keys and want to loop through it.

In my controller, I'm calling a model's function to get API

$api_key = $this->CustomApi->getKey();
if (!$api_key) {
    $this->Flash->error(__('API key not found'));
} else {
    ...
}

In model CustomApi I want to write getKey() function which will find Api key whose call count of the day is less than 100.

Columns of custom_api table are id, api_key and calls are recorded in api_calls table whose columns are id, user_id, custom_api_id, created

Everytime user access function which requires API, a record is created in api_calls table with the time call has been made and primary key of the custom_api.

My question is, How to get Api key from CustomApi model whose call counts are less than 100 for that day (ie., today)

Edit 2 : My Try

$key = $this
    ->find()
    ->select(['CustomApi.id', 'CustomApi.api_key'])
    ->where(['CustomApi' => /* count is less than 100 */])
    ->leftJoinWith('ApiCalls', function ($q) {
          return $q->where(['ApiCalls.created' => date('Y-m-d')]);
    })
    ->group('CustomApi.id');

How to make a count in where?

Left join with `ApiCalls` where `created` is in the bounds of today, group `CustomApi` by its primary key, and select only those `CustomApi`s having a count of `ApiCalls`s less than 100. — ndm, Jun 20 '17 at 09:54
Hello ndm, please check `edit 2`. Is it correct against what you said? How to make a count in `where`. I tried googling but nothing found. — Anuj TBE, Jun 21 '17 at 05:29

score 0 · Accepted Answer · answered Jun 22 '17 at 22:13

You're nearly there, you need to use a HAVING clause, WHERE won't work here, as it works on single results, rather than on aggregated ones, which is what you need here.

SQL functions can be built using the function builder, and in order to create a comparison with a function expression, you'll have to leavarege the expression builder, using for example the lt() (less than) method.

$this
    ->find()
    ->select(['CustomApi.id', 'CustomApi.api_key'])
    ->leftJoinWith('ApiCalls', function ($q) {
        return $q->where(['ApiCalls.created' => date('Y-m-d')]);
    })
    ->group('CustomApi.id')
    ->having(function ($exp, $q) {
        return $exp->lt(
            $q->func()->count('ApiCalls.id'),
            100
        );
    });

And in case ApiCalls.created isn't a date only column, you'd use a BETWEEN clause, or manually clamp the value using >= and <= comparisons against the beginning and the end of the day.

get result filtered by count grouped by date in cakephp 3

1 Answers1