No, rows != depth. Depth correlates to sub-queries - if you have a sub-query that executes over ever

MMax (@rozenmd)I reckon it's plausible, but tell @elithrar what you're building

I

Ian•5/27/23, 1:37 PM

I was thinking that every entity/user has their own "partition" or "account" and all the data and actions will be running against that partition, I reckon this is not SQL-like structure, was just curious if it was possible at all because of the good price for reads even at table sizes of 10M rows.

Original message was deleted

M

Max (@rozenmd)•5/27/23, 3:14 PM

problem is it's native, folks just don't know about it: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Error/cause

Error: cause - JavaScript | MDN

The cause data property of an Error instance indicates the specific original cause of the error.

K

kian•5/27/23, 3:16 PM

fwiw it exists in TS's native types too

K

kian•5/27/23, 3:18 PM

notably, it's a ES2022 thing (which is the target you should use for Workers)

K

kian•5/27/23, 3:18 PM

https://github.com/microsoft/TypeScript/commit/363e3a78f4def6084cc7371c8b1104485a7fe5f8

L

Lettucebowler•5/27/23, 5:22 PM

just switched to the experimental backend and am seeing huge performance gains. Love it, thanks for the hard work and I can't wait to see d1 get even better!

A

Ashley•5/27/23, 6:05 PM

Simple Q: is there a way to get the row(s) written to the DB when executing an INSERT with run()? (or a way to insert data and get the response without running a secondary query)

Original message was deleted

A

Ashley•5/27/23, 6:38 PM

Nice, thank you - still two queries, but at least better than doing two sequentialy in the code

A

Ashley•5/27/23, 6:58 PM

Oh wow TIL also

ty!

U

Unsmart•5/27/23, 7:31 PM

Afaik it’s always been there

Original message was deleted

M

Matt SilverlockOP•5/27/23, 7:31 PM

All errors should (will) be thrown. An empty result set isn’t an error though, but

D1Result

D1Result

will have no rows

UUnsmart Afaik it’s always been there 🤔

M

Matt SilverlockOP•5/27/23, 7:32 PM

Mostly! March 2021 in SQLite.

U

Unsmart•5/27/23, 7:32 PM

Ah interesting that would’ve been after I started using it

Original message was deleted

M

Matt SilverlockOP•5/27/23, 7:34 PM

You should handle the false case - let me take an action to iterate over the failure modes more explicitly

W

Walshy•5/28/23, 2:38 AM

looks like a wrangler bug, not seeing us exit with code 1: https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/migrations/apply.tsx

can you file an issue on the wrangler repo?

H

Hữu Phong•5/28/23, 2:07 PM

Could we use D1 with prisma?

HHữu Phong Could we use D1 with prisma?

J

James•5/28/23, 2:14 PM

Not yet, but hopefully soon! https://github.com/prisma/prisma/issues/13310

JJames Not yet, but hopefully soon! <https://github.com/prisma/prisma/issues/13310>

H

Hữu Phong•5/28/23, 2:19 PM

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 SilverlockOP•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 SilverlockOP•5/29/23, 3:35 PM

You can do it in SQL -

SELECT DISTINCT time FROM …

SELECT DISTINCT time FROM …

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

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

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

No, rows != depth. Depth correlates to sub-queries - if you have a sub-query that executes over ever

Similar Threads

Similar Threads

Similar Threads