Drizzle Team•13mo ago

Optional/Default value for Relational `Extras`

I have a couple cases where a default/optional value for "extras" in the relational query builder could come in handy. I only really want to run this if the user is logged in. ATM I have the following solution

        extras(fields, operators) {
            return {
                authedUserTicketId:
                    sql<string>`(SELECT (ticket_id) from ${members} where event_id = ${
                        fields.id
                    } and user_id = ${authedUser?.user.id || null})`.as(
                        'authed_user_membership'
                    )
            };
        },

        extras(fields, operators) {
            return {
                authedUserTicketId:
                    sql<string>`(SELECT (ticket_id) from ${members} where event_id = ${
                        fields.id
                    } and user_id = ${authedUser?.user.id || null})`.as(
                        'authed_user_membership'
                    )
            };
        },

If there is no auth user it just sets the user_id to null but its not exactly ideal. Is there any plans or current way to optionally add this statement within the same return block?

7 Replies

Luxaritas•13mo ago

You should be able to use something like ifnull or coalesce (or whatever similar feature is in your dialect)

rykuno•13mo ago

Tried that. Actually returns a TS error Type 'string' is not assignable to type 'Aliased<unknown>'.

Luxaritas•13mo ago

Can you share the specific code that got you that?

rykuno•13mo ago

    const event = await db.query.events.findFirst({
        where: eq(events.id, params.eventId),
        extras(fields, operators) {
            return {
                authedUserTicketId:
                    sql<string>`(SELECT (ticket_id) from ${members} where event_id = ${
                        fields.id
                    } and user_id = ${authedUser?.user.id || null})`.as(
                        'authed_user_membership'
                    )
            };
        },
        with: {
            tickets: {
                extras(fields, operators) {
                    return {
                        memberCount:
                            sql<number>`(SELECT count(*)::int from ${members} where ticket_id = ${fields.id})`.as(
                                'member_count'
                            )
                    };
                }
            }
        }
    });

    const event = await db.query.events.findFirst({
        where: eq(events.id, params.eventId),
        extras(fields, operators) {
            return {
                authedUserTicketId:
                    sql<string>`(SELECT (ticket_id) from ${members} where event_id = ${
                        fields.id
                    } and user_id = ${authedUser?.user.id || null})`.as(
                        'authed_user_membership'
                    )
            };
        },
        with: {
            tickets: {
                extras(fields, operators) {
                    return {
                        memberCount:
                            sql<number>`(SELECT count(*)::int from ${members} where ticket_id = ${fields.id})`.as(
                                'member_count'
                            )
                    };
                }
            }
        }
    });

Luxaritas•13mo ago

Hm, where in that code is the error raised from? Trying it in a local project looks fine, and I also don't see any attempt at using a SQL default mechanism

rykuno•13mo ago

SQL default mechanism ?? Odd, is this the latest drizzle orm version? It works for me on the latest typescript/drizzle version but still yields a type error. I just set an ignore for it atm

Luxaritas•13mo ago

I was referring to something like is null or coalesce I tried it in my harness which uses 0.28.2, I’d need to check the TS version but should be latest 5.1

Gaming

Programming

Optional/Default value for Relational `Extras`