[FEATURE]: Add support for LIMIT 0

[sillvva]

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 return an empty data set.

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 believe the cause of this issue is here:

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

Line 345 in ab1cfdf

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

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

I believe it could be resolved with this simple change. limit is number | undefined and undefined fails the test, but 0 does not.

const limitSql = !isNaN(limit) ? sql` limit ${limit}` : undefined;

Not sure if there are any other places where this change would need to be made, though.

[sillvva]

For reference, I tried this method:
https://orm.drizzle.team/docs/select#conditional-select

const characters = 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)
	});

But it resulted in a messy return type.

if (!character) return null;
const test = character.logs[0].dm // Error: dm does not exist
// It should also be erroring, because logs being returned at all is not guaranteed.

This sort of fixes that, but requires type assertions and giving up type safety.

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

[JohnAllenTech]

Silly question perhaps but couldnt you make the with optional? ie something like

 with: includeLogs ? {
        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),
        } :  {
        user: true,
    },

Not sure will the type inference be smart enough here though

[sillvva]

@JohnAllenTech I already tried that. See my second comment.

This is my current workaround, but it would be nice to just have limit 0 to make this simpler.

	const character = await (async () => {
		if (includeLogs) {
			return await q.characters.findFirst({
				with: {
					user: true,
					logs: {
						with: {
							dm: true,
							magicItemsGained: true,
							magicItemsLost: true,
							storyAwardsGained: true,
							storyAwardsLost: 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: [] });
		}
	})();

[JohnAllenTech]

Ah apologies I see that now. As a potential workaround have you tried a negative limit? It seems it would pass the drizzle check but Postgres may have patched it. It used to be the same as 0

[sillvva]

It returns an error: ERROR: 2201W: LIMIT must not be negative

[AndriiSherman]

was released in https://github.com/drizzle-team/drizzle-orm/releases/tag/0.32.1