SELECT 'value'

Is there some way to model SELECT 'value' without a table in drizzle? Specifically I'm trying to do something like

    SELECT
        value
    FROM
        user_config
    WHERE
        user_id = 1
    UNION ALL
        SELECT
            'fallback'

    SELECT
        value
    FROM
        user_config
    WHERE
        user_id = 1
    UNION ALL
        SELECT
            'fallback'

but couldn't find a way to create the SELECT for unionAll

const query = db.select({
    value: UserConfig.value,
  })
  .from(UserConfig)
  .where(eq(UserConfig.userId, 1))

query.unionAll(
// How to create select here?
)

const query = db.select({
    value: UserConfig.value,
  })
  .from(UserConfig)
  .where(eq(UserConfig.userId, 1))

query.unionAll(
// How to create select here?
)

I can of course create it as a raw query and handle the types myself

sql`${query} UNION ALL SELECT ${'fallback'}`

sql`${query} UNION ALL SELECT ${'fallback'}`

5 Replies

rphlmr ⚡•5mo ago

In union/unionAll you have to pass a query with the same selected fields as the left side. So query.unionAll(db.select({value:xxx}).from(xx))

RiftLurkerOP•5mo ago

That's what I gathered. In this case I have some literal values instead of a table to select from though The SQL statement above is a small part of a more complex statement, but essentially the idea is to provide a fallback row from code if no rows were previously found

rphlmr ⚡•5mo ago

Could you create a small example (just few cols and the fallback) with https://drizzle.run ?

Drizzle Run

RiftLurkerOP•5mo ago

https://drizzle.run/avm7dgjo83zccf26xpfouh75

Drizzle Run

literal select - Drizzle Run

RiftLurkerOP•5mo ago

I think this should be a decent enough summary really cool tool btw

Gaming

Programming

SELECT 'value'