Drizzle Team•9mo ago

Is LIMIT 0 possible?

In drizzle it seems that limit: 0 is the same as excluding the limit. However, in SQL it normally is supposed to return an empty set. I had tried this, but it returned all the logs regardless of includeLogs:

return await q.characters.findFirst({
    with: {
        user: true,
        logs: {
            with: {
                dm: true,
                magic_items_gained: true,
                magic_items_lost: true,
                story_awards_gained: true,
                story_awards_lost: true
            },
            orderBy: (logs, { asc }) => asc(logs.date),
            limit: includeLogs ? undefined : 0
        }
    },
    where: (characters, { eq }) => eq(characters.id, characterId)
});

return await q.characters.findFirst({
    with: {
        user: true,
        logs: {
            with: {
                dm: true,
                magic_items_gained: true,
                magic_items_lost: true,
                story_awards_gained: true,
                story_awards_lost: true
            },
            orderBy: (logs, { asc }) => asc(logs.date),
            limit: includeLogs ? undefined : 0
        }
    },
    where: (characters, { eq }) => eq(characters.id, characterId)
});

I ended up writing the query like this:

const character: (Character & { user: User; logs: LogData[] }) | undefined = await (async () => {
    if (includeLogs) {
        return await q.characters.findFirst({
            with: {
                user: true,
                logs: {
                    with: {
                        dm: true,
                        magic_items_gained: true,
                        magic_items_lost: true,
                        story_awards_gained: true,
                        story_awards_lost: true
                    },
                    orderBy: (logs, { asc }) => asc(logs.date)
                }
            },
            where: (characters, { eq }) => eq(characters.id, characterId)
        });
    } else {
        return await q.characters
            .findFirst({
                with: {
                    user: true
                },
                where: (characters, { eq }) => eq(characters.id, characterId)
            })
            .then((c) => (c && { ...c, logs: [] }) || undefined);
    }
})();

const character: (Character & { user: User; logs: LogData[] }) | undefined = await (async () => {
    if (includeLogs) {
        return await q.characters.findFirst({
            with: {
                user: true,
                logs: {
                    with: {
                        dm: true,
                        magic_items_gained: true,
                        magic_items_lost: true,
                        story_awards_gained: true,
                        story_awards_lost: true
                    },
                    orderBy: (logs, { asc }) => asc(logs.date)
                }
            },
            where: (characters, { eq }) => eq(characters.id, characterId)
        });
    } else {
        return await q.characters
            .findFirst({
                with: {
                    user: true
                },
                where: (characters, { eq }) => eq(characters.id, characterId)
            })
            .then((c) => (c && { ...c, logs: [] }) || undefined);
    }
})();

6 Replies

SillvvaOP•9mo ago

Is this a bug?

SillvvaOP•9mo ago

Yeah, looks like it's a bug. https://github.com/drizzle-team/drizzle-orm/blob/ab1cfdf33f27e1df6e2b24b72496cd006daf0def/drizzle-orm/src/pg-core/dialect.ts#L345

const limitSql = limit ? sql` limit ${limit}` : undefined;

const limitSql = limit ? sql` limit ${limit}` : undefined;

It is checking that it's truthy and 0 is not.

GitHub

drizzle-orm/drizzle-orm/src/pg-core/dialect.ts at ab1cfdf33f27e1df6...

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - drizzle-team/drizzle-orm

Sandvich•9mo ago

It's probably not a bug because I would think the recommended way to do this is with a conditional field instead of limit 0.

return await q.characters.findFirst({
    with: {
        user: true,
        ...(includeLogs ? 
          logs: {
              with: {
                  dm: true,
                  magic_items_gained: true,
                  magic_items_lost: true,
                  story_awards_gained: true,
                  story_awards_lost: true
              },
              orderBy: (logs, { asc }) => asc(logs.date),
          }
          : {})
    },
    where: (characters, { eq }) => eq(characters.id, characterId)
});

return await q.characters.findFirst({
    with: {
        user: true,
        ...(includeLogs ? 
          logs: {
              with: {
                  dm: true,
                  magic_items_gained: true,
                  magic_items_lost: true,
                  story_awards_gained: true,
                  story_awards_lost: true
              },
              orderBy: (logs, { asc }) => asc(logs.date),
          }
          : {})
    },
    where: (characters, { eq }) => eq(characters.id, characterId)
});

Sandvich•9mo ago

Like in this example: https://orm.drizzle.team/docs/select#conditional-select (sorry about the formatting, I'm sure prettier will fix that)

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

SillvvaOP•9mo ago

That sort of works, but makes a complete mess of the return type.

if (!character) return null;
const test = character.logs[0].dm // Error: dm does not exist

if (!character) return null;
const test = character.logs[0].dm // Error: dm does not exist

Or... they could support valid PG SQL syntax This kind of fixes it, but now I'm resorting to type assertions and giving up type safety.

.then((c) => c && { ...c, logs: "logs" in c ? (c.logs as LogData[]) : ([] as LogData[]) });

.then((c) => c && { ...c, logs: "logs" in c ? (c.logs as LogData[]) : ([] as LogData[]) });

SillvvaOP•9mo ago

https://github.com/drizzle-team/drizzle-orm/issues/2011

GitHub

[FEATURE]: Add support for LIMIT 0 · Issue #2011 · drizzle-team/d...

Describe what you want I have a query that will conditionally exclude the relational data by setting the limit to 0. Drizzle treats this as omitting the limit, however in PostgreSQL this should ret...

Gaming

Programming

Is LIMIT 0 possible?