Drizzle Team•17mo ago

How to do conditional joins with the query builder?

Hi, I'm looking for a way to do conditional join's without working with AnyPgSelect. Thanks in advance.

15 Replies

Mendy•17mo ago

Are you trying to use the new relational query or the old syntax?

Dustin@WunderGraphOP•17mo ago

I think the new one. db.select().from(table).innerJoin

Eternal Mori•17mo ago

Here is a where clause with conditional params:

      .where(
        and(
          channel ? eq(s.tickets.channelId, channel) : undefined,
          status ? eq(s.tickets.status, status) : undefined,
          eq(s.channels.organizationId, organizationId)
        )
      )

      .where(
        and(
          channel ? eq(s.tickets.channelId, channel) : undefined,
          status ? eq(s.tickets.status, status) : undefined,
          eq(s.channels.organizationId, organizationId)
        )
      )

All equels with a undefined value are ignored

Dustin@WunderGraphOP•17mo ago

I think you missed the point. I want to do conditional joins.

Mendy•17mo ago

I didn’t try it, but you can pass an and function instead of eq where the first condition is the ‘magical’ sql utility and do something like this:

const result = await db.select().from(users).leftJoin(pets, 
  and(
    sql`${condition} IS TRUE`
    eq(users.id, pets.ownerId)
  )
)

const result = await db.select().from(users).leftJoin(pets, 
  and(
    sql`${condition} IS TRUE`
    eq(users.id, pets.ownerId)
  )
)

Dustin@WunderGraphOP•17mo ago

I mean conditional joins like leftJoin or innerJoin not condition statements part of a single join.

Mendy•17mo ago

I’m not sure I’m following.. Although my example does have a join, ultimately if condition is false the join will have no effect. Can you share a made-up example of what will be your ideal solution? Or better yet, a raw sql example.

Dustin@WunderGraphOP•17mo ago

I want to do this without specifying all types on my own. This doesn't work because leftJoin and inerJoin return different types.

const query = this.db
  .select()
  .from(schema.users)
  // here
  if (myCondition) {
    query =  query.innerJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  } else {
    query =  query.leftJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  }
  const result = await query

const query = this.db
  .select()
  .from(schema.users)
  // here
  if (myCondition) {
    query =  query.innerJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  } else {
    query =  query.leftJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  }
  const result = await query

Andrii Sherman•17mo ago

cc @Dan Kochetov

Dan•17mo ago

good question in this particular case, you can workaround like this:

const query = this.db.select().from(schema.users)[myCondition ? 'innerJoin' : 'leftJoin'](schema.projects, eq(schema.users.id, schema.projects.userId));

const query = this.db.select().from(schema.users)[myCondition ? 'innerJoin' : 'leftJoin'](schema.projects, eq(schema.users.id, schema.projects.userId));

Dustin@WunderGraphOP•17mo ago

Hi @Dan Kochetov, tsc does not complain but innerJoin literal is not type-safe. Therefore according to my example above, I'd like to work with one concrete join and reassign the result back to query.

Dan•17mo ago

why do you think it's not type-safe?

Dustin@WunderGraphOP•17mo ago

I tested it. I don't have auto completion for the different join types.

Dan•17mo ago

yes, you won't have the autocompletion for the method name, but that doesn't mean it's not type safe. if you enter an incorrect method name, it won't work.

Dustin@WunderGraphOP•17mo ago

You're right, thx

Gaming

Programming

How to do conditional joins with the query builder?