D1 data listing

Hello, I have a code snippet where I list the entries of an ip address:

export interface Env {
    DB: D1Database;
}

export default {
    async fetch(request: Request, env: Env) {
        const stmt = env.DB.prepare('SELECT time FROM accesslog WHERE ip = ?1').bind("81.213.254.29");
        const results = await stmt.all();
        return Response.json(results);
    },
};

Response:

{
  "results": [
  {
    "time": "13:43:42"
  },
  {
    "time": "13:43:43"
  },
  {
    "time": "13:43:44"
  },
  {
    "time": "13:43:44"
  },
  {
    "time": "13:43:44"
  },
  {
    "time": "13:44:9"
  },
  {
    "time": "13:44:11"
  },
  {
    "time": "13:45:28"
  },
  {
    "time": "13:55:7"
  },
  {
    "time": "13:55:7"
  }
  ],
  "success": true,
  "meta": {
    "duration": 0.6928119957447052,
    "last_row_id": 0,
    "changes": 0,
    "served_by": "SinglePrimary-d8dc9de1-4b0a-4c65-xxxx-38d9a8ee8fda.db3",
    "internal_stats": null
  }
}

My question is: How can I determine if there are 3 of the same data?
There are 3 of the data 13:43:44 in the code response.
I want to perform an operation when there are 3 of the same data.
How do I detect this with workers

I believe @elithrar helped you with this in the Discord.

SELECT time FROM (SELECT time, count(*) as cnt FROM accesslog WHERE ip = ?1 GROUP BY time) WHERE cnt >= 3 seems to be the query you settled on.

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.