This issue has opened more than 1 year 😅 But hope it soon

HHữu Phong This issue has opened more than 1 year 😅 But hope it soon

M

Matt Silverlock•5/28/23, 4:33 PM

That issue was opened before D1 was even in open alpha, which was probably… a little premature

(We’re close with the Prisma folks so expect something soon)

L

lokiwind•5/28/23, 11:12 PM

There is 1 table and only 4 ip addresses are registered in this table.
Showing 65kb data.
What's the reason for that?

L

lokiwind•5/28/23, 11:13 PM

Size of the database with no tables created

D

Dahoom•5/29/23, 2:39 AM

Just want to say AMAZING work on the new update D1 devs

Ggeelen You might find that if you were to create a table and insert 3kb of data that th...

L

lokiwind•5/29/23, 6:50 AM

Yes you are right I created 1 table and created 3 headings to this table.
And I added 20 rows, the size did not change

L

lokiwind•5/29/23, 6:51 AM

I wonder after how many kb of data this size will start to increase

TTheTwoBoom Hi! I've got a little question about D1 and the Cloudflare Dashboard Is there an...

L

lokiwind•5/29/23, 7:00 AM

Not without using Wrangler.

L

lokiwind•5/29/23, 7:01 AM

https://developers.cloudflare.com/d1/get-started/

Get started · Cloudflare D1 docs

This guide will instruct you through:

MMatt Silverlock No, rows != depth. Depth correlates to sub-queries - if you have a sub-query tha...

J

Jason Hostetter•5/29/23, 11:33 AM

Ok, it’s not obvious to me how to calculate expression depth- the query that’s producing this error has two nested CTEs, the inner CTE has a single sub query. So it seems like only three layers of depth with the rest a bunch of joins? I’m clearly missing something about how depth is calculated.

L

lokiwind•5/29/23, 2:51 PM

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

M

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

DISTINCT

DISTINCT

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

L

lokiwind•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.

L

lokiwind•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...

M

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 = 3

SELECT 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...

L

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

L

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

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

L

lokiwind•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

M

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

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

L

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

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

H

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

h...hhi

H

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

D1Database is undefined, why? i followed the tutorial

HHasi D1Database is undefined, why? i followed the tutorial

L

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

can you share

wrangler.toml

wrangler.toml

file

Llokiwind can you share `wrangler.toml` file

H

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"

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

L

lokiwind•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

H

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

Im using normal Js, let me convert that

L

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

Okey

HHasi Im using normal Js, let me convert that

L

lokiwind•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.

H

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

?

Llokiwind Okey 🙂

H

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

Same response

L

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

Can you send a remote link pm.

Llokiwind Can you send a remote link pm.

H

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

yup

HHasi D1Database is undefined, why? i followed the tutorial

H

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

M

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 …

L

lokiwind•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) {

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>'.

K

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

all()

all()

returns a D1Result object

K

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

results

results

property

K

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

which is an array of returned rows

K

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

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

L

lokiwind•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

This issue has opened more than 1 year 😅 But hope it soon

Similar Threads

Similar Threads

Similar Threads