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

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);
    },
};

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);
    },
};

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);
    },
};

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
  }
}

{
  "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
  }
}

{
  "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
  }
}

{
  "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

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

Matt Silverlock•5/29/23, 3:35 PM

You can do it in SQL - SELECT DISTINCT time FROM …SELECT DISTINCT time FROM …

DISTINCT is an exact match.

f you want to match on a less precise time - such as minute granularity - then use time functions to format as HH:MM before you use DISTINCTDISTINCT

https://www.sqlite.org/lang_datefunc.html

lokiwindOP•5/29/23, 3:49 PM

What I want to do has nothing to do with time. @elithrar
I just want to query if there are 3 data of the same type.

lokiwindOP•5/29/23, 3:52 PM

I explained the process I want to do in great detail by giving an example...

Llokiwind What I want to do has nothing to do with time. @elithrar I just want to query if...

Matt Silverlock•5/29/23, 4:12 PM

I misread your question — and I will make it clear that you don’t need to get snarky when folks are trying to help you.

SELECT time FROM (SELECT time, count(*) as cnt FROM accesslog WHERE ip = ?1 GROUP BY time) WHERE cnt = 3SELECT time FROM (SELECT time, count(*) as cnt FROM accesslog WHERE ip = ?1 GROUP BY time) WHERE cnt = 3 will give you want you want. Get a count of all occurrences of each time entry in a sub query and then filter for 3.

MMatt Silverlock I misread your question — and I will make it clear that you don’t need to get sn...

lokiwindOP•5/29/23, 4:13 PM

lokiwindOP•5/29/23, 4:15 PM

I'm sorry if I used a bad word, @elithrar

lokiwindOP•5/29/23, 4:15 PM

I just wanted to write my problem
Thanks for your help

Llokiwind I just wanted to write my problem Thanks for your help

Matt Silverlock•5/29/23, 4:44 PM

Of course. Let me know if that query works for you.

lokiwindOP•5/29/23, 4:50 PM

Yes, it worked. Thank you so much for helping me. @elithrar
I leave the code here, maybe someone needs it

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env) {
      const stmt = env.DB.prepare(`SELECT time FROM (SELECT time, count(*) as cnt FROM accesslog WHERE ip = ?1 GROUP BY time) WHERE cnt >= 3`).bind("81.213.254.29");
      const results = await stmt.all();
      return Response.json(results);
  },
};

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env) {
      const stmt = env.DB.prepare(`SELECT time FROM (SELECT time, count(*) as cnt FROM accesslog WHERE ip = ?1 GROUP BY time) WHERE cnt >= 3`).bind("81.213.254.29");
      const results = await stmt.all();
      return Response.json(results);
  },
};

Hasi•5/29/23, 6:17 PM

h...hhi

Hasi•5/29/23, 6:18 PM

D1Database is undefined, why? i followed the tutorial

HHasi D1Database is undefined, why? i followed the tutorial

lokiwindOP•5/29/23, 6:19 PM

can you share wrangler.tomlwrangler.toml file

Llokiwind can you share `wrangler.toml` file

Hasi•5/29/23, 6:22 PM

name = "foxes"
main = "src/index.js"
compatibility_date = "2023-05-18"

[[d1_databases]]
binding = "DB"
database_name = "foxes"
database_id = "im-not-sure-if-its-safe-to-share-it"

name = "foxes"
main = "src/index.js"
compatibility_date = "2023-05-18"

[[d1_databases]]
binding = "DB"
database_name = "foxes"
database_id = "im-not-sure-if-its-safe-to-share-it"

Llokiwind Yes, it worked. Thank you so much for helping me. @elithrar I leave the code he...

lokiwindOP•5/29/23, 6:24 PM

It seems to be correct can you try using a code like the code structure I used

Llokiwind It seems to be correct can you try using a code like the code structure I used

Hasi•5/29/23, 6:25 PM

Im using normal Js, let me convert that

lokiwindOP•5/29/23, 6:26 PM

Okey

HHasi Im using normal Js, let me convert that

lokiwindOP•5/29/23, 6:28 PM

It is healthier to write your codes with wrangler instead of using quick edit.

Llokiwind It is healthier to write your codes with wrangler instead of using quick edit.

Hasi•5/29/23, 6:29 PM

Llokiwind Okey 🙂

Hasi•5/29/23, 6:30 PM

Same response

lokiwindOP•5/29/23, 6:30 PM

Can you send a remote link pm.

Llokiwind Can you send a remote link pm.

Hasi•5/29/23, 6:30 PM

yup

HHasi D1Database is undefined, why? i followed the tutorial

Hasi•5/29/23, 7:05 PM

Fixed, I'm a literal idiot

Bbroofa1 Is there a way of specifying a preview D1 database in `wrangler.toml` , similar ...

Matt Silverlock•5/29/23, 7:54 PM

Better: a locally isolated version - https://developers.cloudflare.com/d1/learning/local-development/

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

lokiwindOP•5/29/23, 9:15 PM

Hello again @elithrar
I am sharing a part of the code I wrote.facing an error, how can I fix it.Thanks in advance..

const results = await stmt.all();
        if (results.length > 0) {

const results = await stmt.all();
        if (results.length > 0) {

Error Response:
The 'length' property is not of type 'D1Result<unknown>'.

kian•5/29/23, 9:18 PM

all()all() returns a D1Result object

kian•5/29/23, 9:18 PM

you probably want it to be const { results } = await stmt.all();const { results } = await stmt.all(); to grab just the resultsresults property

kian•5/29/23, 9:18 PM

which is an array of returned rows

kian•5/29/23, 9:19 PM

https://developers.cloudflare.com/d1/platform/client-api/#return-object

lokiwindOP•5/29/23, 9:20 PM

const { results } = await stmt.all();const { results } = await stmt.all();
Thanks for the help.
How can I query whether the result value is empty or not

kian•5/29/23, 9:20 PM

resultsresults will be an empty array if there were no results, or

null

null

if it doesn't apply (that query doesn't return anything).

Kkian <https://developers.cloudflare.com/d1/platform/client-api/#return-object>

kian•5/29/23, 9:21 PM

Take a look at this

lokiwindOP•5/29/23, 9:21 PM

Oki, I'll look.

lokiwindOP•5/29/23, 9:33 PM

The solution: @kiannh thx

const { results } = await stmt.all();
    if (results && results.length > 0) {

const { results } = await stmt.all();
    if (results && results.length > 0) {

Stronk•5/29/23, 10:40 PM

I have a billing question. If I have a table full of indexed images and I want to read one I know the index of how many read units would that use? Would it have to scan the whole table? Or would knowing the index just make it one read unit with the right query?

SStronk I have a billing question. If I have a table full of indexed images and I want t...

Matt Silverlock•5/29/23, 11:00 PM

One for the index and one for the table row, as a generalization / not committal.

yeehow•5/29/23, 11:16 PM

Have a 100k line SQL import I want to do.
Gets hung up on "Mapping SQL input into an array of statements"

Read that splitting into files with 5k lines helps.
Any tools to do this or should I just manually go through it?

MMatt Silverlock One for the index and one for the table row, as a generalization / not committal...

Stronk•5/30/23, 12:53 AM

From my understanding that doesn't make sense. Aren't the read units based on how many 4 kB chunks you use when searching. So if the image is under 4 kilobytes including the index it should be one read if it doesn't have to scan the rest of the table right.

SStronk From my understanding that doesn't make sense. Aren't the read units based on ho...

Matt Silverlock•5/30/23, 1:12 AM

Reading an index will likely always be an additional operation (read unit). We will make this clearer in the docs as we refine our pricing.

Stronk•5/30/23, 1:12 AM

Cool

Stronk•5/30/23, 1:13 AM

Is there a way I can test how a lookup would be counted during this time?

SStronk Is there a way I can test how a lookup would be counted during this time?

Matt Silverlock•5/30/23, 1:15 AM

Not yet, per the docs: https://developers.cloudflare.com/d1/platform/pricing/

we are working on it. Won’t be within the next month. Focusing on Time Travel and stability.

Pricing · Cloudflare D1 docs

While in public Alpha, D1 is currently free to use on all Workers plans. Refer to the our recent announcement) for more information.

SStronk Is there a way I can test how a lookup would be counted during this time?

Matt Silverlock•5/30/23, 1:17 AM

Keep in mind that you would need to serve 25 billion requests with that query pattern to exhaust the 50B included read units. 25B / 30 / 86400 = 9600+ requests/sec, 24/7/30.

Yyeehow Have a 100k line SQL import I want to do. Gets hung up on "Mapping SQL input int...

Mozzy•5/30/23, 8:19 AM

I did it manually, but if you do find a good secure tool to use, hit me up

Max (@rozenmd)•5/30/23, 8:44 AM

We're planning on removing the "Mapping SQL input into an array of statements" step from wrangler fwiw

Cconnor hopefully it gets merged soonish :)

Max (@rozenmd)•5/30/23, 9:42 AM

Merged the PR + cleaned up migrations output a bit

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

Similar Threads

Similar Threads

Similar Threads