Drizzle Team•6mo 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

Sillvva•6mo ago

Is this a bug?

Sillvva•6mo 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•6mo 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•6mo 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.

Sillvva•6mo 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[]) });

Sillvva•6mo 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?